I am NEW to SQL writing and I am getting a error message

Database

I am NEW to SQL writing and I am getting a error message

I am getting an error message that says: Improper Column reference in a search condition of join

SEL

esf.MTN,

esf.ESN_NUM,

esf.ESN_CHANGE_REAS_CD,

coalesce (esf.device_prod_nm,esf.prod_nm) as PROD_NAME,

case when O.MASTER_AGENT_ID = 'APPLE'

then 'APPLE'

else 'VERIZON'

end as Location

From

ntl_prd_secvm.CUST_ACCT_LINE_V CALT

left outer join ntl_prd_secvm.POS_TRANS_V POS

on esf.sor_id=pos.sor_id

and esf.cust_id=pos.cust_id

and esf.cust_line_seq_id= pos.cust_line_seq_id

and esf.invc_num=pos.invc_num

inner join

ntl_prd_secvm.equip_sum_fact_v esf

on

ESF.ESN_CHANGE_REAS_CD = CALT.ESN_CHANGE_REAS_CD

AND

ESF.SOR_ID = CALT.SOR_ID

AND

ESF.MTN = CALT.MTN

AND

ESF.CUST_LINE_SEQ_ID = CALT.CUST_LINE_SEQ_ID

inner join OUTLET_V O

ON POS.SOR_ID = O.SOR_ID

AND POS.SLS_OUTLET_ID = O.SLS_OUTLET_ID

AND (MASTER_AGENT_ID LIKE '%APPLE%' OR STORE_TYPE_IND = 'S')

where esf.INVC_DT between '1120101' and '1120131'

and esf.ITEM_CD LIKE 'CLNRAPL%'

5 REPLIES
Enthusiast

Re: I am NEW to SQL writing and I am getting a error message

I think the last condition in the AND clause is causing the problem. You can't have LIKE operator in a JOIN clause. You need to move the LIKE condition (MASTER_AGENT_ID LIKE '%APPLE%') in WHERE clause...

Re: I am NEW to SQL writing and I am getting a error message

thanks for your advise Qaisar, but I made the changes you remended but the query is still NOT running. I am getting the same error code.

Enthusiast

Re: I am NEW to SQL writing and I am getting a error message

I thought it would work. Anyways, can you share the DDL of referenced tables to have a look.

Senior Apprentice

Re: I am NEW to SQL writing and I am getting a error message

Of course you can have a LIKE in a join condition.

You just wrote the joins in wrong order. In ON you can/must use columns from any of the previously defined tables.

Rearrange it like this:

FROM
ntl_prd_secvm.CUST_ACCT_LINE_V CALT
INNER JOIN
ntl_prd_secvm.equip_sum_fact_v esf
ON ESF.ESN_CHANGE_REAS_CD = CALT.ESN_CHANGE_REAS_CD
AND ESF.SOR_ID = CALT.SOR_ID
AND ESF.MTN = CALT.MTN
AND ESF.CUST_LINE_SEQ_ID = CALT.CUST_LINE_SEQ_ID
LEFT OUTER JOIN
ntl_prd_secvm.POS_TRANS_V POS
ON esf.sor_id =pos.sor_id
AND esf.cust_id =pos.cust_id
AND esf.cust_line_seq_id= pos.cust_line_seq_id
AND esf.invc_num=pos.invc_num
INNER JOIN
OUTLET_V O
ON
POS.SOR_ID = O.SOR_ID
AND POS.SLS_OUTLET_ID = O.SLS_OUTLET_ID
AND
(
MASTER_AGENT_ID LIKE '%APPLE%'
OR STORE_TYPE_IND = 'S'
)

This query will change the Left ton an Inner Join, you should check if OUTLET_V must be outer joined, too.

Dieter

mjj
Teradata Employee

Re: I am NEW to SQL writing and I am getting a error message

ntl_prd_secvm.CUST_ACCT_LINE_V CALT

left outer join ntl_prd_secvm.POS_TRANS_V POS

on esf.sor_id=pos.sor_id

and esf.cust_id=pos.cust_id

and esf.cust_line_seq_id= pos.cust_line_seq_id

and esf.invc_num=pos.invc_num

The Joining order is wrong I think. Here CALT and POS joining conditions need to be specified instead of ESF and POS.

Regards,