numeric works, non-numeric fails in my where statement

[stogez13]

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, :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; ); Any help much appreciated - thanks

[RW9]

[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."

[stogez13]

Sorry - first post 😞

Will try to reformat code to make it understandable - thanks

[stogez13]

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; 
);

[Kurt_Bremser]

With character variables, use quotes as suggested by @RW9

[stogez13]

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 

[RW9]

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.

[Kurt_Bremser]

---

@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'"))

[Tom · Accepted solution]

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

FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))

[Kurt_Bremser]

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

[stogez13]

Thank you so much - this worked like a dream!

🙂