Oracle subquery returning an error

Question

I am working on the below query, I want to return a date column (DATE1) to the outer query for each candidate. Since each candidate can have multiple date entries, I want it to return those multiple entries where they exist.

I am getting the above error and other times I get

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

SELECT DISTINCT xyf.can_num, 
                xyf.associate_id, 
                event_f.pi_his_itm_app_trk_creation_dt, 
                xyf.offer_accepted_date, 
                xyf.conversion_hire_date, 
                xyf.parent_pi_number, 
                information_d.job_sub_family_name, 
                information_d.job_family_name, 
                event_f.contest_number, 
                xyf.full_time_offer_location, 
                xyf.associate_name 
FROM            ( 
                       SELECT * 
                       FROM   ( 
                                         SELECT     event_f.pi_his_itm_app_trk_creation_dt 
                                         FROM       event_f 
                                         inner join xyf 
                                         ON         xyf.can_num = event_f.can_num 
                                         inner join information_d 
                                         ON         event_f.job_info_row_wid = information_d.row_wid
                                         WHERE      information_d.job_family_name IN ('MP', 
                                                                                      'PLE', 
                                                                                      'EP', 
                                                                                      'Other') 
                                         AND        event_f.pi_his_itm_app_trk_sts_name = 'Extended'
                                         AND        event_f.pi_his_itm_app_trk_step_name = 'Offer'
                                         AND        information_d.title NOT IN ('Student Ambassador Program for Eligible Summer Interns',
                                                                                'Student Ambassador')
                                         AND        event_f.pi_his_itm_app_trk_sts_name = 'Extended'
                                         AND        event_f.pi_his_itm_app_trk_step_name = 'Offer')) AS date1
from            xyf 
inner join      event_f 
ON              xyf.can_num = event_f.can_num 
inner join      information_d 
ON              event_f.job_info_row_wid = information_d.row_wid 
WHERE           information_d.job_family_name IN ('MP', 
                                                  'PLE', 
                                                  'EP', 
                                                  'Other') 
AND             event_f.pi_his_itm_app_trk_sts_name = 'Extended' 
AND             event_f.pi_his_itm_app_trk_step_name = 'Offer' 
AND             information_d.title NOT IN ('Student Ambassador Program for Eligible Summer Interns',
                                            'Student Ambassador');

I am working on the below query, I want to return a date column(DATE1) to outer query for each candidate, since each candidate can have multiple date entries, I want it to return those multiple entries where they exist. I am getting the above error and other times I get ( ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" — Sly, Aug 22 '18 at 17:59
You seem to be using SQL Developer, which points you to a line and character number to investigate the error. In this case, `date1` is a table alias, and you can't use `AS` for table aliases; so change `AS date1` to just `date1`. That should fix the initial ORA-00933. What is the other error you refer to, and when do you see that? You seem to have two `from` clauses at the same level of query too... — Alex Poole, Aug 22 '18 at 18:08
I have made changes as you suggested but Sql developer still saying there is an error at or date1, the previous error I was getting about query returning multiple rows when the outer query expected single row is not showing up — Sly, Aug 22 '18 at 18:23

Alex Poole · Accepted Answer · 2018-08-22T18:33:17.397

The initial ORA-00933 is because of the AS date1, which is currently a table alias, and you can't use AS for those in Oracle.

But you also have two from clauses, which will cause a further ORA-00933 as it isn't expecting the second one.

From your description, referring to date1 as a date column, you actually meant that subquery to be a column expression and not an inline view. So maybe you really want something like:

...
                xyf.associate_name, 
                (
                       SELECT     event_f.pi_his_itm_app_trk_creation_dt 
                       FROM       event_f 
                       inner join xyf 
                       ON         xyf.can_num = event_f.can_num 
                       inner join information_d 
                       ON         event_f.job_info_row_wid = information_d.row_wid
                       WHERE      information_d.job_family_name IN ('MP', 
                                                                    'PLE', 
                                                                    'EP', 
                                                                    'Other') 
                       AND        event_f.pi_his_itm_app_trk_sts_name = 'Extended'
                       AND        event_f.pi_his_itm_app_trk_step_name = 'Offer'
                       AND        information_d.title NOT IN ('Student Ambassador Program for Eligible Summer Interns',
                                                              'Student Ambassador')
                ) AS date1
from            xyf 
...

I've removed the first FROM and added a comma to the preceding line; and removed a redundant inner subquery, and duplicated filters.

Whether the overall query makes sense, or if that is the most efficient way to get the date value, is another matter. The subquery looks remarkably like the outer query, so I'm not sure why you aren't just referring to the column you want directly in the outer query:

...
                xyf.associate_name, 
                event_f.pi_his_itm_app_trk_creation_dt as date1
from            xyf 
...

You may have a reason for using a subquery, and a way to correlate it with the outer query rows, but it isn't obvious to me.

the previous error I was getting about query returning multiple rows when the outer query expected single row

That's because there is no correlation between the outer query and subquery. If you run the subquery on its own it will presumably return multiple rows, and you're running that - and trying to include all of those rows - for every row in the outer query. You can't have multiple rows in a scalar column expression, hence the "ORA-01427: single-row subquery returns more than one row" error you got. You either need to add some correlation - which will be simpler if change the table aliases inside the subquery so they aren't the same as the outer query - or remove the subquery completely if it isn't actually needed (see above).

I have tested it but still not executing. In case you missed what I am trying to accomplish, here is my requirement: each candidate can receive multiple job offers on same date or different date, so what I am trying to do is to get the inner query to pull up all the dates that a candidate was offered a job. be it on same day or different day. — Sly, Aug 22 '18 at 18:32
Code and results don't really work in comments, you could edit your question to include sample data and results. If you try it without a subquery, and just getting `pi_his_itm_app_trk_creation_dt` in the outer query, you should get one row for every date. If that isn't what you want then you need to explain what you do want, but you can't have a variable number of columns. The simplest thing is to use `listagg()` to turn them into a single string expression, but that might not be what you want either. — Alex Poole, Aug 22 '18 at 18:37
If I try the inner query itself, I am getting the desired results, I am only getting error when I use it with the main query. so basically if a candidate has more than one offer dates I want those dates listed / returned by the inner query. — Sly, Aug 22 '18 at 18:52
it will be in a row: date1 is a row which will have dates that a candidate was offered a job — Sly, Aug 22 '18 at 18:55
I don't know what that means. You can't have a row within a row. You can have a single aggregated column, or multiple columns via a pivot, or even a collection. Maybe you should edit your question (or ask a new one) showing sample data and what ypu actually want to see. — Alex Poole, Aug 22 '18 at 19:00
sorry my apologies, that was a typo. date1 is a column that will contain rows of the dates that a candidate was offered a job — Sly, Aug 22 '18 at 19:01
But a column still can't contain a row. Probably best to ask a new question with an [MCVE](https://stackoverflow.com/help/mcve) with simplified data. — Alex Poole, Aug 22 '18 at 19:12

Oracle subquery returning an error

1 Answers1