Multiple to single fields 1

[Griffyn]

Hi all,

Using SQL Server 2000 and a stored procedure. I need to join some tables together in a way I can't seem to do.

Table SAMASTER is a stock code master, and contains the stock_code and 15 price fields called PRICE1, PRICE2, etc.

Table SABATCH is a detail table to SAMASTER, and contains a field called PRICE_CODE. This field will be contain 1 to 15, and obviously refers to which PRICE field in SAMASTER is relevant to each record in SABATCH.

But how do I show the correct price in my SQL? I only want a single column called PRICE in my output, and for it to show the correct price for each record in SABATCH.

Should I have 15 IF..THENs or somesuch to make up the formula for the PRICE column? Doesn't seem very elegant. I tried doing a ('PRICE'+[PRICE_CODE]) AS PRICE sort of thing, but that just shows PRICE1 or PRICE2, etc as the values in that column.

Thanks for your replies!

 

[JarlH]

Something like thos should do it:
[tt]
SELECT CASE price_code WHEN 1 THEN pricecol1
WHEN 2 THEN pricecol2
...
WHEN 15 THEN pricecol15 END
FROM joined tables...
[/tt]

/Jarl

 

[PHV]

15 price fields called PRICE1, PRICE2, etc
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Griffyn]

If I'd set this database up myself PHV, I surely wouldn't have done it this way. Unfortunately it belongs to our accounting package, so no can change.

Jarl, I'm pretty sure I already tried your suggestion and ended up with 15 fields in my query result, all called PRICE and the first one containing the prices for PRICE1 where appropriate, the second containing the prices for PRICE2 where appropriate, etc.

I'll test it out again on Monday.

 

[Griffyn]

Hooray that worked. For my first try I had coped some CASE SQl from another area, but it was designed to return multiple fields, so that explains that.

Thanks Jarl.