Page 1 of 1
WHERE DATE in (StringParameter) in DB2 Stage
Posted: Wed Nov 26, 2008 1:59 am
by 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
Posted: Wed Nov 26, 2008 3:19 am
by bikan
try by enclosing date into single quote.......& using or function
Posted: Wed Nov 26, 2008 3:29 am
by gauravrb
bikan wrote: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.
Posted: Wed Nov 26, 2008 3:43 am
by 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
Posted: Wed Nov 26, 2008 3:58 am
by gauravrb
bikan wrote: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.
Posted: Wed Nov 26, 2008 4:05 am
by 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
Posted: Wed Nov 26, 2008 4:15 am
by gauravrb
Scope wrote: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
Posted: Wed Nov 26, 2008 4:23 am
by Scope
i think your parameter 'BD' datatype is date.change the parameter type to string.
Posted: Wed Nov 26, 2008 4:27 am
by gauravrb
Scope wrote:i think your parameter 'BD' datatype is date.change the parameter type to string.
No.
The parameter data type is String only.
Posted: Wed Nov 26, 2008 4:50 am
by ray.wurlod
Your job parameter needs to have the intermediate quote characters.
Code: Select all
'2008-11-26','2008-11-28','2006-01-01'
Posted: Wed Nov 26, 2008 5:05 am
by gauravrb
ray.wurlod wrote: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
Posted: Wed Nov 26, 2008 1:33 pm
by 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.
Posted: Mon Dec 01, 2008 7:20 am
by 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