Improper column reference in the search condition of a joined table. PLZ HELP

UDA

Improper column reference in the search condition of a joined table. PLZ HELP

Select

A.Column1
,B.Column1
Case When C.column1 is null
and C.column2 is null

then 'y'
else 'n'
end flag

from STG.table1 as A
,STG.table2 as B

left outer join
TGT.table1 as C
on A.column1 = C.column1
and B.column1 = c.column2

Why am i getting this error??

Improper column reference in the search condition of a joined table. please help.

Thanks
4 REPLIES

Re: Improper column reference in the search condition of a joined table. PLZ HELP

You are doing left outer join between B & C. So condition you use in ON clause can not reference columns of A. Your query is resulting into error because of condition "A.column1 = C.column1". It will not give error if you remove that condition.

Is there any join relation between A & B?

Re: Improper column reference in the search condition of a joined table. PLZ HELP

What i m trying to do is figure out if the records in
the staging tables exist or do not exist in the core table.

This was the logic i came up with for that. So if the flag
is n it means that they don't exist if its y they do exist.

Now what i want to do is insert the records that don't exist
into the target table, plus generate the surrogate key.

So my target table has column1 column2 plus a SK column.

How would i do this? I don't want to create a volatile
table. I m stuck as how to. What i m doing right now is
this.

Insert into Target table

select

stg.column1
stg.column2
one up the SK
MY CASE STATEMENT

from stg.table 1

left outer join
to Target table.

where case statement logic = n

This isn't working. And i m stuck. Please help.

Re: Improper column reference in the search condition of a joined table. PLZ HELP

Sorry, I am little confused with your explanation. In original query you posted, there were three tables. There is no join condition specified between A &B. Now recent query you are showing has only join between two tables namely stage, core.

If you want to find all records in stage which are not present in core. This can be done using simple outer join. Not sure if this is what you are looking for.

select
stage.table1.*
from
stage.table1 left outer join core.table2 ON (stage.table1.col1 = core.table2.col2)
where
core.table2.col2 is NULL --I am assuming join column is not null.

Re: Improper column reference in the search condition of a joined table. PLZ HELP

Hey Ravi,

Thanks for your help but i think i got what i was trying to accomplish. Your input would be helpful.

This is what i was trying to do, and is how i did it.

Insert into DEV_CORE.TABLE1 (SK, column1, column2...)

Select

DEV_STG.SKVALUE + row_number () over(order by A.COL1)
,A.COL1
,A.COl2
,'i'

From

(Select
STG.COL1
,STG.COL2
,Case When CORE.COl1 is null
AND CORE.COL2 is null
Then 'N'
Else 'Y'
End Exist
From
DEV_STG.TABLE1 as STG
LEFT OUTER JOIN
DEV_CORE.TABLE1 as CORE
ON STG.Col1 = CORE.COl1
AND STG.Col2 = CORE.col2

Where exist = 'N'

) as A

So in my first select i 1 up the SK and insert all the records that don't exist in my target table, which is what i m finding out in my sub query. Now this logic is for one staging table going into one target table.

My first post, which had the two different staging tables, i m going to be doing what i want to do in this sql but i m going to have 2 staging tables going into 1 target. Those two staging tables don't have any correlation. Basically i need the two values from there corresponding tables. So i m going to grab col1 from stg.table1 and col1 from stg.table2 and then somehow insert those values into my one target table. How would i do that?