Need a single answer for multiple conditions.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need a single answer for multiple conditions.

Author

Topic

fparker
Starting Member

27 Posts

Posted - 2011-11-21 : 17:12:16

Doing a data transfer from one system to another and am stuck on one aspect.

It kind of goes like this.

Select
column1
,column2
,column3
...

Then I need to calculate a values as such.

Query a column in another table and if there is one row returned then if that row has a 1 or 12 in it "String1" else "String2" <<-- this part i think i have solved

if there is more than one row returned, then all values must be 1 or 12 to get "String1", else "String2" << this part i am stumped on

I thought about writing a case statement but it seems really complicated.

TIA on any ideas on how to tackle this.

--
f

fparker
Starting Member

27 Posts

Posted - 2011-11-21 : 17:40:55

Think i got it.

Thanks :)

--
f

fparker
Starting Member

27 Posts

Posted - 2011-11-21 : 17:45:06

(select case (select count(w.how)
from wline w
where w.lineid = l.lineid )
when 1 then
(select case
(select w.how
from line w
where w.lineid = l.lineid )
when 1 then 'string1'
when 12 then 'string1'
else 'string2'
end)
else
case when (select count(w.how)
from wline w
where w.lineid = l.lineid and w.how in (1,12))
=
(select count(x.how)
from wline x
where x.lineid = l.lineid )
then 'string1'
else 'string2'
end

end )

l.linid is part of the bigger query.

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:05:13

dont overcomplicate things. i think what you need is this


select other columns...,
case when coalesce(l.cnt,0) = 0 then 'string1' else 'string2' end
from (mainquery) m
left join (select lineid,count(case when how in (1,12) then null else how end) as cnt
            from wline
            group by lineid
            )l
on l.lineid = m.lineid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fparker
Starting Member

27 Posts

Posted - 2011-11-22 : 16:38:55

thanks. i will try that out.

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 23:43:33

wc
let us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fparker
Starting Member

27 Posts

Posted - 2011-11-29 : 14:00:50

On the second to last line, what is the l for? the line is )l
btw, we are on sql 2003. sorry that I left that out.

--
f

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-29 : 14:46:22

The )l is aliasing the derived table, so that you can reference it by that name.
Also, now the compiler can tell what you mean in the ON clause by l.lineid

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

fparker
Starting Member

27 Posts

Posted - 2011-11-29 : 15:19:28

got it, thanks.

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 01:39:54

quote:
Originally posted by fparker

On the second to last line, what is the l for? the line is )l
btw, we are on sql 2003. sorry that I left that out.

--
f

there's no such version of sql server
i think you probably meant sql 2008

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources