difficult SP

I have the following table:
tblFriends
OwnerCode FriendCode
7 10
7 14
10 7
10 12
10 13
12 10
13 10
13 18
14 7
18 13

I need a SP which return the following (im unsure about the best return datatype and the sql statement):

I want return all friendcodes of user nr 7 (10 and 14)
and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7

(if possible WITHOUT the use of a temptable!)

SELECT FriendCode

FROM tblFriends

WHERE OwnerCode=7

UNION

SELECT t2.FriendCode

FROM tblFriends t1

JOIN tblFriends t2 ON (t2.OwnerCode=t1.FriendCode)

WHERE t1.OwnerCode=7 AND t2.FriendCode<>7

That's not really a SP, but you can put it in one if you want.

|||GREAT!!! :-D|||hmm...it does what I want but I need a bit more...
I need to check if a certain value (e.g. 6) is in the result set...

I tried the following (which does not work):

SELECT

COUNT(*)FROM

(

SELECT

FriendCode

FROM

tblFriends

WHERE

OwnerCode=5

UNION

SELECT

t2.FriendCode

FROM

tblFriends t1

JOIN

tblFriends t2ON(t2.OwnerCode=t1.FriendCode)

WHERE

t1.OwnerCode=5AND t2.FriendCode<>5

)

WHERE

FriendCode=6I get the error:

Incorrect syntax near the keyword 'WHERE'.

|||

Close. You need to give your subquery an alias. For example, change "WHERE" to "t1 WHERE" or

SELECT COUNT(*)

FROM ( ... ) t1

WHERE ...

Difficult query: return recordset from concatenated strings?

Hi All,

I have what seems to me to be a difficult query request for a database
I've inherited.

I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.

For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.

A pseudo example of the table would be:

custrep, orderid, orderdate, comments

1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order

So, what I've been asked to provide is something like this:

orderdate, custrep, syscomment, countofsyscomments
2004-04-12, 1, Comment 1, 1
2004-04-12, 1, Comment 2, 2
2004-04-12, 1, Comment 3, 1
2004-04-12, 1, Comment 8, 1
2004-04-12, 2, Comment 1, 1
2004-04-12, 2, Comment 3, 1
2004-04-12, 2, Comment 4, 2
2004-04-12, 2, Comment 6, 2
2004-04-12, 2, Comment 7, 1

I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.

Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.

Any help will be humbly and immensely appreciated!

Much warmth,

MurrayAssuming your tables look something like this:

CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
*/)

CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)

Try this:

SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment) AS count_of_syscomments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment

--
David Portas
SQL Server MVP
--|||On Fri, 14 May 2004 15:51:25 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>Assuming your tables look something like this:
>CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
>DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
>comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
>*/)
>CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)
>Try this:
>SELECT O.orderdate, O.custrep, O.comment,
> COUNT(S.comment) AS count_of_syscomments
> FROM
> (SELECT orderdate, custrep, comment1
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment2
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment3
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment4
> FROM Orders)
> AS O (orderdate, custrep, comment)
> LEFT JOIN SystemComments AS S
> ON O.comment = S.comment
> GROUP BY O.orderdate, O.custrep, O.comment

Hi David,

Thanks for the suggestion, unfortunately that's not how the table is
defined.

Sorry, I should have posted a pseudo create table statement as well.

It looks something like:

CREATE TABLE OrderComments (custrep INTEGER NOT NULL, orderid INTEGER,
orderdate DATETIME NOT NULL, comments VARCHAR(2000))

The create table statement you have for SystemComments is fine.

So, in the OrderComments table, the comments column might contain:

':Comment 1:Comment 2: Comment 8:Comment whatever'

So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.

Sorry for the confusion...

Much warmth,

Murray|||>So, in the OrderComments table, the comments column might contain:
>':Comment 1:Comment 2: Comment 8:Comment whatever'
>So, each of the system and user generated comments for a particular
>order are concatenated into a string and are put into a single column
>(comments column) for that order.
>Sorry for the confusion...
>Much warmth,
>Murray

Can I assume that these are free form and free for all type of
comments and not standardized ?

Is there some kind of unique seperator between comments ?

Been there, done this real recently and it wasn't pretty at all.

Randy
http://members.aol.com/rsmeiner|||You can try this:

SELECT O.orderdate, O.custrep, O.comments,
COALESCE(SUM((LEN(O.comments)-LEN(REPLACE(O.comments,S.comment,'')))
/LEN(S.comment)),0)
FROM OrderComments AS O
LEFT JOIN SystemComments AS S
ON O.comments LIKE '%'+S.comment+'%'
GROUP BY O.orderdate, O.custrep, O.comments

Don't expect great performance though!

--
David Portas
SQL Server MVP
--|||On 14 May 2004 15:20:02 GMT, rsmeiner@.aol.comcrap (RSMEINER) wrote:

[snip]

>>
>Can I assume that these are free form and free for all type of
>comments and not standardized ?
>Is there some kind of unique seperator between comments ?
>Been there, done this real recently and it wasn't pretty at all.

Hi Randy,

Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.

The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.

The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.

I hear you that it isn't pretty.

Much warth,

Murray|||>Hi Randy,
>Pretty much, except that I have a reference table of the exact wording
>of each of the system comments that might be found in the concatenated
>value in the comments column.
>The comments are delimited by a colon character, but I can't assume
>that user comments, which get concatenated in the same field, will
>always be lacking colon characters.
>The only thing I can think to do is create a temp table in a stored
>procedure and do multiple update...select statements to populate the
>temp table, using the values in the predfined comments table.
>I hear you that it isn't pretty.
>Much warth,
>Murray

Since you have a table of the system comments, it makes it
much easier. I'm thinking on this.

How big are these tables ?

Randy
http://members.aol.com/rsmeiner|||Did you try my second solution?

--
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
--
Message posted via http://www.sqlmonster.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:
> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
--
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned
in result #1?
Message posted via http://www.droptable.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via droptable.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returne
d in result #1?
> --
> Message posted via http://www.droptable.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
Message posted via http://www.sqlmonster.com
PRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>
|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

Different results when executing from .NET component compare to executing from SQL Managem

Hi all,

I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.
This started to really annoying me, any thoughts or solution?
Thanks very much guys

I'm interested in some more details: what's the meaning of different results? Returning different data (rows)? Or same data (rows) in different orders? Can you post the stored proceudre if it does not contain too many statements?

Since recomplie the stored procedure can solve the issue, it seems SQL optimizer chooses different execution plans, thus may lead to different results. One work around is to call the stored procedure with recomplie:

EXECUTEyourSPName WITH RECOMPILE

However this is not a so good solution, as RECOMPLIE at each execution will impact SQL performance.

|||

The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.
I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.
SELECT
main.*,fa.*
FROM [NetFare] main
INNER JOIN dbo.Agent fa
ON main.FareId = fa.FareId
LEFT OUTER JOIN travel.dbo.currencies c
ON c.Code = main.FareCurrency
Where

AND (PriceReturnType = @.ReturnType)
And main.PortSetID in (Select Distinct PortSetID
From FarePortSetMember Where PortCode = @.DestinationPort Or PortCode = @.DestinationCity)
And main.OriginPortSetId in (Select Distinct FarePortSetId
From FarePortSetMember Where PortCode = @.OriginPort Or PortCode = @.OriginCity)
AND ((DateFrom <= @.EarliestDepartureDate) AND (DateTo >= @.EarliestDepartureDate))
AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)
And status in ('Active','Updating')

Option (KEEPFIXED PLAN)

I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.
So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.

Could MVP dudes verify this for us, please?

I am using SQL 2000 by the way and .NET 1.1 component called from ASP.NET 2.0

Different results from like and contains

Hi guys,
I have 2 different queries that I expected to return the same results.
Where Name like '%fish%'
and
Where contains((Name),'("*fish*")')
The first returned 181 results and the 2nd returned 178 results.
On closer inspection I determined that the Contains was not returning Names
with words that had "fish" embedded as in:
bigfishtackle.com
flyfishing
kingfisher
I thought that the *fish* would return words where fish was a suffix, prefix
or both.
What am I doing wrong?
TIA
Olaf,
Thanks for the heads-up on that information.
"Olaf Pietsch" <olaf_pietsch@.online.ms> wrote in message
news:%23iWYMNLSIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hi John,
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:%23m1txyKSIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
> The search with "*..." (leading asterisk) is not possible with contains,
> only the "...*" is possible.
> BOL says only:
> <prefix_term>
> Specifies a match of words or phrases beginning with the specified text.
> Enclose a prefix term in double quotation marks ("") and add an asterisk
> (*) before the ending quotation mark, so that all text starting with the
> simple term specified before the asterisk is matched.
> http://msdn2.microsoft.com/en-us/library/ms187787.aspx
> Many Ragards,
> Olaf
> --
> Gru Olaf
> Ich untersttze PASS Deutschland e.V. (http://www.sqlpass.de)
> Blog (http://www.sqlpass.de/PASSUserBlogs/tabid/178/Default.aspx?BlogID=3)
> Regionalgruppe Kln/Bonn/Dsseldorf
> (http://www.sqlpass.de/Regionalgruppen/KoelnBonnDuesseldorf/tabid/81/Default.aspx)
>

Different results

I am trying to return a two character result for the day number and was
wondering why I get a one character day returned in Statement A, and a two
character result in Statement B?
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
day(getdate())
end
print @.day
--the result is a one character day, if the date was February 7, 2005, the
result = 7
--Statement B:
if len(day(getdate())) = 1
set @.day = '0' + cast (day(getdate()) as varchar(1))
else
set @.day = day(getdate())
print @.day
--the result is a two character day, if the date was February 7, 2005, the
result = 07
Message posted via http://www.webservertalk.comThis is because of implicit conversion and datatype precedence for case/when
statement. Your 'else' clause has higher precedence (INT). Thus, your TRUE
(varchar(2)) clause has to be implicitly converted to INT (i.e. '07' -> 7).
This is the fix.
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
cast(day(getdate()) as varchar(2)) --explicit conversion to
varchar
end
print @.day
And here is a trick without case/when or if/else:
e.g.
set @.day = right(day(getdate())+100,2)
print @.day
-oj
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
>I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||The answer - don't use implicit conversions. And read BOL about the CASE
expression and how it determines the datatype of the returned value. Below
is a quick script that demonstrates two much easier ways to accomplish the
task.
declare @.test datetime
set @.test = '20050115'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
set @.test = '20050102'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||Robert,
The diff are here:
(A)
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
(B)
> set @.day = '0' + cast (day(getdate()) as varchar(1))
In 'A' you are casting the whole expression to varchar(1), that is why you
get 1 character.
Another way of doing this is:
set @.day = right('0' + ltrim(day(getdate())), 2)
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com
>|||I am completely wrong. I missed the "as varchar(2)" part. OJ and Scott post
s
explain the problem correctly.
AMB
"Alejandro Mesa" wrote:
> Robert,
> The diff are here:
> (A)
> (B)
> In 'A' you are casting the whole expression to varchar(1), that is why you
> get 1 character.
> Another way of doing this is:
> set @.day = right('0' + ltrim(day(getdate())), 2)
>
> AMB
>
> "Robert Richards via webservertalk.com" wrote:
>