Question

SQL Creator is not Exposing the attributes when I click on "Populate from SQL Query..."

  • 20 March 2023
  • 3 replies
  • 18 views

Badge

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,'') = ''

 


3 replies

Userlevel 2
Badge +13

Hello @pauldeq​, sorry to hear you’re having issues auto-exposing your attributes. After running a quick test in FME 2023.0, with a simple statement, I’m able to automatically populate and expose attributes without issues. May I ask what version of FME Desktop you’re currently using? (Help > About FME Workbench). There are two outstanding issues related to this parameter, but neither seem overly fitting based on your SQL statement.

 

Besides missing the “FME_SQL_DELIMITER ;” at the beginning of your query, there is nothing that looks overly alarming. Have you tried removing certain sections/aspects of your query to see if you can pinpoint which part FME is struggling with (when it comes to exposing the attributes)? If you're able to share the SQL used to create the table you're querying, I'm happy to try and reproduce the issue! Best, Kailin.

Badge

I am running FME Version 2022.1. The problem was trying to get the attributes to be exposed using the "Populate from Query..." button, and nothing populates. I was able to get it to work by just manually entering them, but i still dont know why it would auto-populate when i click that button. Maybe its because i am running and older version?

Userlevel 2
Badge +13

I am running FME Version 2022.1. The problem was trying to get the attributes to be exposed using the "Populate from Query..." button, and nothing populates. I was able to get it to work by just manually entering them, but i still dont know why it would auto-populate when i click that button. Maybe its because i am running and older version?

Hello @pauldeq​, after some further investigation, it looks like the "Populate from Query..." function doesn't always work on more complex joins. I believe this is something the development team is aware of and looking into!

 

Though the data is not displayed by the Visual Preview's Table View on read, you should be able to find the attributes stored in the Feature Information window. For now, it may be best to use an AttributeExposer after your reader feature type and import the attributes from cache (AttributeExposer > Import > From Feature Cache > Select All). This will ultimately perform a similar task! Let me know if you have any issues importing your feature cache! Happy to help, Kailin.

Reply