| Author |
 Topic  |
|
|
fparker
Starting Member
27 Posts |
 Posted - 11/21/2011 : 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 |
Edited by - fparker on 11/21/2011 17:35:16
|
|
|
fparker
Starting Member
27 Posts |
Posted - 11/21/2011 : 17:40:55
|
Think i got it.
Thanks :)
--
f |
 |
|
|
fparker
Starting Member
27 Posts |
Posted - 11/21/2011 : 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 |
Edited by - fparker on 11/21/2011 17:46:14 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/22/2011 : 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 - 11/22/2011 : 16:38:55
|
thanks. i will try that out.
--
f |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/22/2011 : 23:43:33
|
wc
let us know how you got on
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fparker
Starting Member
27 Posts |
Posted - 11/29/2011 : 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 |
Edited by - fparker on 11/29/2011 14:02:38 |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2113 Posts |
|
|
fparker
Starting Member
27 Posts |
Posted - 11/29/2011 : 15:19:28
|
got it, thanks.
--
f |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/30/2011 : 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/
|
|
|
| |
Topic |
|
Need a single answer for multiple conditions.
