Multiple cases - SQL Server Forums

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

Multiple cases

Author

Topic

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-10 : 05:58:44

Hi all

Can someone tell me how to perform an insert/select using multiple conditions?

Here is my (incorrect) code chopped about a bit to make it easier to understand!

case when country =1 AND Q15_10_Codes = 'X99'
then [Q15_10_Transcription]
else when country = 3 AND Q15_10_Codes = 'X99' [Q15_10_Translation]
end as [Q15_X99_Text_1],

I tried doing them seperately, i.e.

--case when country = 1 And Q15_10_Codes = 'D99' then [Q15_10_Transcription] end as [Q15_D99_Text_1],
--case when country = 3 And Q15_10_Codes = 'D99' then [Q15_10_Translation] end as [Q15_D99_Text_1],

but this gives an error that the number in the insert does not match the select.

Any help would be most welcome as this is driving me a bit mad and I just know it will be very simple!!

Thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-10 : 06:06:00

Try this.

case
when country =1 AND Q15_10_Codes = 'X99'
then [Q15_10_Transcription]
when country = 3 AND Q15_10_Codes = 'X99'
then [Q15_10_Translation]
end as [Q15_X99_Text_1],

Karthik

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-10 : 07:17:24

Thanks for that - it very nearly works!!

The problem is that I have other Codes, i.e. "D99" so if I do this....

case when country =1 AND Q15_10_Codes = 'X99'
then [Q15_10_Transcription]
when country = 3 AND Q15_10_Codes = 'X99'
then [Q15_10_Translation]
end as [Q15_X99_Text_1],
case when country =1 AND Q15_11_Codes = 'X99'
then [Q15_11_Transcription]
when country = 3 AND Q15_11_Codes = 'X99'
then [Q15_11_Translation]
end as [Q15_X99_Text_2],
case when country = 3 And Q15_10_Codes = 'X99' then [Q15_10_Transcription] end as [Q15_X99_Text_1_Orig],
case when country = 3 And Q15_11_Codes = 'X99' then [Q15_11_Transcription] end as [Q15_X99_Text_2_Orig],

case when country =1 AND Q15_10_Codes = 'D99'
then [Q15_10_Transcription]
when country = 3 AND Q15_10_Codes = 'D99'
then [Q15_10_Translation]
end as [Q15_D99_Text_1],
case when country =1 AND Q15_11_Codes = 'D99'
then [Q15_11_Transcription]
when country = 3 AND Q15_11_Codes = 'D99'
then [Q15_11_Translation]
end as [Q15_D99_Text_2],
case when country = 3 And Q15_10_Codes = 'D99' then [Q15_10_Transcription] end as [Q15_D99_Text_1_Orig],
case when country = 3 And Q15_11_Codes = 'D99' then [Q15_11_Transcription] end as [Q15_D99_Text_2_Orig]

It gives some very strange results which surprises me as the condition Q15_10_Codes = 'D99' is very specific. Any ideas? For example should I put a series of "Else"s around it somehow?

Thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-10 : 07:26:02

what is the error message you got?

Karthik

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-10 : 07:37:20

Thanks Karthik! It is not an error message. It compiles OK but the results are wrong. What I am trying to do is pick up from two fields, Original and Translation depending on "Codes" and Country. So if Country = 1 (UK) AND Code = X99 put the Original in X99Text. If country = 3 and code = X99, put the Translation in X99Text and the Original in X99Text_Orig. If I only do it for the X99s it works fine but as soon as I included otheres (i.e. D99) it was inserting the data in random columns! Does that make sense?!!

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-10 : 07:48:42

ok, then post the result set you got.

and mention also what you need to get.

Karthik

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-10 : 08:19:18

Hi Karthik

Is there a way to post the output in excel? Otherwise it kind of gets lost!

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-10 : 08:22:15

quote:
Originally posted by paull

Hi Karthik

Is there a way to post the output in excel? Otherwise it kind of gets lost!

no need just post two or three error records and tell what needs to be in the output.

Karthik

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-10 : 08:31:08

RID Country Q15_10_Transcription Q15_10_Translation Q15_10_Codes Q15_X99_Text_1_orig Q15_X99_Text_2_orig Q15_D99_Text_1 Q15_D99_Text_2 Q15_D99_Text_1_orig
11000573 1 Rear shocks gone NULL D99 Rear shocks gone NULL NULL NULL NULL
11100649 1 Brake pipes hanging down under car NULL D99 Brake pipes hanging down under car NULL NULL NULL NULL
13004419 3 Beim Anziehen der Handbremse When pulling up the hand brake D99 When pulling up the hand brake NULL NULL NULL Beim Anziehen der Handbremse
13004828 3 Fahrzeug is bei schneller Fahrt Seitenwind anfällig. The vehicle is sensitive to side winds when driving faster. D99 The vehicle is sensitive to side winds when driving faster. NULL NULL NULL Fahrzeug is bei schneller Fahrt Seitenwind anfällig.

(All these examples are based on Q15_10_Codes = 'D99')
Q15_D99_Text_1 should contain data from q15_10_Transcription where Country =1 and from Q15_10_Translation where country =3 but for some reason it is putting the data in Q15_X99_text1

paull
Yak Posting Veteran

50 Posts

Posted - 2009-07-13 : 05:57:27

Hi Karthik - did you have any luck deciphering my code?!!!

Thanks

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources