If... Else Statement in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table:

ID Product Trading_ID Internal_CODE
1 Bicycle 1005304
2 Vehicle 1005304
3 Scooter 1005303
4 Books 1004002

------------------------------------------------------------------------------------------
My Question:

if the "Trading_ID" ends with 4 then, "Internal_Code" should be "BUY"

if the "Trading_ID" ends with 3 then, "Internal_Code" should be "SELL"

if the "Trading_ID" ends with 2 then, "Internal_Code" should be "RETURN"



I need an SQL Query to generate "Internal_Code". Thank you so much !! ^_^!
 
Maybe this could help!
Change Table1 to the name of your table

SELECT Table1.ID, Table1.Product, Table1.Trading_ID, Table1.Internal_CODE,
IIf(Right([Trading_ID],1)=4,"BUY",IIf(Right([Trading_ID],1)=3,"SELL",IIf(Right([Trading_ID],1)=2,"RETURN","UKNOWN"))) AS Expr1
FROM Table1;
 
I would create a table with the 2,3,4 in one field and the RETURN, SELL, BUY
in the other field. You could then add this table to the query and create a
new column:
Code: Right([Trading_ID],1)
set the criteria under this column to the 2,3,4 field from your new table.

You should always maintain data and not expressions. To read about this,
check out http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.
 
Maybe this could help!
Change Table1 to the name of your table

SELECT Table1.ID, Table1.Product, Table1.Trading_ID, Table1.Internal_CODE,
IIf(Right([Trading_ID],1)=4,"BUY",IIf(Right([Trading_ID],1)=3,"SELL",IIf(Right([Trading_ID],1)=2,"RETURN","UKNOWN"))) AS Expr1
FROM Table1;
Click to expand...

Maybe this would scan better!

SELECT ID, Product, Trading_ID,
SWITCH
(
Right(Trading_ID, 1) = '2', 'RETURN',
Right(Trading_ID, 1) = '3', 'SELL',
Right(Trading_ID, 1) = '4', 'BUY',
TRUE, '{{UKNOWN}}'
) AS Internal_Code
FROM Table1;

Jamie.

--
 
Big thanks to Mike, Jamie and Duane

For Mike, your SQL works perfectly thank you!.

FOr Jamie, big thanks for the extra SQL , now my query works perectly as
expected!


FOr Duane, big thanks for the advise for keeping the data inside the table.
But since i only have to generate daily report, i do not need to keep those
data, so i just keep the data inside my code. ONce it's done, i'll just throw
it !


ANyway, big thanks to all of you!!
 
Back
Top