Change column to search according to day of the week

I’ve to query a Oracle DB in order to get data from a table according to the day of the week.

If it’s Monday, I need to query FL_PO_LUN, if it’s Tuesday, my query will be on FL_PO_MAR and so on.

This is the query I’m trying to use.

SELECT DISTINCT(DEPCOD),ENTECOD FROM RIO_PLA_DEP WHERE
DT_INIZIO_VAL <= trunc(sysdate) AND
DT_FINE_VAL >= trunc(sysdate) AND 
(SELECT DECODE( 
TO_CHAR(SYSDATE,'d'),
'1','FL_PO_LUN',
'2','FL_PO_MAR',
'3','FL_PO_MER',
'4','FL_PO_GIO',
'5','FL_PO_VEN',
'6','FL_PO_SAB',
'7','FL_PO_DOM') AS DAY_OF_WEEK
FROM DUAL) = 1;

Decode statement works fine, but the whole statement won’t work. Actually i get a ORA-01722 error.

Expected result would be:

SELECT DISTINCT(DEPCOD),ENTECOD FROM RIO_PLA_DEP WHERE
DT_INIZIO_VAL <= trunc(sysdate) AND
DT_FINE_VAL >= trunc(sysdate) AND 
FL_PO_LUN = 1;

Answer

Drop quotes around column names. Currently you are trying to compare char to int type. This is impossible as ‘FL_PO_LUN’ and other char constants do not represent int.

SELECT DISTINCT(DEPCOD),ENTECOD 
FROM RIO_PLA_DEP 
WHERE
    DT_INIZIO_VAL <= trunc(sysdate) AND
    DT_FINE_VAL >= trunc(sysdate) AND 
    (SELECT DECODE( 
    TO_CHAR(SYSDATE,'d'),
    '1',FL_PO_LUN,
    '2',FL_PO_MAR,
    '3',FL_PO_MER,
    '4',FL_PO_GIO,
    '5',FL_PO_VEN,
    '6',FL_PO_SAB,
    '7',FL_PO_DOM) AS DAY_OF_WEEK
    FROM DUAL) = 1;