Desktop productivity for business analysts and programmers

numeric works, non-numeric fails in my where statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

numeric works, non-numeric fails in my where statement

Hi, When I run this piece of code (query a numeric variable) it works proc sql noprint; select Centre, Branch, Product, Contract into :Centre, :Branch, Smiley Tongueroduct, :Contract from DD1; %put &Centre &Branch &Product &Contract; PROC SQL; connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD2 AS SELECT * FROM CONNECTION TO odbc ( SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD1 AS Key FROM EXAMPLETABLE where FIELD1=&Centre AND FIELD2=&Branch AND FIELD3=&Product AND FIELD4=&Contract for fetch only with ur; ); This returns the data I was expecting - but when I run a similar piece of code it fails and I think it's because the variable I'm querying in the WHERE statement is non-numeric. Do I need to do something different? proc sql noprint; select CheckUp1, CheckUp2 into :CheckUp1, :CheckUp2 from DD2; %put &CheckUp1 &CheckUp2; PROC SQL; connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD3 AS SELECT * FROM CONNECTION TO odbc ( SELECT FIELD6, FIELD7, FIELD8, FIELD9, FIELD6 AS Key FROM EXAMPLETABLE2 where FIELD6=&CheckUp1 AND FIELD7=&CheckUp2 for fetch only with ur; ); Any help much appreciated - thanks

Accepted Solutions
Solution
‎02-28-2018 09:10 AM
Super User
Super User
Posts: 8,264

Re: numeric works, non-numeric fails in my where statement

Posted in reply to KurtBremser

This might be because ANSI SQL doesn't like double quotes for string literals. Try

FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))
You don't want or need macro quoting here. The values are already in regular quotes!. 
 
So just use normal %SYSFUNC() instead of %QSYSFUNC().
 

View solution in original post


All Replies
Super User
Super User
Posts: 9,812

Re: numeric works, non-numeric fails in my where statement

[Big Wall of Text] - Sorry, can't make head not tail of it.  Please reformat your post, use returns, paragraphs and such like so text is readable.  For code, use the code blocks - it is the {i} above the post area - this will highlight code and retain source formatting so that it is readable.

I suspect on your second query, you would need to put the macro variables in quotes otherwise they appear as numbers:

where FIELD6="&CheckUp1." AND FIELD7="&CheckUp2."

Occasional Contributor
Posts: 5

Re: numeric works, non-numeric fails in my where statement

Sorry - first post Smiley Sad

 

Will try to reformat code to make it understandable - thanks

Occasional Contributor
Posts: 5

Re: numeric works, non-numeric fails in my where statement

proc sql noprint; 
select Centre, Branch, Product, Contract 
into :Centre, :Branch, :Product, :Contract 
from DD1; 
%put &Centre &Branch &Product &Contract; 

PROC SQL; 
connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD2 AS SELECT * FROM CONNECTION TO odbc 
( 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, 
FIELD1 AS Key 
FROM EXAMPLETABLE 
where FIELD1=&Centre AND 
FIELD2=&Branch AND 
FIELD3=&Product AND
 FIELD4=&Contract 
for fetch only with ur; 
); 

This returns the data I was expecting - but when I run a similar piece of code it fails and I think it's because the variable I'm querying in the WHERE statement is non-numeric. Do I need to do something different? 

proc sql noprint; 
select CheckUp1, CheckUp2 
into :CheckUp1, :CheckUp2 from DD2; 
%put &CheckUp1 &CheckUp2; 
PROC SQL; 
connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD3 AS SELECT * FROM CONNECTION TO odbc 
( 
SELECT FIELD6, FIELD7, FIELD8, FIELD9, 
FIELD6 AS Key 
FROM EXAMPLETABLE2 
where 
FIELD6=&CheckUp1 AND 
FIELD7=&CheckUp2 
for fetch only with ur; 
);
Super User
Posts: 10,530

Re: numeric works, non-numeric fails in my where statement

With character variables, use quotes as suggested by @RW9

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: numeric works, non-numeric fails in my where statement

The second bit returns the following error

 

ERROR: CLI describe error: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]EH3920197742 NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.

 

If I put FIELD6="&CheckUp1" AND FIELD7="&CheckUp2" it doesn't return anything

 

But if I put FIELD6='EH3920197742' then it works - so I know the data is there, it just can't find it when I use the variable name 

Super User
Super User
Posts: 9,812

Re: numeric works, non-numeric fails in my where statement

And what does &Checkup1 and 2 look like?  Look in your log, turn macro printing functions on with:

options mlogic mprint symbolgen;

 

Does &Checkup1 = EH3920197742?  I would guess it doesn't.

Super User
Posts: 10,530

Re: numeric works, non-numeric fails in my where statement


stogez13 wrote:

The second bit returns the following error

 

ERROR: CLI describe error: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]EH3920197742 NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.

 

If I put FIELD6="&CheckUp1" AND FIELD7="&CheckUp2" it doesn't return anything

 

But if I put FIELD6='EH3920197742' then it works - so I know the data is there, it just can't find it when I use the variable name 


This might be because ANSI SQL doesn't like double quotes for string literals. Try

FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎02-28-2018 09:10 AM
Super User
Super User
Posts: 8,264

Re: numeric works, non-numeric fails in my where statement

Posted in reply to KurtBremser

This might be because ANSI SQL doesn't like double quotes for string literals. Try

FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))
You don't want or need macro quoting here. The values are already in regular quotes!. 
 
So just use normal %SYSFUNC() instead of %QSYSFUNC().
 
Super User
Posts: 10,530

Re: numeric works, non-numeric fails in my where statement

Have to say, I just copied it from a solution that google found for me Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: numeric works, non-numeric fails in my where statement

Thank you so much - this worked like a dream!

Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 195 views
  • 0 likes
  • 4 in conversation