1 Reply Latest reply on Jul 12, 2020 7:33 PM by Frank Kulash

    Error in SQL Query

    upen

      Hi  Experts

       

      Request you to help me to solve the error in the below query.

       

      SELECT NVL (TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

                  -(TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

      FROM H_OTMO

       

      Regards,

      Upendran

        • 1. Re: Error in SQL Query
          John Thorton

          upen wrote:

           

          Hi Experts

           

          Request you to help me to solve the error in the below query.

           

          SELECT NVL (TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

          -(TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

          FROM H_OTMO

           

          Regards,

          Upendran

          ERROR? What Error? I see no error.

          What datatype is OUTTIME & INTIME?

           

          Please click on URL below & provide details as stated in #5 - #9 inclusive

           

          How do I ask a question on the forums?


          • 2. Re: Error in SQL Query
            Frank Kulash

            Hi, Upendran,

             

            Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

            Explain, using specific examples, how you get those results from that data.

            If your question involves an error, post the complete error message you're getting.

            Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

            See the forum FAQ: Re: 2. How do I ask a question on the forums?

            upen wrote:

             

            Hi Experts

             

            Request you to help me to solve the error in the below query.

             

            SELECT NVL (TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

            -(TO_DATE(OUTTIME,'YYYY/MM/DD HH24:MI')-TO_TIMESTAMP(INTME,'YYYY/MM/DD HH24:MI'))

            FROM H_OTMO

             

            Regards,

            Upendran

            Always format your code so it's easy to see what the arguments to functions are, and how functions are nested.  If you format the code above, it might look like this:

            SELECT  NVL ( TO_DATE (OUTTIME, 'YYYY/MM/DD HH24:MI')

                        , TO_TIMESTAMP (INTME, 'YYYY/MM/DD HH24:MI')

                )

                 - ( TO_DATE (OUTTIME, 'YYYY/MM/DD HH24:MI')

                   - TO_TIMESTAMP (INTME, 'YYYY/MM/DD HH24:MI')

                   )

            FROM   H_OTMO

            ;

            Now it's much easier to see that yu're calling NVL with only one argument, but NVL always requires exactly two arguments.

            How to fix that problem depends on your data and your requirements, and possible on your version.

             

            By the way, you should decide if you want to convert intme and outtime as DATEs or TIMESTAMPs, and convert both the same way.  Since you're only interested in accuracy to the minute, you should probably use DATEs.