Oracle SQL inner select doesn't work

Question

I have a problem with my pl/sql statement.

This is the good working select statement:

SELECT P.DAYS FROM PLPLANPEAKPROGRAM P WHERE P.ID = 1060;

Returns; 1,2,3 (varchar2)

The main sql is here:

SELECT * 
from FIFLIGHTS   
LEFT JOIN PLPLANPEAKPROGRAMCOPY ON FIFLIGHTS.IATACODE = PLPLANPEAKPROGRAMCOPY.STATIONCODE   
WHERE  TO_CHAR(FIFLIGHTS.SCHEDULEDTIME, 'D') IN (SELECT  P.DAYS 
                                                 FROM PLPLANPEAKPROGRAM P 
                                                 WHERE P.ID = 1060) ;

But this doesn't return any result.

Also this above is returned well;

SELECT * 
from FIFLIGHTS   
LEFT JOIN PLPLANPEAKPROGRAMCOPY ON FIFLIGHTS.IATACODE = LPLANPEAKPROGRAMCOPY.STATIONCODE   
WHERE TO_CHAR(FIFLIGHTS.SCHEDULEDTIME, 'D') IN (1,2,3);

What's problem and solution, please help ?

also what it this ` TO_CHAR(FIFLIGHTS.SCHEDULEDTIME, 'D') `? — bumbumpaw, Jul 19 '14 at 00:46

score 2 · Accepted Answer · answered Jul 19 '14 at 01:50

You say that:

SELECT P.DAYS
FROM PLPLANPEAKPROGRAM P
WHERE P.ID = 1060;

Returns 1,2,3. That means that it returns a single string value. Not a list with three elements. The to_char() function call returns single values, such as 1 or 2. There are no commas, so these cannot match.

This is a bad data format. You should be storing the list with one element per row. SQL offers a very powerful way to store lists, called tables. That said, you can solve this problem with exists:

SELECT *
from FIFLIGHTS LEFT JOIN
     PLPLANPEAKPROGRAMCOPY
     ON FIFLIGHTS.IATACODE = PLPLANPEAKPROGRAMCOPY.STATIONCODE
WHERE EXISTS (SELECT 1
              FROM PLPLANPEAKPROGRAM P
              WHERE P.ID = 1060 and
                    P.DAYS LIKE '%' || TO_CHAR(FIFLIGHTS.SCHEDULEDTIME, 'D') || '%'
             );

score 0 · Answer 2 · answered Jul 19 '14 at 00:34

0

SELECT * from FIFLIGHTS LEFT JOIN 
PLPLANPEAKPROGRAMCOPY ON FIFLIGHTS.IATACODE = PLPLANPEAKPROGRAMCOPY.STATIONCODE
WHERE TO_CHAR(FIFLIGHTS.SCHEDULEDTIME, 'D') 
IN (SELECT P.DAYS FROM PLPLANPEAKPROGRAM P WHERE P.ID = '1060') ;

add single quote to your argument '1060'

answered Jul 19 '14 at 00:34

bumbumpaw

2,522
1
24
54

@volkan no im sorry, can you give me the 3 columns you are using in `PLPLANPEAKPROGRAM ` – bumbumpaw Jul 19 '14 at 00:43
@volkan or you can try to check this link http://stackoverflow.com/questions/8585592/using-select-within-select-in-mysql-query – bumbumpaw Jul 19 '14 at 00:44
Sure STATIONCODE, DEPARTMENTNAME, DAYS are columns in PLPLANPEAKPROGRAM – volkan Jul 19 '14 at 00:47

Oracle SQL inner select doesn't work

2 Answers2