CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sly
Sly

Reputation: 73

Oracle subquery returning an error

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');

Upvotes: 1

Views: 129

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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

Upvotes: 2

Related Questions