• EN
Question

SQL Query

  • 15 March 2024
  • 4 replies
  • 46 views
gehanis2
Rank
Badge +2

I am attempting to get the value of a date field from a subset of data tables in an Enterprise geodatabase.  I have narrowed down the list of tables with the required attribute from the SDE_column_registry and I only want to query those data tables for the value of that date field.

I have attached two screenshots showing the inner join and rejected output and part of the translation.log.  Perhaps I am formulating my query incorrectly. I am only concerned with getting the value of the date field from 133 data tables that have the attribute.

Please help.

Sheila

County of Kern, CA

3 Attachments

4 replies

hkingsbury
Rank
Userlevel 5
Badge +29

What does your log file say? Also, your first line has an equals expression in the SELECT. This should be part of the WHERE?

Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
ctredinnick
Rank
Userlevel 3
Badge +16

I don’t think the join to the sde column registry is required, since you’re already dynamically providing the database, owner, table, column name. Unless you’re using the inner join to ensure that the column is strictly in SDE, and not some column from any table.

One other thing you can do is put your SQL construction into an attributecreator, run it, then copy the statement into SSMS to test run it there and have it check your syntax.

 

Something like this should be all you need?

SELECT
 @Value(column_name) as ResultColumn
FROM
 @Value(datebase_name).@Value(owner).@Value(table_name)
WHERE
 OBJECTID = 1

or even SELECT TOP (1) instead of getting the first row.

gehanis2
Rank
Badge +2

In the results, I do get the dates returned from the date attribute.  However, there is no database name, username or table name associated with them because that cannot be captured from the contents of the data tables.  Those fields show up empty.  It isn’t the first part of the query that is a problem, it is the second part.

However, both suggestions are useful, and I will try them both.

 

Sheila

gehanis2
Rank
Badge +2

I solved my problem by changing a parameter value in the SQL Executor transformer.

Thank you for your help.  I did use the SELECT TOP(1) in my query.

1 Attachment

Reply


Community Stats

28,868
Posts
109,990
Replies
36,680
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.