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

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
    ACCEPTED ANSWER

    Re: Input SQL variable to variable

    ‏2009-11-24T05:53:04Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Input SQL variable to variable

      ‏2009-11-24T06:15:53Z  in response to SystemAdmin
      Hi:
      I have tried as you said.But it's also wrong.Can the "?" in the to_date function?
      • DGawron
        DGawron
        34 Posts
        ACCEPTED ANSWER

        Re: Input SQL variable to variable

        ‏2009-12-04T22:09:43Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Input SQL variable to variable

          ‏2009-12-05T05:17:53Z  in response to DGawron
          Hi:
          I have tried as you said,but it's also wrong.
          • DGawron
            DGawron
            34 Posts
            ACCEPTED ANSWER

            Re: Input SQL variable to variable

            ‏2009-12-07T16:36:59Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Input SQL variable to variable

              ‏2009-12-08T00:22:12Z  in response to DGawron
              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
              ACCEPTED ANSWER

              Re: Input SQL variable to variable

              ‏2009-12-08T00:27:54Z  in response to DGawron
              Hi:
              This is my model.And the "?" is date data.Please have a look,what's wrong with it.

              Attachments

              • DGawron
                DGawron
                34 Posts
                ACCEPTED ANSWER

                Re: Input SQL variable to variable

                ‏2009-12-08T16:52:54Z  in response to SystemAdmin
                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
                ACCEPTED ANSWER

                Re: Input SQL variable to variable

                ‏2009-12-08T17:14:05Z  in response to SystemAdmin
                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
                  ACCEPTED ANSWER

                  Re: Input SQL variable to variable

                  ‏2009-12-09T01:24:43Z  in response to DGawron
                  Thank you for you help.The model was OK. Thank you .