I know this question has been asked multiple times before, but I've seen every discussion about this here but none could help me to solve my problem.
So my problem is I got this error. From what I understand is this error was caused by column could not determine from which table it would pull the data, hence we can solve it by giving the column name variable such as s.sampleid as ssampleid. Right?
Here are my codes
select * from(select S.S_SAMPLEID,s.collectiondt,SDI.PARAMLISTID||'|'||SDI.PARAMID as paramlistparam,s.u_samcondition,SDI.enteredtext,sdi.displayunits As Unit, sd.s_datasetstatus
case when SDIS.condition = 'Pass' then 'Pass'
when SDIS.condition = 'Fail' then 'Fail'
when SDIS.condition = 'Warning' then 'Warning'
when SDIS.waivedflag = 'Y' then 'Waived'
else 'No Spec' end as quality
from s_sample s left join sdidata sd on s.s_sampleid=sd.keyid1 AND SD.SDCID = 'Sample'
LEFT JOIN SDIDATAITEM SDI ON sd.keyid1 = SDI.KEYID1 AND SDI.SDCID = 'Sample' AND SD.PARAMLISTVERSIONID = SDI.PARAMLISTVERSIONID AND SD.VARIANTID = SDI.VARIANTID AND SD.DATASET = SDI.DATASET AND SD.PARAMLISTID = SDI.PARAMLISTID
LEFT JOIN SDIDATAITEMSPEC SDIS ON SDI.KEYID1 = SDIS.KEYID1 AND SDI.PARAMLISTVERSIONID = SDIS.PARAMLISTVERSIONID AND SDI.VARIANTID = SDIS.VARIANTID AND SDI.DATASET = SDIS.DATASET AND SDI.PARAMLISTID = SDIS.PARAMLISTID AND SDI.PARAMID = SDIS.PARAMID AND SDI.PARAMTYPE = SDIS.PARAMTYPE AND SDIS.SDCID = 'Sample'
where s.samplepointid='"+samplepoint+"' and sdi.paramlistid in ('"+paramlistid+"') and sdi.paramid in ('"+paramid+"') and s.collectiondt between '"+startdt+"' and '"+enddt+"' and sd.s_datasetstatus in ('"+statussql+"') and SDIS.condition in ('"+qualitysql+"'))
pivot(max(enteredtext) as result, max(Unit) as unit for paramlistparam in ('"+paramlistpa+"')) order by collectiondt
And here are what I did by giving variables to the column
select * from(select s.S_SAMPLEID as ssampleid,s.collectiondt,SDI.PARAMLISTID||'|'||SDI.PARAMID as paramlistparam,s.u_samcondition as scondition,SDI.enteredtext as sdienteredtext,SDI.displayunits As Unit, sd.s_datasetstatus as sddataset
case when SDIS.condition = 'Pass' then 'Pass'
when SDIS.condition = 'Fail' then 'Fail'
when SDIS.condition = 'Warning' then 'Warning'
when SDIS.waivedflag = 'Y' then 'Waived'
else 'No Spec' end as quality
from s_sample s left join sdidata sd on s.s_sampleid=sd.keyid1 AND SD.SDCID = 'Sample'
LEFT JOIN SDIDATAITEM SDI ON sd.keyid1 = SDI.KEYID1 AND SDI.SDCID = 'Sample' AND SD.PARAMLISTVERSIONID = SDI.PARAMLISTVERSIONID AND SD.VARIANTID = SDI.VARIANTID AND SD.DATASET = SDI.DATASET AND SD.PARAMLISTID = SDI.PARAMLISTID
LEFT JOIN SDIDATAITEMSPEC SDIS ON SDI.KEYID1 = SDIS.KEYID1 AND SDI.PARAMLISTVERSIONID = SDIS.PARAMLISTVERSIONID AND SDI.VARIANTID = SDIS.VARIANTID AND SDI.DATASET = SDIS.DATASET AND SDI.PARAMLISTID = SDIS.PARAMLISTID AND SDI.PARAMID = SDIS.PARAMID AND SDI.PARAMTYPE = SDIS.PARAMTYPE AND SDIS.SDCID = 'Sample'
where s.samplepointid='"+samplepoint+"' and sdi.paramlistid in ('"+paramlistid+"') and sdi.paramid in ('"+paramid+"') and s.collectiondt between '"+startdt+"' and '"+enddt+"' and sd.s_datasetstatus in ('"+statussql+"') and SDIS.condition in ('"+qualitysql+"')
or s.samplepointid='"+samplepoint+"' and sdi.paramlistid in ('"+paramlistid+"') and sdi.paramid in ('"+paramid+"') and s.collectiondt between '"+startdt+"' and '"+enddt+"' and sd.s_datasetstatus in ('"+statussql+"') and SDIS.condition is null)
pivot(max(sdienteredtext) as result, max(Unit) as unit for paramlistparam in ('"+paramlistpa+"')) order by collectiondt
From what I test, the error was caused by paramlistpa
