WHERE DATE in (StringParameter) in DB2 Stage

[gauravrb]

The SQL query required in DB2 Stage is

Code: Select all

select 
          BUSINESS_DATE 

from saggbp2.etl_business_time 

where BUSINESS_DATE in (#BD#)

#BD# - Is parameter containing comma separated values of date of type string.
Eg:- (2008-11-26,2008-11-28,2006-01-01).

But it did not work in DB2 stage. The Job aborted.

I tried coverting the source Date in to string by using VARCHAR() function and using the following query

Code: Select all

select 
          BUSINESS_DATE 

from saggbp2.etl_business_time 

where VARCHAR(BUSINESS_DATE) in ('#BD#')

The job ran but did not fetch any records inspite of having the date in the list of values. I guess it is taking the enitire parameter as one string.
Is there any way around to make this work in DB2 stage itself?

Thanks

[bikan]

try by enclosing date into single quote.......& using or function

[gauravrb]

try by enclosing date into single quote.......& using or function

Thanks.

I tried enclosing it in single quotes as well. But the stage i guess considers the entire parameter as string and does not fect any records.

I am not sure how can we use or function here because i will not be knowing the how many values i will be receiving via parameter.

[bikan]

As per me tour query should look like

select
BUSINESS_DATE

from saggbp2.etl_business_time

where BUSINESS_DATE in ('2008-11-26','2008-11-28','2006-01-01')..b

[gauravrb]

As per me tour query should look like

select
BUSINESS_DATE

from saggbp2.etl_business_time

where BUSINESS_DATE in ('2008-11-26','2008-11-28','2006-01-01')..b

Thanks.

It works when we harcode the values.
But the problem is when we use it as parameter.

[Scope]

try this

Code: Select all

select 
          BUSINESS_DATE 

from saggbp2.etl_business_time 

where BUSINESS_DATE in ('#BD#') 

pass the value 2008-11-26','2008-11-28','2006-01-01 in the parameter

[gauravrb]

try this

Code: Select all

select 
          BUSINESS_DATE 

from saggbp2.etl_business_time 

where BUSINESS_DATE in ('#BD#') 

pass the value 2008-11-26','2008-11-28','2006-01-01 in the parameter

Tried it. Did not work. Got the following error.

DB2_UDB_Enterprise_0,0: Error Idx = 9;
DB2Driver Embedded SQL message: SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007
;
sqlcode = -180;
sqlstate = 22007

[Scope]

i think your parameter 'BD' datatype is date.change the parameter type to string.

[gauravrb]

i think your parameter 'BD' datatype is date.change the parameter type to string.

No.
The parameter data type is String only.

[ray.wurlod]

Your job parameter needs to have the intermediate quote characters.

Code: Select all

'2008-11-26','2008-11-28','2006-01-01'

[gauravrb]

Your job parameter needs to have the intermediate quote characters.

Code: Select all

'2008-11-26','2008-11-28','2006-01-01'

...

Hi Ray,

Using the code

Code: Select all

select 
          BUSINESS_DATE 

from saggbp2.etl_business_time 

where BUSINESS_DATE in (#BD#) 

Where

BUSINESS_DATE - Of Type Date
#BD# - Of Type String

I pass The value for the Parameter #BD# having intermediate quote characters.

Code: Select all

'2008-11-26','2008-11-28','2006-01-01'

The job aborts giving the followinng Error

Code: Select all

DB2_UDB_Enterprise_0: Error Idx = 5;
DB2Driver Embedded SQL message: SQL0206N  "€8" is not valid in the context where it is used.  SQLSTATE=42703
;
sqlcode = -206;
sqlstate = 42703

[ray.wurlod]

Are you SURE that that message is coming from the date column? The mention of "€8" suggests that something else may be involved.

It may also be that null is not being handled gracefully.

[thumati.praveen]

Hi,

when we right clik on the stage and view the data it is allowing.But when I ran the job I got following error.

error code is

DB2_UDB_Enterprise_1: Error Idx = 5;
DB2Driver Embedded SQL message: SQL0401N The data types of the operands for the operation "IN" are not
compatible. SQLSTATE=42818
;
sqlcode = -401;
sqlstate = 42818

Thanks,
Praveen