Error found by SQL Complete, can't format a valid query
Posted: Mon 29 Dec 2014 02:40
Hi,
I have this query from an object, which runs fine. When I try to use SQL Complete to format it though, it fails with this error message:
I have this query from an object, which runs fine. When I try to use SQL Complete to format it though, it fails with this error message:
The error reported in the output window is:Formatter has found some syntax errors in the text. The statements that contain errors are not formatted. See the output window for details.
Any idea of why this is failing?Error (3,43): 'Declaration' expected
Code: Select all
select *
from
(
select [HOLEID],
[PROJECTCODE]
,[HOLETYPE]
,([dbo].[QFN_COLLAR_DESPATCH_LIST](hl.[HOLEID],hl.[PROJECTCODE],'DESPATCHNO',',')) as [DespatchNumber_D]
,([dbo].[QFN_COLLAR_CORP_LABJ_LIST](hl.[HOLEID],hl.[PROJECTCODE],'LABJOBNO',',')) as [LabjobNumber_D]
,stuff(
(
select ',' + (case
when d.MINPRIORITY > 2
then 'Rejected'
when d.MINPRIORITY <> d.MAXPRIORITY
then 'Partial'
when d.MINPRIORITY = 2
then 'Pending'
when d.MINPRIORITY = 1
then 'Accepted'
else ''
end) as QAQCStatus
from
(
select clp.LABJOBNO
,min(csa.[PRIORITY]) as [MINPRIORITY]
,max(csa.[PRIORITY]) as [MAXPRIORITY]
from CORPSAMPLEASSAY as csa
join [SAMPLE] as s
on s.SAMPLEID = csa.SAMPLEID
join SAMPLEDESPATCH as sd
on s.SAMPLEID = sd.SAMPLEID
join CORPLABPROPERTIES as clp
on clp.DESPATCHNO = sd.DESPATCHNO
and clp.LABJOBNO = csa.LABJOBNO
and clp.NAME = csa.NAME
where s.PROJECTCODE = hl.[PROJECTCODE]
and s.HOLEID = hl.[HOLEID]
group by clp.LABJOBNO
) as d
order by LABJOBNO
for xml path (''),type
).value('.','varchar(4000)')
,1,1,'') as [LabjobNumberStatus_D]
from [HOLELOCATION] as hl
) [ACQTMP]