SQL functions, calculating a result based on other values

Question

I have 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).

I 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.

Please tag the DBMS you use and please add some sample data and the expected result, both as tables in your question. It's good you described what you want to do, but also showing it will make it even clearer. And please edit your question to add clarifications rather than doing this in comments. — Jonas Metzler, Jan 19 '23 at 06:41
Why do you refer to "cell"? Is the question for a spreadsheet? you also refer to a "date column" but we cannot see any columns at all - so we are flying blind. I suggest you read these: [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) - oh, and please do NOT use images of data or of sql — Paul Maxwell, Jan 19 '23 at 06:47

Jonas Metzler · Accepted Answer · 2023-01-19T07:50:05.717

0

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;

edited Jan 19 '23 at 07:50

answered Jan 19 '23 at 07:41

Jonas Metzler

4,517
1
5
17

When i do this query: SELECT Termin, Prüfungsform, Raum_id, Modul_id, Lehrveranstaltung_id, CASE WHEN EXTRACT (MONTH FROM TERMIN) <= 6 THEN 'S' || EXTRACT (YEAR FROM TERMIN) ELSE 'W' || EXTRACT (YEAR FROM TERMIN) END AS test FROM PRÜFUNG; I only get ‘Termin’ and ‘Prüfungsform’, what could cause this?This only happens in the application when i do it in the ‘terminal’ it works normal? – Squallel Jan 19 '23 at 08:16
@Squallel This is another question and I think you should create a new question for that. It is difficult to say what is the reason of that issue without further information and we don't know which application or terminal you use. – Jonas Metzler Jan 19 '23 at 08:22

score 0 · Answer 2 · edited Jan 20 '23 at 22:47

0

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;

edited Jan 20 '23 at 22:47

buddemat

4,552
14
29
49

answered Jan 19 '23 at 07:43

Hậu tran

17
7

SQL functions, calculating a result based on other values

2 Answers2