1 2 Previous Next 28 Replies Latest reply on Aug 11, 2020 9:22 AM by Frank Kulash

    date not incrementing

    Robeen

      Oracle Database 12c

       

       

      Hello Team,

       

      can you please help with SQL below? I see 'unique_date' it is incrementing and decrementing after

       

      select * FROM (select

        distinct(a.apt_order_id) as orderid,

        a.apt_date as aptdate,

        /*coalesce(a.apt_mdate, a.apt_cdate) as unique_date,*/

        coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS')) as unique_date,

        b.cws_status as status,

        (

          select

            status_description

          from

            L2TCP.gci_clm_status_mapping

          where

            status = b.cws_status

            and rownum = '1'

        ) as statusdesc,

        d.exa_area as area

      from

        L2TCP.gci_appointment a,

        L2TCP.gci_contractor_work_status b,

        L2TCP.gci_clmorder c,

        L2TCP.gci_ref_exchange_area d

      where

        a.apt_order_id = B.CWS_ORDER_ID

        and B.CWS_STATUS in (

          select

            status

          from

            L2TCP.gci_clm_status_mapping

          where

            status not in ('WWIP', 'WCOM')

        )

        and trunc(b.cws_cdate) = a.apt_date

        and b.cws_cdate =(

          select

            max(cws_cdate)

          from

            L2TCP.gci_contractor_work_status

          where

            cws_order_id = a.apt_order_id

        )

        and a.apt_order_id = c.clm_order_id

        and substr(c.clm_mdf, 1, 2) = d.exa_exchange

      order by cws_cdate ASC

        )

      WHERE unique_date > TO_TIMESTAMP('24-Jul-2020', 'DD-MON-YYYY HH12:MI:SS')                                             

        ORDER BY unique_date ASC;

       

       

       

      Thanks,

       

      Joe

        • 1. Re: date not incrementing
          mathguy

          You lost me at   rownum = '1'

           

          Good luck!

          • 2. Re: date not incrementing
            Robeen

            If I remove it I get

            ORA-01427: single-row subquery returns more than one row

            Goal: to ensure unique date in ascending

            • 3. Re: date not incrementing
              mathguy

              I had no issue with seeing a condition on rownum. I use them too, sometimes.

               

              I will let you figure out what problem I had with the particular condition you wrote. Honestly, after I saw that I stopped reading - I'm not joking.

               

              Good luck!

              • 4. Re: date not incrementing
                Robeen

                field unique_date is incrementing and decrementing.

                • 5. Re: date not incrementing
                  BEDE

                  So, when you put conditions like (... ) and rownum=1 you actually select the first row that meets the other conditions. Hard to say which will be the first. If you wish to have rows taken in a a given order by a set of columns, use row_number() over (partition by ... order by ...) RTM on that.

                  So, to get the first row in a given order, use:

                  with tb_ord as (

                  select  row_number() over (partition by ... order by ...)  rn, ... --- other columns you need

                  from...

                  join...

                  where ...

                  )

                  select *

                  from tb_ord

                  where rn=1

                  ;

                   

                  After you are clear about what you wished to get using that rownum=1, then we may talk about the rest.

                  1 person found this helpful
                  • 6. Re: date not incrementing
                    Saubhik

                    What I have seen from your other posts in past, the first question came into my mind is what is the data type of unique_date?

                    • 7. Re: date not incrementing
                      Frank Kulash

                      Hi,

                       

                      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.

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

                      • 8. Re: date not incrementing
                        Stefan Jager

                        To add to what Saubhik mentioned: to what datatypes are you comparing unique_date? And how is the ordering by unique_date done?

                        I bet it is not what you think it is...

                         

                        And, besides everything else already mentioned: using "distinct" is always a huge red flag to me...

                        • 9. Re: date not incrementing
                          Robeen

                          Hi,

                           

                          can you please help me

                           

                          ....

                            coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS')) as unique_date,

                          ....

                           

                          ROW_NUMBER() OVER (

                          ORDER BY unique_date

                             ) row_num,                           

                           

                          where should I place the ROW_NUMBER code?

                           

                          Regards,

                           

                          Joe

                          • 10. Re: date not incrementing
                            Robeen

                            Unique_date is char as per below code.

                              coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS')) as unique_date,

                             

                            But below code is comparing char to timestamp:

                            WHERE unique_date > TO_TIMESTAMP('24-Jul-2020', 'DD-MON-YYYY HH12:MI:SS')                                            

                             

                            Do you think the issue is with this part?

                             

                            Regards,

                             

                            Joe

                            • 11. Re: date not incrementing
                              Robeen

                              The 4th column has date values which are incrementing and decrementing. Normally with order by asc, the values should increment

                               

                              • 12. Re: date not incrementing
                                Saubhik

                                The issue in your first post about shorting of the data, If it's character then I don't see any problem in the way Oracle have sorted! It's sorted as character not date.

                                • 13. Re: date not incrementing
                                  Robeen

                                  So the problem lies here

                                   

                                    coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS')) as unique_date,

                                   

                                  I added

                                  TO_TIMESTAMP('unique_date', 'DD-MON-YYYY HH12:MI:SS') UD,

                                   

                                  ORA-01858: a non-numeric character was found where a numeric was expected

                                  01858. 00000 -  "a non-numeric character was found where a numeric was expected"

                                  *Cause:    The input data to be converted using a date format model was

                                             incorrect.  The input data did not contain a number where a number was

                                             required by the format model.

                                  *Action:   Fix the input data or the date format model to make sure the

                                             elements match in number and type.  Then retry the operation.

                                   

                                  I have to set the filed unique_date to this format because Streamset uses this format.

                                  • 14. Re: date not incrementing
                                    Stefan Jager

                                    crikey. You're going from bad to worse...

                                     

                                    Robeen wrote:

                                    TO_TIMESTAMP('unique_date', 'DD-MON-YYYY HH12:MI:SS') UD,

                                    Now you're telling Oracle to convert the string 'unique_date' to a timestamp... of course it's going to complain about that. It tries hard using implicit conversions, but  this is a bit much to ask...

                                     

                                    Robeen wrote:

                                    I have to set the filed unique_date to this format because Streamset uses this format.

                                    Never heard of Streamset, can't be bothered to look it up. Any tool/library/whatever that cannot handle date and time PROPERLY is not worth the effort. Get rid of it.

                                     

                                    Robeen wrote:

                                    So the problem lies here

                                     

                                    coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS')) as unique_date,

                                    Many of us have given you plenty of hints as to where the multiple problems with your sql lie. This is one (only one) of the issues with your sql.

                                     

                                    Hint: start looking into datatypes and how to handle them properly using (Oracle) SQL...

                                    1 2 Previous Next