I am doing a simple SQL query that I created MS Sql Server. I pasted it into the SQL Creator and ran it, and it was successful. I then go to expose the attributes by populating them from the SQL Query, and nothing shows up. Below is my query. Can someone please tell me how to fix this. I have tried using the Delimiter statement at the top, but that doesn't work either. Here is the query:
With AcscerRecdQry as (Select
lpm.permit_no as PermitNumber,
lpm.permit_id as PermitID,
lpm.version as PermitVersion,
gc.code_desc as PermitStatus,
soclpm.soc_id as ScheduleID,
soclpm.schedule_name as ScheduleName,
(soclpm.ACSCERYearDue - 1) as ACSCERRptPeriod,
soclpm.ACSCERYearDue,
Case
When (IsNull(soclpm.received_dttm,'') = '')
Then 'No'
Else 'Yes'
End as ACSCERRecd,
'Permit Schedule Does Not Exist' as QA_Issue
From nms.dbo.latest_permit_master lpm
Left Outer Join (Select Distinct
slpm.permit_no,
slpm.permit_id,
slpm.version,
slpm.status_cd,
scsd.soc_id,
scssr.schedule_name,
YEAR(scsd.due_dttm) as ACSCERYearDue,
scss.received_dttm
From nms.dbo.latest_permit_master slpm
Inner Join nms.dbo.ce_soc_detail scsd
ON slpm.permit_id = scsd.permit_id
and slpm.version = scsd.permit_version
Left Outer Join nms.dbo.ce_soc_schedule_ref scssr
ON scsd.schedule_id = scssr.schedule_id
Left Outer Join nms.dbo.ce_soc_submittal scss
ON scsd.soc_id = scss.soc_id) soclpm
ON lpm.permit_id = soclpm.permit_id
and lpm.version = soclpm.version
Left Outer Join nms.dbo.ge_codes gc
ON lpm.status_cd = gc.code_value
and gc.code_group = 'PERM_STATUS'
Where IsNull(soclpm.permit_id,'') = ''
AND lpm.permit_no like 'OKG11%'
AND NOT(lpm.permit_no like 'OKG11MT%')
UNION
(Select
lpm.permit_no as PermitNumber,
lpm.permit_id as PermitID,
lpm.version as PermitVersion,
gc.code_desc as PermitStatus,
csd.soc_id as ScheduleID,
cssr.schedule_name as ScheduleName,
(YEAR(csd.due_dttm) - 1) as ACSCERRptPeriod,
YEAR(csd.due_dttm) as ACSCERYearDue,
Case
When IsNull(css.received_dttm,'') = ''
Then 'No'
Else 'Yes'
End as ACSCERRecd,
Case
When IsNull(css.received_dttm,'') = ''
Then 'ACSCER Not Received'
Else 'ASCER Received'
End as QA_Issue
From nms.dbo.latest_permit_master lpm
Inner Join nms.dbo.ce_soc_detail csd
ON lpm.permit_id = csd.permit_id
and lpm.version = csd.permit_version
Inner Join nms.dbo.ce_soc_submittal css
ON csd.soc_id = css.soc_id
Left Outer Join nms.dbo.ge_codes gc
ON lpm.status_cd = gc.code_value
and gc.code_group = 'PERM_STATUS'
Left Outer Join nms.dbo.ce_soc_schedule_ref cssr
ON csd.schedule_id = cssr.schedule_id
Where lpm.permit_no like 'OKG11%'
AND NOT(lpm.permit_no like 'OKG11MT%')))
Select
arq.PermitNumber,
arq.PermitID,
arq.PermitVersion,
arq.PermitStatus,
arq.ScheduleID,
arq.ScheduleName,
arq.ACSCERRptPeriod,
arq.ACSCERYearDue,
arq.ACSCERRecd,
arq.QA_Issue
From AcscerRecdQry arq
Where arq.ACSCERRptPeriod in (2019,2020,2021,2022)
OR IsNull(arq.ACSCERRptPeriod,'') = ''
