Date Variable

648980

Hi,
I want to filter the source data with 'trunc(sysdate)'.
When i assign a variable as 'select sysdate from dual' and use it in interface bb selecting it from project_variables, It assigns to varibale something like that:

variable : 2008-07-22 11:29:51.0

code:
select * from SH.AD AD where ((AD.AD=2008-07-22 11:29:51.0)) and (1=2)

But when we use a numeric or else alphanumeric variable,it works fine.

Tnx in advance

641360

plz give some more details of u r problem.


jai

648980

Sure Jai,

Variable name=test
In the interface filter=> trunc(CITIES.CREATION_DATE)=trunc(#test)
In the refreshing variable tab=>select sysdate from dual
In the definition variable tab=> type:date, Action:Not Persistent

When i refresh variable, it works fine.
However when i use it in package or interface,it prompts error as:
"Bad query:missing parameter"

My comment:
As far as i see,the variable is not used as date in stead it is like used as char or number something.

659202

Yes, you right.

I've got the same error as your. So I've changed my variable to be an alphanumeric, and it worked fine.

But I am still in troubles, my variable is not global, it's a project variable, but when I execute the interface the generated query filter looks like this:

where (1=1)
And (TO_DATE(SUBSTR(ATRANSACCION.CAB_MOMENTO,1,8), 'YYYYMMDD') = TO_DATE(#CIBEDW.VAR_DATA_CARGA_INI, 'yyyymmdd'))

Additional info: #CIBEDW is my current project.
VAR_DATA_CARGA is my variable, and it's perfoming a query (select date from my_table where my_process = 'XXX' - should return 20081001), I already refreshed it several times.
This query above has been performing in a different data base not the same as my source.

But even with a refresh my interface query is not correctly, because it's returning no rows. But I am pretty sure the 20081001 exists on my source table.

Any help?

566902

Try putting quotes around the variable:
bq. where (1=1) \\ And (TO_DATE(SUBSTR(ATRANSACCION.CAB_MOMENTO,1,8), 'YYYYMMDD') = TO_DATE('#CIBEDW.VAR_DATA_CARGA_INI', 'yyyymmdd'))
Craig
bq.