topic Re: Error in PROC SQL query in SAS Programming

Error in PROC SQL query

Aexor — Sat, 18 Feb 2023 20:18:43 GMT

Dears,

I need help in understanding a error

ERROR: CLI execute error: ERROR: operator does not exist: uuid = character varying;Error while executing the query

proc sql ;
connect to POSTGRES (<server-password details>);
execute ( update X.tableX set plan_lock_flg = true, process_status_no=41,
modified_by_user_id = 'tst', modified_dttm = current_timestamp where id in (select pp_id from
X.tableY) ) by POSTGRES;
ERROR: CLI execute error: ERROR: operator does not exist: uuid = character varying;Error while executing the query

But the same query is working in when I give value in the inner query in below format

eaxmple :

proc sql ;
connect to POSTGRES (<server-password details>);
execute ( update X.tableX set plan_lock_flg = true, process_status_no=41,
modified_by_user_id = 'tst', modified_dttm = current_timestamp where id in ('00b3553b-408d-41b2-8ed3-cee324187d6e' , '018b781e-cd65-460a-8faa-5b4e57dbff49') ) by POSTGRES;

value of TableY is as below

How can I resolve this error. Please help.

Thanks!

Re: Error in PROC SQL query

Quentin — Sat, 18 Feb 2023 21:08:02 GMT

I'm not a postgres person, but I googled a bit and I'm guessing the problem is your ID column is type varchar but your PP_ID column is type UUID. Don't know if you can simply CAST your UUID column as varchar, but it's worth a shot. This stack exchange question seems similar, don't think it's quite an answer for you:

https://dba.stackexchange.com/questions/177733/postgresql-custom-operator-uuid-to-varchar

Note that the error message is coming from postgres, not SAS. So if you google:

postgres "ERROR: operator does not exist: uuid = character varying"

you may find a solution.

Or try posting on stack overflow with a postgres tag. I wouldn't mention SAS if you go that route, you can just post the postgres query.

Re: Error in PROC SQL query

Patrick — Sun, 19 Feb 2023 14:30:38 GMT

I normally develop explicit pass-through SQL directly via a database client and only integrate into a SAS process once that's fully working.

What @Quentin proposes will work. See PG test script below developed using the PG client PGAdmin.

1. Create and populate a temporary PG table for testing.

--create temporary table with a varchar and a uuid column
drop table if exists test;
create temporary table if not exists test 
	(
		 col_uuid uuid
		,col_varchar character varying(40)
	)
	;
	
--populate columns
insert into test values('00b3553b-408d-41b2-8ed3-cee324187d6e','00b3553b-408d-41b2-8ed3-cee324187d6e');

2. Where clause without type casting to replicate the error you observe.

--test for where clause with different data types
select * from test where col_uuid = col_varchar;

...and here what Postgres returns:

ERROR:  operator does not exist: uuid = character varying
LINE 14: select * from test where col_uuid = col_varchar;
                                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 403

3. Where clause with type casting - two syntax options tested.

--test for where clause casting varchar to uuid
select * from test where col_uuid = cast(col_varchar as uuid);

select * from test where col_uuid = col_varchar::uuid;

...and this works.

Above tests done with Postgres 15 but I would be rather surprised if such syntax isn't supported in older versions.

And as a related topic:

Try to avoid explicit passing of credentials (even if the password is SAS encoded). Ideally use an authentication domain or if there is already a pre-assigned SAS library then you can also use this libref as documented here.

Re: Error in PROC SQL query

Aexor — Thu, 23 Feb 2023 13:44:34 GMT

Sorry. I need one more info. Cas we have CAST kind of functionality outside pass through query. To use CAST function in later step . I have to load the dataset in PG schema and then I am using CAST . Can we remove the LOADING overhead and use CAST like functional in PROC SQL itself . Thank You!

Re: Error in PROC SQL query

Patrick — Thu, 23 Feb 2023 22:57:38 GMT

@Aexor wrote:
Sorry. I need one more info. Cas we have CAST kind of functionality outside pass through query. To use CAST function in later step . I have to load the dataset in PG schema and then I am using CAST . Can we remove the LOADING overhead and use CAST like functional in PROC SQL itself . Thank You!

Are you talking about loading the data first into SAS and then do the join within SAS?

If so then SAS 9.4 got only two data types (character and numeric). A UUID column will get converted to a character type column same as a Varchar so you wouldn't need any type casting anymore.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1prjok4ko5rf4n1h2rf5ok8d2ct.htm

With SAS type casting is done via put/input functions.