CODEWITHSUNDEEP
sql
user1464559
user1464559

Reputation: 29

SQL Query to display a name based on column value

I have three different statuses for a column called ORDER_STATUS

Now depending on the numeric value returned by query, I want to display the actual name. How can I achieve this? I am trying to use it in a SELECT.

SELECT 
  OLI.do_dtl_status ORDER_STATUS
FROM 
  appwms.order_line_item OLI
WHERE 
  ORD.tc_order_id = '1002424748'

Here OLI.do_dtl_status ORDER_STATUS is the column I want to be replaced with names depending on its value. Please note this is for trouble-shooting. So I am not looking for performance etc but something which is simple to add in the query.

Upvotes: 2

Views: 3538

Answers (2)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

The typical way is to have a separate table with orderstatus (value, name) and a possibly a foreign key to that table. This way you can join with the orderstatus table to get the status names and the foreign key ensures you only enter valid orderstatus in the main table.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838696

Ideally you'd store the names in a separate table and use a JOIN.

Please note this is for trouble-shooting so I am not looking for performance etc but something which is simple to add in the query.

For a one-off situation you can just hard-code them into the query:

SELECT CASE OLI.do_dtl_status
          WHEN 110 THEN 'Released'
          WHEN 120 THEN 'Packed'
          WHEN 130 THEN 'Shipped'
       END AS ORDER_STATUS
FROM appwms.order_line_item OLI
WHERE ORD.tc_order_id='1002424748'

Upvotes: 5

Related Questions