SQL-getting error-"Message=ORA-00918: column ambiguously defined"

Transact-SQL
1

Hello All,
I am new to SQL. I am getting error. Please, help me

With status as(
Select
lemnum,
count(
case when status = 'COMP' then linenum end
) as COMP_line,
count(
case when status = 'POSTED' then linenum end
) as POSTED_line,
count(
case when status = 'SUBMIT' then linenum end
) as SUBMIT_line,
count(
case when status = 'WAPPR' then linenum end
) as WAPPR_line
FROM
BI_HZ_ETL.LEM_FACTS
Group by
lemnum
)
SELECT
status.*,
v.vendor_name,
lf.LINENUM,
lf.LEMNUM,
lf.VENDOR,
lf.WORKDATE,
lf.SITEID,
lf.laborhrs,
lf.equiphrs
FROM
BI_HZ_ETL.LEM_FACTS lf
join status on (LEMNum = lf.LEMNUM)
Left JOIN bi_hz_etl.LEM_vendor v ON (lf.vendor = v.vendor_id)
where
lf.siteid in('HORIZON', 'ALBIAN') and STATUS not in('DELETED', 'NEW', 'REJECT')

Thanks

2

This is Microsoft SQL Server forum. But someone might be able to help you with it.

Found this on google

Issue could be you are not referring the source table in this join

FROM
BI_HZ_ETL.LEM_FACTS lf
join status on (LEMNum = lf.LEMNUM)

Change it to

FROM
BI_HZ_ETL.LEM_FACTS lf
join status  on (status.LEMNum = lf.LEMNUM)
1 Like
3

Thanks Yosiasz.
ITs worked