March 17, 2005 at 7:10 pm
I have 5 subqueries that return a memo field in a select query.
it looks like this select ptpg.notetext,
mepg.notetext,
etc.
I would like to create a 6th column either in addition to or more preferably in lieu of the 5 subqueries where there's a heirarchy like this
if subquery 1 did not return a null then col 6 = ptpg.notetext
else if subquery 2 did not return a null then col 6 = mepg.notetext
etc.
I've tried an if statement in the select statement, but get a syntax error {tried with and without parenthesis around the clause,
I suspect strongly that a case statement will work unless I can nest case statements within one another.
March 18, 2005 at 2:54 am
If I am understanding you correctly, then a case statement should work.
Here is a simple example of how you might use it; this example should return "hello3", which is the value from the third subquery.
use tempdb
-- create a temp table and populate
create table Source (UniqueID int not null, Memofield text null)
-- try different values in the memo field to illustrate the example
Insert Source values (1, NULL)
Insert Source values (2, NULL)
Insert Source values (3, 'hello3')
Insert Source values (4, 'hello4')
Insert Source values (5, 'hello5')
Insert Source values (6, 'hello6')
-- this case statement shows
SELECT
CASE WHEN SPAM.Memofield IS NOT NULL THEN SPAM.Memofield
WHEN CHIPS.Memofield IS NOT NULL THEN CHIPS.Memofield
WHEN BEANS.Memofield IS NOT NULL THEN BEANS.Memofield
WHEN EGG.Memofield IS NOT NULL THEN EGG.Memofield
WHEN EXTRASPAM.Memofield IS NOT NULL THEN EXTRASPAM.Memofield
END
FROM
(SELECT * From Source WHERE UniqueID = 1) AS SPAM,
(SELECT * From Source WHERE UniqueID = 2) AS CHIPS,
(SELECT * From Source WHERE UniqueID = 3) AS BEANS,
(SELECT * From Source WHERE UniqueID = 4) AS EGG,
(SELECT * From Source WHERE UniqueID = 5) AS EXTRASPAM
-- Tidy up
drop table Source
GO
March 18, 2005 at 3:01 am
Or simpler still, you could replace the select statement with
SELECT
COALESCE (SPAM.Memofield, CHIPS.Memofield, BEANS.Memofield, EGG.Memofield, EXTRASPAM.Memofield)
FROM
(SELECT * From Source WHERE UniqueID = 1) AS SPAM,
(SELECT * From Source WHERE UniqueID = 2) AS CHIPS,
(SELECT * From Source WHERE UniqueID = 3) AS BEANS,
(SELECT * From Source WHERE UniqueID = 4) AS EGG,
(SELECT * From Source WHERE UniqueID = 5) AS EXTRASPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply