Topic
  • 10 replies
  • Latest Post - ‏2009-12-09T01:24:43Z by SystemAdmin
SystemAdmin
SystemAdmin
801 Posts

Pinned topic Input SQL variable to variable

‏2009-11-20T07:35:44Z |
Hi:
I want to use this sql to produce a table.But this sql has some variable.And among them the need to calculate .And I input the variable":dr".So how to achieveit?
Thank you.
Updated on 2009-12-09T01:24:43Z at 2009-12-09T01:24:43Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-11-24T05:53:04Z  
    Hi,
    do you mean the variables with ":" prefix are those you want to input at runtime? If so, you could use SQL call builder and replace these variables with question mark - "?", the builder will help generate parameters for all the "?" in your SQL statement.
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-11-24T06:15:53Z  
    Hi,
    do you mean the variables with ":" prefix are those you want to input at runtime? If so, you could use SQL call builder and replace these variables with question mark - "?", the builder will help generate parameters for all the "?" in your SQL statement.
    Hi:
    I have tried as you said.But it's also wrong.Can the "?" in the to_date function?
  • DGawron
    DGawron
    34 Posts

    Re: Input SQL variable to variable

    ‏2009-12-04T22:09:43Z  
    Hi:
    I have tried as you said.But it's also wrong.Can the "?" in the to_date function?
    You can use positional parameters in the to_date function. Maybe you need to give the parameter a value formatted as "yyyy-mm-dd".
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-12-05T05:17:53Z  
    • DGawron
    • ‏2009-12-04T22:09:43Z
    You can use positional parameters in the to_date function. Maybe you need to give the parameter a value formatted as "yyyy-mm-dd".
    Hi:
    I have tried as you said,but it's also wrong.
  • DGawron
    DGawron
    34 Posts

    Re: Input SQL variable to variable

    ‏2009-12-07T16:36:59Z  
    Hi:
    I have tried as you said,but it's also wrong.
    You need to provie more detail. Is there an error message or a stack trace you can post? Have you tried enabling SQL statement logging and warnings logging in SQL Call?
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-12-08T00:22:12Z  
    • DGawron
    • ‏2009-12-07T16:36:59Z
    You need to provie more detail. Is there an error message or a stack trace you can post? Have you tried enabling SQL statement logging and warnings logging in SQL Call?
    Hi:
    Sorry,but I don't know where is the error message or a stack trace you want.And how to let it enabling SQL statement logging and warnings logging in SQL Call?
    Thank you.
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-12-08T00:27:54Z  
    • DGawron
    • ‏2009-12-07T16:36:59Z
    You need to provie more detail. Is there an error message or a stack trace you can post? Have you tried enabling SQL statement logging and warnings logging in SQL Call?
    Hi:
    This is my model.And the "?" is date data.Please have a look,what's wrong with it.

    Attachments

  • DGawron
    DGawron
    34 Posts

    Re: Input SQL variable to variable

    ‏2009-12-08T16:52:54Z  
    Hi:
    This is my model.And the "?" is date data.Please have a look,what's wrong with it.
    Looks OK, but it's hard to tell what is wrong without knowing more about your environment, data. etc. It's possible that the value of ${Variables/H1_SelectedRowData/Row/USEDAY} applied to the positional parameter is not formatted correctly. In SQL Call open the "Events, Statistics, and Logging" group and enable logging of SQL statements and warnings. The rerun your test and look in web-inf/logs of your deployed WAR. One of more of the files in that dir will contain a dump of the SQL executed and the value used for the positional parameter. Confirm that this value agrees with what your DB epects for a date format.
  • DGawron
    DGawron
    34 Posts

    Re: Input SQL variable to variable

    ‏2009-12-08T17:14:05Z  
    Hi:
    This is my model.And the "?" is date data.Please have a look,what's wrong with it.
    Try this updated version of your model. I enabled the logging inputs and also noticed that the builder didn't have a cached schema. That's because SQL Call has a problem with automatically generating a schema from a SELECT that hs a DATE term in the WHERE clause. The simple work-around is to use an alternate SQL statement as shown.

    Attachments

  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Input SQL variable to variable

    ‏2009-12-09T01:24:43Z  
    • DGawron
    • ‏2009-12-08T17:14:05Z
    Try this updated version of your model. I enabled the logging inputs and also noticed that the builder didn't have a cached schema. That's because SQL Call has a problem with automatically generating a schema from a SELECT that hs a DATE term in the WHERE clause. The simple work-around is to use an alternate SQL statement as shown.
    Thank you for you help.The model was OK. Thank you .