SQL - Good way to change a column's results using a query?

Question

I'm not sure if there is a specific term for what I am trying to do, but basically I need to change a column that has data that is either a 'Y' or a 'N' value into a 1 bit boolean value(on/off, true/false etc...) I need to have it done on the query level as the function I am passing the resulting record set to expects a 1 or a 0, not 'Y' or 'N' for this column.

Currently, I am thinking I might have to query for the 'Y' values and add a new column with the value of 1 for these, then do all the 'N' values and select 0 as my new column's value and then merge those two result sets. Something like this:

SELECT newsID, title, url, description, body, 1 as isActive
FROM tbl_news_data
WHERE active = 'Y'
UNION
SELECT newsID, title, url, description, body, 0 as isActive
FROM tbl_news_data
WHERE active = 'N'

with `isActive` being my new column with the numeric value of 1 or 0 and leaving the old 'Y'/'N' values(column `active`) out of the result set.

Barring no syntax errors in my post, is this going to be an efficient way to do this? Are there better/simpler ways to do it with a query and not in my code? I just need the query result to be in a specific format and I can't change the table as other people's code expects the 'Y'/'N' values(and no, I can't ask them to change what they are looking for - I'm stuck with using the data as is).

Answer 1

If the DBMS is SQL Server...



SELECT

CASE WHEN active = 'Y' THEN 1 ELSE 0 END AS isActive

FROM news_data



This assumes that either the value of active for all rows is 'Y' or 'N', or that it's acceptable for any values other than 'Y' to resolve to 0.



Note that you could use the same expression to define a computed column -- the problem with adding a regular column is that you'd also need a trigger to keep it up to date when rows are inserted or altered.

Answer 2

If you're using oracle, you can use



select newsId, title, url, description, body,

DECODE(active,

'Y', 1,

'N', 0) as isActive

FROM tbl_news_data

Answer 3

i think of the least complicated area of do woulb be to union 2 queries into and then do the counts. occasion elect call, count quantity(call) from ( elect chief as call from initiatives union elect assistant as call from initiatives the place assistant isn't null ) x team with the aid of call