Re: Weird sql response, could not explain

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

--
http://www.freelists.org/webpage/oracle-l