Re: Weird sql response, could not explain
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: dd.yakkali@xxxxxxxxx
- Date: Mon, 26 Jul 2010 17:17:04 -0600
Not weird at all, just unexpected...
Based on the statement,
There is a column "JOB_ID" in the table "JOB_RUNS".
There is not a column named "JOB_ID" in "LOAD_JOB_PROFILES"
You are not using aliases or fully qualified column names
So...the JOB_ID in the subquery resolves to JOB_RUNS.JOB_ID since there
is no LOAD_JOB_PROFILES.JOB_ID
To prevent this behavior, use fully qualified column names (with
table_name or table alias)
dd yakkali wrote:
Can someone help me explain this weird behaviour.
when a subquery has a syntactical error what happens to the parent query.
select job_id , job_run_id , job_run_status_id
from job_runs
where dataset_date = trunc(sysdate)
and job_id in
( select job_id from load_job_profiles where load_job_profile_id =
33244 )
and job_run_status_id <> 6
...
1714260 77168858 11
1714260 77168859 7
1714260 77168860 7
1714260 77168862 11
1714260 77168863 7
1714260 77168865 7
1714260 77168868 11
1714260 77168869 7
.....
gives me bunch of results.
where as I run
select job_id from load_job_profiles where load_job_profile_id = 33244
ERROR at line 1:
ORA-00904: "JOB_ID": invalid identifier
Thanks
Deen
Other related posts:
