I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"
Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?
select *
from connection to &glb_db2.
(
SELECT DISTINCT
D1IPAR,
MTD
FROM U.TBFQ81
WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE.
)
;
DB/2 uses a different format for date literals. Ask your DB/2 people how you need to provide the date in the pass-through. Or do a google search for "db2 date literal".
@Babloo wrote:
Sure. Just to inform you that I could see the DB2 table (might be
registered) in SAS DI Studio and it has the value 31MAR2018 (which I'm
looking for) and it has date9. format.
That's because SAS uses its own display formats in its world. But in an explicit pass-through, you have to adhere to the rules of the DBMS.
@Babloo wrote:
I was asked to use explicit pass-through.
Then you have to use DB/2 syntax, period.
BTW as a SAS developer, it is often your job to make it clear to persons who have no clue (at least about SAS) which solution is better. Just that somebody says "do it this way" does in no way implicate they know what they're talking about. In fact, people trying to force you to do something in their preferred way is usually a sign of galloping stupidity on their side.
@Babloo wrote:
I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"
Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?
select * from connection to &glb_db2. ( SELECT DISTINCT D1IPAR, MTD FROM U.TBFQ81 WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE. ) ;
Like in SAS you need to tell the DB2 that the string you're passing in needs to get converted to a datetime value. Just Googling in quickly it appears that DB2 has a TO_DATE() function for this.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.