Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

GO

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

')

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')

)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

')

/*

Result set from your query:

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

<CountryCodes>BR</CountryCodes>

<CountryCodes>FR</CountryCodes>

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

*/

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

/*

KT,LC,VG,SU,TT,UY,VE

BR

FR

*/

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

conditional syntax in functions

I can't seem to get the nesting correct for an IF THEN condition inside a
function. My intent is to return the results (a table) of one of two
dfferent complex select statements. And, really, I am porting this SELECT
over from a working stored procedure. I wanted the convenience of being able
to use it in another select statement to further limit it down without
filters.
I keep getting "Incorrect syntax near 'BEGIN'"
To summarize:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
BEGIN
If @.category=@.subcategory
RETURN (
SELECT ...... WHERE products.FK_category = @.category
)
ELSE
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
)
END
GO
Am I doing this correctly?
Thanks
JulianYou are mixing inline table-valued functions (which are basically views that
accept parameters) and multi-statement table-valued functions (which allow
control-flow statement like IF..ELSE
Try the following to have an inline table-valued function:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = CASE WHEN
@.category=@.subcategory
THEN products.FK_subcategory ELSE @.subcategory
END
)
END
GO
Jacco Schalkwijk
SQL Server MVP
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:eUH9CyHYFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I can't seem to get the nesting correct for an IF THEN condition inside a
>function. My intent is to return the results (a table) of one of two
>dfferent complex select statements. And, really, I am porting this SELECT
>over from a working stored procedure. I wanted the convenience of being
>able to use it in another select statement to further limit it down without
>filters.
> I keep getting "Incorrect syntax near 'BEGIN'"
> To summarize:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS table
> AS
> BEGIN
> If @.category=@.subcategory
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> )
> ELSE
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> )
> END
> GO
>
>
> Am I doing this correctly?
>
> Thanks
> Julian
>|||If the in-line function would not work for you (because the two select
statements are completely different), you may want to use an
multi-statement function, i.e. something like this:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS @.result TABLE (
column1 int,
column2 varchar(50),
..
)
AS
BEGIN
IF @.category=@.subcategory BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
END
ELSE BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
END
RETURN
END
GO
Of course, if the two SELECT statements are similar, it's easier (and
usually better) to write an in-line function, like Jacco suggested.
Razvan|||If you just have 2 select statement in your function, you can always write
it as an inline function. The two select statements must always return the
same columns when you have a multi-statement function, so you can always put
them in an inline function with a UNION. Inline functions have less overhead
and in general leas to better query plans.
Jacco Schalkwijk
SQL Server MVP
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116961197.756965.191640@.g49g2000cwa.googlegroups.com...
> If the in-line function would not work for you (because the two select
> statements are completely different), you may want to use an
> multi-statement function, i.e. something like this:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS @.result TABLE (
> column1 int,
> column2 varchar(50),
> ...
> )
> AS
> BEGIN
> IF @.category=@.subcategory BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> END
> ELSE BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> END
> RETURN
> END
> GO
> Of course, if the two SELECT statements are similar, it's easier (and
> usually better) to write an in-line function, like Jacco suggested.
> Razvan
>

Conditional outer join help

I need to do an outer join in order to retrieve info in cases where some
table data does not exist, but doing this causes my query to return rows I
don't want for cases where data does exist in all tables. I have simplified
the problem as follows:
Say my data looks like this:
Table A Table B Table C
ID Code ID Code ID Code
1 11 11 A 11 X
1 12 12 A 12 Y
1 13 13 B 13 Z
2 21 21 C 21 X
My query needs to return data from other tables, which then join to Table A
on ID. I'm only interested in data from Table B and C where B.code = A and
C.code = Y. So effectively what I want returned from this part of the query
is
A.ID A.Code B.ID B.Code C.ID C.Code
1 12 12 A 12 Y
null null null null null null (where the null row is
from A.ID = 2)
The problem is, to get the null row returned from A.ID = 2, I have to do the
following:
select *
from A
left outer join B
on A.code = B.ID
and B.code = 'A'
left outer join C
on B.ID = C.ID
and C.code = 'Y'
But this means that I also get two extra rows returned for when A.ID = 1.
To get the correct rows returned for A.ID = 1, I need to do the following:
select *
from A
left outer join B
on A.code = B.ID
left outer join C
on B.ID = C.ID
where B.code = 'A'
and C.code = 'Y'
How can I do both?
Thanks in advance.Easy: replace the second outer join by an inner join between B and C. (I'm
not sure if you will have to put it into a subquery.)
Another possibility would be to put your first solution into a subquery
itself but this is a much less elegant solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>I need to do an outer join in order to retrieve info in cases where some
> table data does not exist, but doing this causes my query to return rows I
> don't want for cases where data does exist in all tables. I have
> simplified
> the problem as follows:
> Say my data looks like this:
> Table A Table B Table C
> ID Code ID Code ID Code
> 1 11 11 A 11 X
> 1 12 12 A 12 Y
> 1 13 13 B 13 Z
> 2 21 21 C 21 X
> My query needs to return data from other tables, which then join to Table
> A
> on ID. I'm only interested in data from Table B and C where B.code = A
> and
> C.code = Y. So effectively what I want returned from this part of the
> query
> is
> A.ID A.Code B.ID B.Code C.ID C.Code
> 1 12 12 A 12 Y
> null null null null null null (where the null row
> is
> from A.ID = 2)
> The problem is, to get the null row returned from A.ID = 2, I have to do
> the
> following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> and B.code = 'A'
> left outer join C
> on B.ID = C.ID
> and C.code = 'Y'
> But this means that I also get two extra rows returned for when A.ID = 1.
> To get the correct rows returned for A.ID = 1, I need to do the following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> left outer join C
> on B.ID = C.ID
> where B.code = 'A'
> and C.code = 'Y'
> How can I do both?
> Thanks in advance.|||Thanks Sylvain but inner joining between B and C eliminates the row of all
nulls that I need returned for A.ID = 2. I'm not sure how putting this in a
subquery would help? Can you be more specific?
Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
A.ID A.Code B.ID B.Code
1 11 11 A
1 12 12 A
null null null null (A.ID was 1)
null null null null (A.ID was 2)
So you can see that inner joining this to C on B.ID = C.ID is not going to
return the null rows. What am I missing in your explanation?
"Sylvain Lafontaine" wrote:

> Easy: replace the second outer join by an inner join between B and C. (I'
m
> not sure if you will have to put it into a subquery.)
> Another possibility would be to put your first solution into a subquery
> itself but this is a much less elegant solution.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>
>|||Sorry, but your repetition of the same names (ID and CODE) for differents
values may have mixed my little head. I've just took a little time to write
a test database with your data and here a query that I have made by
transforming your second query into a subquery and use it with an outer join
to the distinct values from A:
Select R.*, S.* from
(Select Distinct Id from A) as R Left outer join
(select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id as
CId, C.Code as CCode
from A left outer join B on A.code = B.ID
left outer join C on B.ID = C.ID
where (B.Code is Null and C.Code is Null)
or (B.code = 'A' and C.code = 'Y')
) as S
On R.Id = S.AId
and here are the result:
1 1 12 12 A 12 Y
2 NULL NULL NULL NULL NULL NULL
The first column is a new column that I have added and it simply gives the
list of distinct values for the ID of A. With the exception of this column,
this is exactly the result that you have asked for in your first post. I
have also added alias because of the multiple repetition of ID and CODE with
different meanings in the three tables.
Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
useless and can be probably replaced with INNER JOIN to give the same
results but I'm not sure if this is the case for you because I don't know
enough about your real data for the rest of the tables.
There are probably other possibilities, too but now, it's getting to late
for me.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
> Thanks Sylvain but inner joining between B and C eliminates the row of all
> nulls that I need returned for A.ID = 2. I'm not sure how putting this in
> a
> subquery would help? Can you be more specific?
> Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
> A.ID A.Code B.ID B.Code
> 1 11 11 A
> 1 12 12 A
> null null null null (A.ID was 1)
> null null null null (A.ID was 2)
> So you can see that inner joining this to C on B.ID = C.ID is not going to
> return the null rows. What am I missing in your explanation?
> "Sylvain Lafontaine" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Right now you have a magical "id" that does not tell us what it
identifies and a magical "code' that modeled as both strings and
numerics in the same schema. Then you have code and id are equi-joined
together. Here is my guess at what you might have meant to say:
CREATE TABLE Foobar
(foo_grp INTEGER NOT NULL,
member_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE Foo
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
foo_score CHAR(1) NOT NULL);
CREATE TABLE Bar
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
bar_score CHAR(1) NOT NULL);
Table A on ID. I'm only interested in data from Table B and C where
B.code = A and C.code = Y. <<
SELECT foo_grp, member_id, 'A' AS bar_score, 'Y' AS foo_score
FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foo AS F1, Bar AS B1
WHERE F1.member_id = Foobar.member_id
AND B1.member_id = Foobar.member_id
AND bar_score = 'A'
AND foo_score = 'Y');|||Hi Sylvain,
I really appreciate your help on this. Sorry that my attempt at
simplification has made things confusing. The real tables have hideous long
names and the referential integrity between them is full of holes.
I have tried to apply your solution but I don't understand how your inner
query generates the null column values. As you say, the outer joins in the
subqueries are effectively the same as inner joins. Therefore, there will b
e
no null column values in the S table. I tried adding the restrictions to th
e
inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code = 'A
'
left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this mean
s
I will always get an extra row of nulls for the A.ID = 1 row. i.e. my resul
t
set would be (using your leading row of distinct values from A):
1 1 12 12 A 12 Y
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
Jane
"Sylvain Lafontaine" wrote:

> Sorry, but your repetition of the same names (ID and CODE) for differents
> values may have mixed my little head. I've just took a little time to wri
te
> a test database with your data and here a query that I have made by
> transforming your second query into a subquery and use it with an outer jo
in
> to the distinct values from A:
> Select R.*, S.* from
> (Select Distinct Id from A) as R Left outer join
> (select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id a
s
> CId, C.Code as CCode
> from A left outer join B on A.code = B.ID
> left outer join C on B.ID = C.ID
> where (B.Code is Null and C.Code is Null)
> or (B.code = 'A' and C.code = 'Y')
> ) as S
> On R.Id = S.AId
> and here are the result:
> 1 1 12 12 A 12 Y
> 2 NULL NULL NULL NULL NULL NULL
> The first column is a new column that I have added and it simply gives the
> list of distinct values for the ID of A. With the exception of this colum
n,
> this is exactly the result that you have asked for in your first post. I
> have also added alias because of the multiple repetition of ID and CODE wi
th
> different meanings in the three tables.
> Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
> useless and can be probably replaced with INNER JOIN to give the same
> results but I'm not sure if this is the case for you because I don't know
> enough about your real data for the rest of the tables.
> There are probably other possibilities, too but now, it's getting to late
> for me.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
>
>|||Hi Jane,
The null column values are not generated by the inner query but by the
Left Outer Join of the outer query.
Excerpt for the aliases, the big inner query is the same as your second
query in your first post and this query generate only one line. The other
inner query (the small one: (Select Distinct Id from A) ) generate only
two lines with two values: 1 and 2 and combined as a Left Outer Join to the
other inner query can give only two big lines: the first one with values
other than null and the other one with all null values.
Maybe you could post here the query that you have tried and that gives
three lines instead of two.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
> Hi Sylvain,
> I really appreciate your help on this. Sorry that my attempt at
> simplification has made things confusing. The real tables have hideous
> long
> names and the referential integrity between them is full of holes.
> I have tried to apply your solution but I don't understand how your inner
> query generates the null column values. As you say, the outer joins in
> the
> subqueries are effectively the same as inner joins. Therefore, there will
> be
> no null column values in the S table. I tried adding the restrictions to
> the
> inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code =
> 'A'
> left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this
> means
> I will always get an extra row of nulls for the A.ID = 1 row. i.e. my
> result
> set would be (using your leading row of distinct values from A):
> 1 1 12 12 A 12 Y
> 1 NULL NULL NULL NULL NULL NULL
> 2 NULL NULL NULL NULL NULL NULL
> Jane
> "Sylvain Lafontaine" wrote:
>|||Hi,
Okay I see what you mean. I have used a version of your solution and it
works although I have to repeat a large query. I feel like there should be
a
better way but I don't want to post the actual query. It's too large and th
e
relationships between the tables are too hard to see.
Thanks for your help.
jane
"Sylvain Lafontaine" wrote:

> Hi Jane,
> The null column values are not generated by the inner query but by the
> Left Outer Join of the outer query.
> Excerpt for the aliases, the big inner query is the same as your secon
d
> query in your first post and this query generate only one line. The other
> inner query (the small one: ? (Select Distinct Id from A) ? ) generate o
nly
> two lines with two values: 1 and 2 and combined as a Left Outer Join to th
e
> other inner query can give only two big lines: the first one with values
> other than null and the other one with all null values.
> Maybe you could post here the query that you have tried and that gives
> three lines instead of two.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
>
>|||You can use a temporary table or a table variable to store the result of
this large query and avoid repeating it.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
> Hi,
> Okay I see what you mean. I have used a version of your solution and it
> works although I have to repeat a large query. I feel like there should
> be a
> better way but I don't want to post the actual query. It's too large and
> the
> relationships between the tables are too hard to see.
> Thanks for your help.
> jane
>
> "Sylvain Lafontaine" wrote:
>|||And finally, if you query is really complex, then the use of other options
like Exists() could be your best solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23UcmOnIYFHA.796@.TK2MSFTNGP09.phx.gbl...
> You can use a temporary table or a table variable to store the result of
> this large query and avoid repeating it.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIAhttp://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:
> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:
>> Hi
>> I am trying to query a table and return the results all in one field.
>> My table basicly looks like this:
>> tblBody:
>> ID Body
>> 1 This is body 1
>> 2 This is body 2
>> 3 This is body 3
>> Now i want to select all this data, and concatenate it into one field
>> So when i do:
>> SELECT Body FROM tblBody (altered to be the right way of doing this
>> of
>> course)
>> The results are:
>> Body
>> This is body 1This is body 2This is body 3
>> and not
>> Body
>> 1 This is Body 1
>> 2 This is Body 2
>> 3 This is Body 3
>> TIA
>>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIAhttp://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:
>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIA
http://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>
|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:

Concatinate without Nulls

I am building a view and I want to return a combined field of all that make
up a full address (Address1, Address2, City, State and ZipCode). If any of
the fields are Null it returns a null. Do I have to use something like
COALESCE on each one as any of them can be null. Thanks.
DavidUse Isnull(field_name, '') to return an empty string from a null.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:#63iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> I am building a view and I want to return a combined field of all that
make
> up a full address (Address1, Address2, City, State and ZipCode). If any
of
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>|||>> Do I have to use something like COALESCE on each one as any of them can
That is one simple and recommended way to avoid NULLs being returned.
Anith|||You can also use ISNULL.
Example:
select isnull(lastname + ', ', '') + isnull(firstname)
from employees
go
AMB
"David C" wrote:

> I am building a view and I want to return a combined field of all that mak
e
> up a full address (Address1, Address2, City, State and ZipCode). If any o
f
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>
>|||You could also investigate the session option
CONCAT_NULL_YIELDS_NULL
Most client tools set this value to ON to give you the behavior you're
seeing. But if want nulls to be treated as empty strings during
concatenation operations, you can
SET CONCAT_NULL_YIELDS_NULL OFF
The setting (as with all SET options) only applies to the current
connection, or, if you set it in a stored procedure, it applies to that
procedure.
Changing this option will also invalidate the use of indexed views or
indexes on computed columns.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%2363iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>I am building a view and I want to return a combined field of all that make
>up a full address (Address1, Address2, City, State and ZipCode). If any of
>the fields are Null it returns a null. Do I have to use something like
>COALESCE on each one as any of them can be null. Thanks.
> David
>