1 2 Previous Next 16 Replies Latest reply on Oct 6, 2017 5:31 PM by L. Fernigrini

    Oracle sql input bind data Type Value

    Venkat Thota - BIP

      Hi ,

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      Toad for oracle :12:8:0:49.

       

      I am trying to filter data for fiscal year  july 01 through june 30 , I am using bind variable to choose input value (please check sql query posted below)

      if input bind value current month(9/10/2017) is greater or equal to 7 then  fiscal start date will be 7/01/2017 and end date will be 8/30/2018 ,

      if input bind value current month(6/20/2017) is less than  or equal to 7 then  fiscal start date will be 7/01/2016 and end date will be 8/30/2017 ,

       

      I can able to achieve this using below sql script , but problem is with bind variable and if i choose input bind value  as varchar2 then data will generated , if choose date data type as input value then throwing error ,

       

      I want to pass input bind value as date data type ,

      Any suggestion please?

       

      SELECT case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy')

                  when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))-1),'mm/dd/yyyy') end fis_start,

             case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))+1),'mm/dd/yyyy')

                  when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy') end fis_end,

             to_date(:dt,'mm/dd/yyyy') mnthly

             FROM fac

       

      Thanks

        • 1. Re: Oracle sql input bind data Type Value
          L. Fernigrini

          Try this:

          WITH vDate AS (
               SELECT TO_DATE('20170521','YYYYMMDD')  AS TheDate FROM DUAL
               UNION ALL
               SELECT TO_DATE('20170810','YYYYMMDD')  AS TheDate FROM DUAL
               )
          SELECT TheDate, 
                 CASE WHEN EXTRACT(MONTH FROM TheDate) < 7
                     THEN ADD_MONTHS(TRUNC(TheDate,'YEAR'),-6)
                     ELSE ADD_MONTHS(TRUNC(TheDate,'YEAR'),6)
                 END AS BEginDate,
                 (CASE WHEN EXTRACT(MONTH FROM TheDate) < 7
                     THEN ADD_MONTHS(TRUNC(TheDate,'YEAR'),6)
                     ELSE ADD_MONTHS(TRUNC(TheDate,'YEAR'),18)
                 END) - 1 AS EndDate
          FROM vDate
          

           

          I believe you have a typo, end day for fiscal years is June 30, not August 30.. Just use the DATE bind variable insteaf of "TheDate" column.

           

          [EDIT] This is the output:

           

          1 person found this helpful
          • 2. Re: Oracle sql input bind data Type Value
            Frank Kulash

            Hi,

            2807661 wrote:

             

            Hi ,

            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

            Toad for oracle :12:8:0:49.

             

            I am trying to filter data for fiscal year july 01 through june 30 , I am using bind variable to choose input value (please check sql query posted below)

            if input bind value current month(9/10/2017) is greater or equal to 7 then fiscal start date will be 7/01/2017 and end date will be 8/30/2018 ,

            Did you mean the end date will be 6/30/2018?

            if input bind value current month(6/20/2017) is less than or equal to 7 then fiscal start date will be 7/01/2016 and end date will be 8/30/2017 ,

             

            I can able to achieve this using below sql script , but problem is with bind variable and if i choose input bind value as varchar2 then data will generated , if choose date data type as input value then throwing error ,

             

            I want to pass input bind value as date data type ,

            Any suggestion please?

             

            SELECT case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy')

            when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))-1),'mm/dd/yyyy') end fis_start,

            case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))+1),'mm/dd/yyyy')

            when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy') end fis_end,

            to_date(:dt,'mm/dd/yyyy') mnthly

            FROM fac

             

            Thanks

            Unfortunately, bind variables (like you set with the SQL*Plus VARIABLE command), can't be DATEs.  The best you can do using that kind of bind variable is to use a VARCHAR2.  You can convert that string to a DATE (using TO_DATE) in a WITH clause, so you only need to call TO_DATE once.

             

            Here's how I would derive the fiscal year from a SQL*Plus bind varibale:

            VARIABLE  dt  VARCHAR2 (10)

            EXEC     :dt  := '6/20/2017';

             

            WITH    got_fis_start   AS

            (

                SELECT  ADD_MONTHS ( TRUNC ( ADD_MONTHS ( TO_DATE ( :dt, 'MM/DD/YYYY')

                                                        , -6

                                                        )

                                           , 'YEAR'

                                           )

                                   , 6

                                   )  AS fis_start

                FROM    dual

            )

            SELECT  :dt                             AS dt

            ,       fis_start

            ,       ADD_MONTHS (fis_start, 12) - 1  AS fis_end

            FROM    got_fis_start

            ;

            TRUNC (dt, 'year') returns the start of the calendar year containing dt.  The "magic number" 6 in the calls to ADD_MONTHS reflects the fact that your fiscal year starts 6 months earlier.

             

            Output:

            DT                               FIS_START   FIS_END

            -------------------------------- ----------- -----------

            6/20/2017                        01-Jul-2016 30-Jun-2017

            1 person found this helpful
            • 3. Re: Oracle sql input bind data Type Value
              Frank Kulash

              Hi,

               

              If you don't like all the nested function calls in reply #2 (and I don't blame you), here's a slightly different way to get the beginning and end dates of the fiscal year:

               

              WITH    got_dt    AS

              (

                  SELECT  TO_DATE ( :dt, 'MM/DD/YYYY')  AS dt

                  FROM    dual

              )

              ,    got_fis_start   AS

              (

                  SELECT  dt

                  ,       ADD_MONTHS ( TRUNC (dt, 'YEAR')

                                     , CASE

                                           WHEN  EXTRACT (MONTH FROM dt) <= 6

                                           THEN  -6

                                           ELSE  +6

                                       END

                                     )  AS fis_start

                  FROM    got_dt

              )

              SELECT  dt  

              ,       fis_start

              ,       ADD_MONTHS (fis_start, 12) - 1  AS fis_end

              FROM    got_fis_start

              ;

              • 4. Re: Oracle sql input bind data Type Value
                Venkat Thota - BIP

                Thanks Frank Kulash ,

                 

                 

                your query is simpler then mine  , and it was working as expected ,

                 

                 

                I am using Oracle Bi Publisher reporting tool , here i have to create date parameter in bi user interface for this bind variable defined in query from where user provide select date input from calendar screen to filter report data, but in query we have defined bind to accept varchar2 and if we pass date from bi screen and query will be failed.

                 

                 

                so finally ,it is not possbile to accept direct date values as bind for this requirement ?

                • 5. Re: Oracle sql input bind data Type Value
                  Frank Kulash

                  Hi,

                  2807661 wrote:

                   

                  Thanks Frank Kulash ,

                   

                   

                  your query is simpler then mine , and it was working as expected ,

                   

                   

                  I am using Oracle Bi Publisher reporting tool , here i have to create date parameter in bi user interface for this bind variable defined in query from where user provide select date input from calendar screen to filter report data, but in query we have defined bind to accept varchar2 and if we pass date from bi screen and query will be failed.

                   

                   

                  so finally ,it is not possbile to accept direct date values as bind for this requirement ?

                  Sorry, I don't know anything about BI, and that looks like a BI question.  Given that SQL*Plus doesn't allow the DATE datatype for bind variables, it wouldn't surprise me if BI didn't, either.

                  On the other hand, if BI has a calendar widget that returns a DATE, then they must have some way to use that DATE elsewhere.  It may bot be a bind variable.

                  Again, I'm just guessing.  You should ask someone who knows, and those people frequent the BI Forum.

                   

                  I suggest you close this thread, and open a new thread in the BI Forum: Business Intelligence

                  [EDIT: Rather, the BI Publisher Forum, as Christian suggested below.]

                  • 6. Re: Oracle sql input bind data Type Value
                    Christian Berg

                    BI Publisher would be best :-)

                     

                    BI Publisher

                    • 7. Re: Oracle sql input bind data Type Value
                      AndrewSayer

                      2807661 wrote:

                       

                      Hi ,

                      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

                      Toad for oracle :12:8:0:49.

                       

                      I am trying to filter data for fiscal year july 01 through june 30 , I am using bind variable to choose input value (please check sql query posted below)

                      if input bind value current month(9/10/2017) is greater or equal to 7 then fiscal start date will be 7/01/2017 and end date will be 8/30/2018 ,

                      if input bind value current month(6/20/2017) is less than or equal to 7 then fiscal start date will be 7/01/2016 and end date will be 8/30/2017 ,

                       

                      I can able to achieve this using below sql script , but problem is with bind variable and if i choose input bind value as varchar2 then data will generated , if choose date data type as input value then throwing error ,

                       

                      I want to pass input bind value as date data type ,

                      Any suggestion please?

                       

                      SELECT case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy')

                      when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))-1),'mm/dd/yyyy') end fis_start,

                      case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))+1),'mm/dd/yyyy')

                      when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy') end fis_end,

                      to_date(:dt,'mm/dd/yyyy') mnthly

                      FROM fac

                       

                      Thanks

                      Maybe I'm missing something, but would it not be easier to have the user choose between 'this' and 'last' fiscal year with a string input?

                       

                      Like Frank, I've never used BI so it's hard to give great advise.

                      • 8. Re: Oracle sql input bind data Type Value
                        L. Fernigrini

                        As Frank Kulash has mentioned, you should review the BI Publisher documentation or ask for some help in the BI forum.

                         

                        In your original post you mentioned TOAD, and I assumed that the question was related to TOAD.

                         

                        I used TOAD many years, now I mostly use PL/SQL Developer, where date bind variables are valid:

                         

                         

                        PD: As usual, his solutions are really elegant :-)

                        • 9. Re: Oracle sql input bind data Type Value
                          Venkat Thota - BIP

                          Hello Andrew ,

                          I can able to create string param from bi side and can pass values to sql query ,but  Users want to provide input from calendar widget ,calendar widget opens  only for date parameter in BI , Thanks

                          • 10. Re: Oracle sql input bind data Type Value
                            L. Fernigrini

                            If you have the data as DATE, you can get rid of the first step (string to date conversion) in Frank Kulash solution, you can directly work on "got_fis_start" using the variable and DUAL table as source.

                            • 11. Re: Oracle sql input bind data Type Value
                              Venkat Thota - BIP

                              I used TOAD many years, now I mostly use PL/SQL Developer, where date bind variables are valid:

                              can you able to pass date input for bind varible :dt in TOAD for below query?

                               

                              when i execute below query in toad it pop up variable widget where i need to choose datatype and provide input , if i select varchar2 then only query

                              executed successfully , if datatype is date then query is failed.

                               

                               

                               

                               

                              WITH    got_dt    AS

                              (

                                  SELECT  TO_DATE ( :dt, 'MM/DD/YYYY')  AS dt

                                  FROM    dual

                              )

                              ,    got_fis_start   AS

                              (

                                  SELECT  dt

                                  ,       ADD_MONTHS ( TRUNC (dt, 'YEAR')

                                                     , CASE

                                                           WHEN  EXTRACT (MONTH FROM dt) <= 6

                                                           THEN  -6

                                                           ELSE  +6

                                                       END

                                                     )  AS fis_start

                                  FROM    got_dt

                              )

                              SELECT  dt 

                              ,       fis_start

                              ,       ADD_MONTHS (fis_start, 12) - 1  AS fis_end

                              FROM    got_fis_start

                              ;

                              • 12. Re: Oracle sql input bind data Type Value
                                Venkat Thota - BIP

                                Based on the input i have to filter the fiscal year start and end dates and i should be dynamic.

                                • 13. Re: Oracle sql input bind data Type Value
                                  Frank Kulash

                                  Hi,

                                  2807661 wrote:

                                   

                                  I used TOAD many years, now I mostly use PL/SQL Developer, where date bind variables are valid:

                                  can you able to pass date input for bind varible :dt in TOAD for below query?

                                   

                                  when i execute below query in toad it pop up variable widget where i need to choose datatype and provide input , if i select varchar2 then only query

                                  executed successfully , if datatype is date then query is failed.

                                   

                                   

                                   

                                   

                                  WITH got_dt AS

                                  (

                                  SELECT TO_DATE ( :dt, 'MM/DD/YYYY') AS dt

                                  FROM dual

                                  )

                                  , got_fis_start AS

                                  (

                                  SELECT dt

                                  , ADD_MONTHS ( TRUNC (dt, 'YEAR')

                                  , CASE

                                  WHEN EXTRACT (MONTH FROM dt) <= 6

                                  THEN -6

                                  ELSE +6

                                  END

                                  ) AS fis_start

                                  FROM got_dt

                                  )

                                  SELECT dt

                                  , fis_start

                                  , ADD_MONTHS (fis_start, 12) - 1 AS fis_end

                                  FROM got_fis_start

                                  ;

                                  So you're able to pass a DATE there, where I added emphasis?

                                   

                                  You don't want to pass a DATE to TO_DATE.  Try this instead:

                                  WITH    got_dt    AS

                                  (

                                      SELECT  :dt  AS dt

                                      FROM    dual

                                  )

                                  ,    got_fis_start   AS

                                  (

                                      SELECT  dt

                                      ,       ADD_MONTHS ( TRUNC (dt, 'YEAR')

                                                         , CASE

                                                               WHEN  EXTRACT (MONTH FROM dt) < 7

                                                               THEN  -6

                                                               ELSE  +6

                                                           END

                                                         )  AS fis_start

                                      FROM    got_dt

                                  )

                                  SELECT  dt   

                                  ,       fis_start

                                  ,       ADD_MONTHS (fis_start, 12) - 1  AS fis_end

                                  FROM    got_fis_start

                                  ;

                                   

                                  or maybe this:

                                  WITH    got_fis_start   AS

                                  (

                                      SELECT  dt

                                      ,       ADD_MONTHS ( TRUNC (:dt, 'YEAR')

                                                         , CASE

                                                               WHEN  EXTRACT (MONTH FROM :dt) < 7

                                                               THEN  -6

                                                               ELSE  +6

                                                           END

                                                         )  AS fis_start

                                      FROM    got_dt

                                  )

                                  SELECT  dt   

                                  ,       fis_start

                                  ,       ADD_MONTHS (fis_start, 12) - 1  AS fis_end

                                  FROM    got_fis_start

                                  ;

                                  • 14. Re: Oracle sql input bind data Type Value
                                    Frank Kulash

                                    Hi,

                                     

                                    Seriously, you should be asking in the BI Publisher Forum, not the SQL and PL/SQL Forum.  SQL and PL/SQL don't have  calendar widgets, so this is the wrong forum to be asking about how to use them.

                                    1 2 Previous Next