SQL functions, calculating a result based on other values-CodePudding

I am rather new to this whole sql thing, please be gentle on me. I hav a table with (Date(as date data type), Name, …) and i now want to add a column(on the right side), where it does something like this: If Month =<6 (fill the cell in the row) with S YY(YY being the Year in the date column) else (fill the cell with W YY). Thinking about it, there probably is an easy solution, i just can’t figure it out. Any help is greatly appreciated!

I tried a bit, nothing really worked, as said before i am almost 100% beginner

Thanks for the answers so far. I have now tried a bit further and came up with this: SELECT *, CASE WHEN MONTH(Termin) < 6 THEN 'S' YEAR(Termin) ELSE 'W' YEAR(Termin) END as new_test FROM Prüfung; //Termin is the name of the column where the date format is in. I am getting the error thingy saying that from is not in the right place?

CREATE TABLE "PRÜFUNG" ( "PRÜFUNG_ID" NUMBER(30,0), "TERMIN" DATE, "PRÜFUNGSFORM" VARCHAR2(30), "RAUM_ID" NUMBER(30,0), "MODUL_ID" NUMBER(30,0), "LEHRVERANSTALTUNG_ID" NUMBER(30,0), CONSTRAINT "PRÜFUNG_PK" PRIMARY KEY ("PRÜFUNG_ID") USING INDEX ENABLE )

This is code i used to generate the table. I am using oracle. Any ideas?

CodePudding user response：

One option is using EXTRACT with CASE:

SELECT
CASE WHEN EXTRACT(MONTH FROM TERMIN) <= 6
THEN 'S' || EXTRACT(YEAR FROM TERMIN)
ELSE 'W' || EXTRACT(YEAR FROM TERMIN) END AS new_test
FROM PRÜFUNG;

See the documentation of EXTRACT.

An example similar to your sample data here: db<>fiddle

If you need to select further columns, I highly recommend to do not use *, see here why, but to write a proper column selection.

For example

SELECT
TERMIN, PRÜFUNGSFORM, 
CASE WHEN EXTRACT(MONTH FROM TERMIN) <= 6
THEN 'S' || EXTRACT(YEAR FROM TERMIN)
ELSE 'W' || EXTRACT(YEAR FROM TERMIN) END AS new_test
FROM PRÜFUNG;

CodePudding user response：

You try use this

SELECT *,

    CASE WHEN 
    
        EXTRACT(MONTH FROM TERMIN) <= 6 THEN
        
            'S' ||TO_CHAR(TERMIN,'YY') 
        ELSE
        
            'W' || TO_CHAR(TERMIN,'YY')  END as new_test 
        
    FROM Prüfung;