topic FGDB database view — Casting date as text in SELECT clause results in blank field in ArcGIS Pro Questions

FGDB database view — Casting date as text in SELECT clause results in blank field

Bud — Mon, 22 Jan 2024 06:05:46 GMT

ArcGIS Pro 3.2.1; file geodatabase

I can create this FGDB database view without errors:

select 
  s.*,
  cast(t_date as char(50)) as test_col
from 
  species_records s

However, the field that uses CAST is blank in the attribute table. That's not expected.

I don't have that problem if I remove s.* and replace it with the fields listed one by one:

select 
  objectid,
  t_unique_id,
  t_species,
  t_common_name,
  t_taxon_group,
  t_threat,
  t_grid_refer,
  t_site,
  t_date,
  cast(t_date as char(50)) as test_col
from 
  species_records s

And I don't have the s.* problem in a mobile geodatabase view (although the date is displayed as a number, not a textual date, but maybe that's expected for SQLite date fields):

select 
  s.*,
  cast(t_date as char(50)) as test_col
from 
  species_records s

Question:

Why does casting a date field in the SELECT clause result in a blank column — when the query uses s.* ?

Re: FGDB database view — Casting date as text in SELECT clause results in blank field

Robert_LeClair — Mon, 22 Jan 2024 17:57:17 GMT

A file-based data including file geodatabases and all content in it that use standardized queries use the ArcGIS SQL dialect that supports a subset of SQL capabilities. In other words, it has limitations on the SQL expressions that are available in a Data Base Management System such as Oracle or SQL Server. On the other hand, databases or enterprise geodatabases use the SQL syntax of the underlying RDBMS, such as, Oracle, SQL Server, PostgreSQL, SAP HANA, and IBM Db2, where each database uses its own slightly different SQL dialect.

So it seems this is known limitation of using the s.* in a database view for a file geodatabase.

Re: FGDB database view — Casting date as text in SELECT clause results in blank field

Bud — Tue, 30 Jan 2024 04:04:25 GMT

For anyone who's wondering, file geodatabases also support the SQL syntax mentioned on this page:
SQL for reporting and analysis on file geodatabases

That document mentions that aliases are supported. s.* is an alias. Yes, it only mentions column aliases, but it seems like table aliases are generally supported too.

s.* works in other FGDB SQL scenarios, such as the database view in this post:
Selecting the most recent records based on unique values in another field