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. Selectcolumn1,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 solvedif 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 onI 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 thisselect other columns...,case when coalesce(l.cnt,0) = 0 then 'string1' else 'string2' endfrom (mainquery) mleft join (select lineid,count(case when how in (1,12) then null else how end) as cnt from wline group by lineid )lon l.lineid = m.lineid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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
|
wclet us know how you got on------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 )lbtw, 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.lineidhttp://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor 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 )lbtw, we are on sql 2003. sorry that I left that out. --f
there's no such version of sql serveri think you probably meant sql 2008 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|