problem with case-when-then-else syntax

[petergroot]

Hi,
I have a Produkt table with 2 prices (ordinary price and special offer price) in the table there is also a boolean which determines if the produkt is on sale or not.
Now i want to write an SQL line that pulls out the Special Offerprice when the boolean is true.
I understand that i need the case function, but how do i use that?
I want to create a new field called Sold Price.
Thanks in Advance, Peter

 

[MeanGreen]

If you want to pull prices based on your boolean value you can do this:

select produktid,
case when boolean = 'True' then [special offer price]
else [ordinary price]
end
from produkt


'True' would be whatever you are using for true (probably 1 or 0)

Hope this helps.

 

[petergroot]

Hi,
Thanks for answering my question!
I entered the sql line exactly in the VB designer, but i get an error (if i enter the line literally i get: erroe in SELECT clause: expression near 'boolean' Unable to parse query text.
Can you help me out here?

 

[MeanGreen]

What are your column names? Boolean was just what I put as a placeholder. Also, what are the datatype of each column?

Thank you.

 

[petergroot]

Hi,
Table name: tblProdukt
Column names: Produkt_Pris (text format, this is the normal price) , Produkt_ TilbudsPris (text format, this is the special offer price) , Produkt_PåTilbud (boolean to indicate wheter the produkt is on sale or not)
Thanks again! Peter

 

[MeanGreen]

When you say boolean you mean "bit" right? Or is this an Access database? Why aren't your prices datatype money? Anyway, using your column names for SQL Server the select would be:

select case when Produkt_PåTilbud = 1 then [Produkt_ TilbudsPris]
else [Produkt_Pris]
end
from tblProdukt


Hope this helps.

 

[petergroot]

Hi again... i'm sorry to bother you so much, but i have been struggling with this problem for days.
Yes, i meant Boolean. And Yes, it is an Access database, but i use the VB designer to write SQL lines, so i thought i had to find the answer in an SQL forum.
I still get the same error, but i guess that has to do with because it is an Access database?
Thanks again, Peter

 

[MeanGreen]

Try posting your question in the ACCESS Forum. I will look at access and see if I can come up with the proper query too.

 

[MeanGreen]

Here is an access solution:
Test was my database; Onsale, SalePrice were currency and OnSale was my boolean (Yes/No)

SELECT IIf([test]![ONSale],[test]![SalePrice],[test]![OriginalPrice]) AS Expr1, [test].[ID]
FROM test;


Good Luck.

 

[petergroot]

Hi,
You helped me out in the right direction anyway!
I started to look for a similar funcion in Access and found the answer there.
In case you (or anyone) is interested, here is the solution:

Select tblprodukt.produkt_id, IIF(produkt_påtilbud, produkt_tilbudspris, produkt_pris) AS salgspris
from tblprodukt

Thanks again for your help, Peter