5 Replies Latest reply on Jul 10, 2020 5:10 PM by Frank Kulash

    Logic issue

    4243516

      HI Team,

       

      Please help me in writing logic, the requirement is to fetch:

       

      1. The ID'S which are active i.e. sysdate is between start_date and end_date.

      2. I need to filter out(exclude) the records which are active i.e. where  sysdate is between start_date and end_date but address_id is different.

       

      Through my below query it is satisfying point 1. but for point 2 it is not excluding this record:

       

      with temp as(

      select distinct  id, address_id from(select id, address_id,row_number() over partition by id,address_id order by id)rn from  pp_prov_addr ppad

      where ppad.address_type='BILLING' and sysdate between ppad.start_date and ppad.end_date) where rn=1),

      BLL_ADDR as( select id,address_id from temp where id NOT IN (SELECT ID FROM TEMP)

      UNION

      (SELECT t1.id, t1.address_id from((select id, address_id,row_number() over partition by id,address_id order by id)rn  

      from  pp_prov_addr ppad

      where ppad.address_type='BILLING' and sysdate between ppad.start_date and ppad.end_date) t1 where rn=1))

       

       

      Note: Above query is used in stored procedure. Please help for logic.

        • 1. Re: Logic issue
          Paulzip

          with temp as(

          select distinct  id, address_id from(select id, address_id,row_number() over partition by id,address_id order by id)rn from  pp_prov_addr ppad

          where ppad.address_type='BILLING' and sysdate between ppad.start_date and ppad.end_date) where rn=1),

          BLL_ADDR as( select id,address_id from temp where id NOT IN (SELECT ID FROM TEMP)

          UNION

          (SELECT t1.id, t1.address_id from((select id, address_id,row_number() over partition by id,address_id order by id)rn 

          from  pp_prov_addr ppad

          where ppad.address_type='BILLING' and sysdate between ppad.start_date and ppad.end_date) t1 where rn=1))

           

          That isn't even valid SQL syntax, did you actually try it?!

           

          You've provided no sample data or table creation scripts or the Oracle version or what your expected output is based on the sample data.

          • 2. Re: Logic issue
            4243516

            Yes as i mentioned this is being used in stored procedure wherein select statement will be written, but since it was huge so I could not share the actual logic part.

             

            Sample:

             

            1. I want to exclude below record:

             

            ID   ADDRESS_ID        start_date                   end_date          address_type

             

            1          100                   1-04-2019                   1-01-1900         billing

            1          200                   1-03-2019                    1-01-1900        billing

            • 3. Re: Logic issue
              Paulzip

              4243516 wrote:

               

              Yes as i mentioned this is being used in stored procedure wherein select statement will be written,

              As I mentioned : The select statement isn't even valid SQL.

               

              Sample:

               

              1. I want to exclude below record:

               

              ID ADDRESS_ID start_date end_date address_type

               

              1 100 1-04-2019 1-01-1900 billing

              1 200 1-03-2019 1-01-1900 billing

              Can you write SQL against that? No. Then neither can we.  We answer a lot of questions for free on this forum and don't have time to create people's tables based on descriptions or some bit of text data pasted into a question. Which is why ask for table creation and data scripts.  It helps us to help YOU.

               

              but since it was huge so I could not share the actual logic part.

              Which is why you use some common sense and simply do a cut down version of your problem with a few rows.

              • 4. Re: Logic issue
                4243516

                Ok that's fine but it was not possible to paste company's whole stored procedure here so i just described my issue to get some help.

                • 5. Re: Logic issue
                  Frank Kulash

                  Hi,

                  4243516 wrote:

                   

                  Ok that's fine but it was not possible to paste company's whole stored procedure here so i just described my issue to get some help.

                  That's okay; there's no need to post any stored procedure.  But you do need to post a little sample data (CREATE TABLE and INSERT statements), the exact results you want from that sample data, and an explanation of the general rules that produce those specific results.

                  Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

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