topic Re: Unexpected Where Clause behaviour in dictionary.TABLE_CONSTRAINTS (TABLE_CATALOG) in SAS Programming

Unexpected Where Clause behaviour in dictionary.TABLE_CONSTRAINTS (TABLE_CATALOG)

AllanBowe — Fri, 01 Oct 2021 11:00:03 GMT

It appears to be a bug on the TABLE_CATALOG field in dictionary.TABLE_CONSTRAINTS

proc sql;
create table work.example(
  TX_FROM float format=datetime19.,
  DD_TYPE char(16),
  DD_SOURCE char(2048),
  DD_SHORTDESC char(256),
  constraint pk primary key(tx_from, dd_type,dd_source),
  constraint unq unique(tx_from, dd_type),
  constraint nnn not null(DD_SHORTDESC)
);

/* this table has no records */
create table work.zero_records as 
  select * from dictionary.TABLE_CONSTRAINTS 
  where upcase(TABLE_CATALOG)="WORK";

/* this table has expected records */
create table work.has_records as 
  select * from dictionary.TABLE_CONSTRAINTS 
  where TABLE_CATALOG="WORK";

The behaviour is exhibited with other functions as well. The behaviour does not apply to other columns (eg TABLE_NAME).

SYSVLONG=9.04.01M7P080520

Re: Unexpected Where Clause behaviour in dictionary.TABLE_CONSTRAINTS (TABLE_CATALOG)

Oligolas — Fri, 01 Oct 2021 13:18:55 GMT

Indeed.

These methods would work

create table work.has_records1 as 
  select * ,upcase(TABLE_CATALOG) as test from sashelp.vtabcon
  where calculated test eq upper("WoRK");

create table work.has_records2 as 
  select *  from (select *,TABLE_CATALOG as TABLE_CATALOG1 from sashelp.vtabcon)
  where upcase(TABLE_CATALOG1) eq 'WORK';

But not these ones either:


create table work.zero_records1 as 
  select *  from sashelp.vtabcon
  where upper(TABLE_CATALOG) eq 'WORK';

/* this table has no records */
create table work.zero_records2 as 
  select * from dictionary.TABLE_CONSTRAINTS 
  where prxmatch('/work/oi',strip(TABLE_CATALOG));

create table work.zero_records3 as 
  select *  from sashelp.vtabcon
  where strip(lower(TABLE_CATALOG)) eq 'work';

Re: Unexpected Where Clause behaviour in dictionary.TABLE_CONSTRAINTS (TABLE_CATALOG)

AllanBowe — Fri, 01 Oct 2021 13:24:33 GMT

Thanks @Oligolas - indeed, that was the approach I used to fix my issue: https://github.com/sasjs/core/pull/84/files#diff-60df2277f57ab4c7bbd043138958de8dc28e6ccddfddb9e8501bd52facf6e585R56

Interesting to see that the problem applies to sashelp.vtabcon also. And nice idea with the subquery.

Also credit to @yabwon who investigated behind the scenes, which resulted in a vastly refined / simpler question.