Click here to Skip to main content
15,354,542 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi,
The requirement is follows:
I have a table called tblactivity,where I have a column as checkedin_flag.
This checkedin_flag column can contain values [0,1,2,4,6].
Now the corresponding text values are as follows:
0->Incomplete
1->Complete
2->Posted
4->Posted
6->Cancelled
Now I need to write a query where the checkedin_flag value will be printed corresponding to their Text value.There is no other table where this number value->Text value relation is maintained.
Can you please let me know how to write the query:
select checkedin_flag as CheckedIn_Status from tblactivity --but I want to display the text values.
Posted
Updated 14-Oct-21 2:26am

I would strongly suggest that you create a foreign key table to maintain this and use a join query to get the value.

If you can't or don't want to use this approach then you would need to use a case statement

SQL
SELECT 
    'CheckedIn_Status' =
    CASE checkedin_flag
        WHEN 0 THEN 'Incomplete'
        WHEN 1 THEN 'Complete'
        ...
        ELSE 'Unknown Value' -- Referential Integrity is just one reason why a FK table should be used
    END
FROM 
    tblactivity
ORDER BY 
    checkedin_flag;


If you use this approach I would suggest that you make it a function - it would be slower but you would only need to maintain it in one place
   
v2
Try this :
SQL
select case checkedin_flag when 0 then 'Incomplete' 
when 1 then 'Complete' 
when 2 then 'Posted' 
when 4 then 'Posted' 
when 6 then 'Cancelled' 
else '-'
end CheckedIn_Status from tblactivity


Hope It Helps.
   
Personally, I would create a second table to do this - it is a lot more maintainable.
But, if you can't:
SQL
SELECT *, CASE checkedin_flag 
            WHEN 0 THEN 'Incomplete'
            WHEN 1 THEN 'Complete'
            WHEN 2 THEN 'Posted'
            WHEN 4 THEN 'Posted'
            WHEN 6 THEN 'Cancelled'
            ELSE 'Unknown'
          END
   FROM myTable
   
Comments
OriginalGriff 14-Oct-21 9:03am
   
While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]
Member 15394066 14-Oct-21 10:04am
   
Thanks
OriginalGriff 14-Oct-21 10:24am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900