Forum Stats

  • 3,837,484 Users
  • 2,262,264 Discussions
  • 7,900,298 Comments

Discussions

Date Variable

648980
648980 Member Posts: 27
edited Dec 8, 2008 1:08PM in Data Integrator
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
Tagged:

Comments

  • 641360
    641360 Member Posts: 78
    plz give some more details of u r problem.


    jai
  • 648980
    648980 Member Posts: 27
    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
    659202 Member Posts: 10
    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
    566902 Member Posts: 809
    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.
This discussion has been closed.