Help with Case statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Case statement

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-08-15 : 11:30:10

Hey i have 2 tables one is product and the other is barcodes.
the join between the 2 tables is the p-code.

Am trying to return back the barcodes from the barcode table base on the pack size in the product table if the pack size = 1 then its then return the inner_barcode else its the outter_barcode these results are all displayed in the same column

I did a case on it in a view but its not returning back all my data

here is my code.

select p-code,CASE when pack = 1 then inner else outter as barcode
from barcode
inner join product on [product.p-code] = [barcode.p-code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-15 : 11:42:23

your case statement is missing a end


select p-code,CASE when pack = 1 then inner else outter END as barcode
from barcode
inner join product on [product.p-code] = [barcode.p-code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 11:43:03

first of all your case is missing an end clause


select p-code,CASE when pack = 1 then inner else outter end as barcode
from barcode
inner join product on [product.p-code] = [barcode.p-code]

when you say you're not getting all data expected are you sure product table has atleast a record for each p-code in barcode? Can you explain discrepancy by means of some sample data?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-15 : 11:43:56

[code]
select p.[p-code],
case when pack = 1 then inner_barcode else outer_barcode end as barcode
from product p
inner join barcode b on p.[p-code] = b.[p-code]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-15 : 11:44:33

KH
[spoiler]Time is always against us[/spoiler]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-08-15 : 11:54:13

Sorry i was missing the END and it turns out there was a space in my [p-code] on the barcode table don't worry its test data thanks for your help guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 11:56:20

quote:
Originally posted by rookie_sql

Sorry i was missing the END and it turns out there was a space in my [p-code] on the barcode table don't worry its test data thanks for your help guys.

No probs..In the end Vinnie turned out the winner in marathon beating us both