11 Replies Latest reply: Mar 19, 2013 12:31 PM by onedbguru RSS

    Need help with thelogic

    thinkingeye
      Hi all
      I have a table please see below:
      Number                                Name     Date                        Principa     Interest     Fee
      ARRA 21-10     Town of Boones Mill     9/19/2012          0     0     772.45
      ARRA 21-10     Town of Boones Mill     9/19/2012       0     2938.64     0
      ARRA 21-10     Town of Boones Mill     9/19/2012       0     3028.08     0
      ARRA 21-10     Town of Boones Mill     9/19/2012       6040.57     0     0
      ARRA 21-10     Town of Boones Mill     9/19/2012                     0     0     704.04
      ARRA 21-10     Town of Boones Mill     9/19/2012                     0     0     719.52
      ARRA 21-10     Town of Boones Mill     9/19/2012                     0     0     734.82
      ARRA 21-10     Town of Boones Mill     9/19/2012                     0     0     749.68
      ARRA 21-10     Town of Boones Mill     9/19/2012              0     2759.94     0
      ARRA 21-10     Town of Boones Mill     9/19/2012             0     2820.6     0
      ARRA 21-10     Town of Boones Mill     9/19/2012                0     2880.48     0
      ARRA 21-10     Town of Boones Mill     9/19/2012             6114.19     0     0
      ARRA 21-10     Town of Boones Mill     9/19/2012                     6189.4     0     0
      ARRA 21-10     Town of Boones Mill     9/19/2012                     6265.53     0     0
      Please need help with what logic i need to use to get the data in the below required format:
      Number                              Name     Date     Principa     Interest     Fee
      ARRA 21-10     Town of Boones Mill     9/19/2012     6040.57     2938.64     772.45
      ARRA 21-10     Town of Boones Mill     9/19/2012     6114.19     3028.08     704.04
      ARRA 21-10     Town of Boones Mill     9/19/2012     6189.4     2759.94     719.52
      ARRA 21-10     Town of Boones Mill     9/19/2012     6265.53     2820.6     734.82
      ARRA 21-10     Town of Boones Mill     9/19/2012     0     2880.48     749.68
      Any advice is greatly appreciated

      Thanks

      Edited by: thinkingeye on Feb 16, 2013 6:46 PM
        • 1. Re: Need help with thelogic
          sb92075
          Handle:     thinkingeye
          Status Level:     Newbie
          Registered:     Dec 13, 2007
          Total Posts:     784
          Total Questions:     214 (154 unresolved)


          after 5+ years can you even spell S-Q-L?

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Need help with thelogic
            rp0428
            You've posted more than enough to know that you need to provide your 4 digit Oracle version and that you need to use \
             tags to format data and code to make it readable.
            {quote}
            Hi all
            I have a table please see below:
            {quote}
            No you don't - there is no table in what you posted.
            {quote}
            Please need help with what logic i need to use to get the data in the below required format:
            {quote}
            You need to explain what the 'required format' is. You can't just post a bunch of data and leave it to others to try to figure out.
            
            Please spend some time reviiewing your old posts and marking them ANSWERED: 214 (154 unresolved) 
            
            You've created 214 threads and the vast majority of them have not been resolved by you. You need to do your part to keep the forum cleaned up.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Need help with thelogic
              thinkingeye
              Sorry guys,
              Yes you are right about what you said. Unfortunately can you see date from this user id when the last question was posted, this user id belongs to a person before me at work . I'm not a technical person myself and the previous guys arejust report writers so i cannot spell S Q L!! I'm an accountant and the amount of sql i know is not bad i guess.

              Thanks

              Edited by: thinkingeye on Feb 16, 2013 7:00 PM
              • 4. Re: Need help with thelogic
                rp0428
                You still need to explain how the result data is computed from the source data.

                What are the business rules involved?
                • 5. Re: Need help with thelogic
                  thinkingeye
                  Thanks, The data is coming from 6 different tables and i used join statements to get this data. There is a field called receivable type id which has values 1 2 3 and if its 1 its principal and 2 its interest and if 3 its fee. I used the case statement to create them as fields.
                  case when t5.ReceivableTypeId=1 then t5.ReceivableAmount else 0 end as Principal,
                  case when t5.ReceivableTypeId=2 then t5.ReceivableAmount else 0 end as Interest,
                   case when t5.ReceivableTypeId=3 then t5.ReceivableAmount else 0 end as Fee
                  But as i posted in the sample data set i couldn't get all the records in one row.
                  • 6. Re: Need help with thelogic
                    rp0428
                    Thanks for trying but that isn't enough detail.

                    You show this line of output
                    >
                    ARRA 21-10     Town of Boones Mill     9/19/2012     6040.57     2938.64     772.45
                    >
                    And that seems to be lines 1, 2 and 4 on the same line.

                    But you don't explain why line 3 shouldn't be included. All of the lines you show seem to be for the same set of key values and dates.

                    Takes each result line, one at a time, and explain how the values in it are calculated, which rows of source data those values come from and why only those rows are included and not others.
                    • 7. Re: Need help with thelogic
                      thinkingeye
                      Thanks rp0248,
                      The main reason is for me to generate a report accesing database tables. There is a table T1 which has field "receivable type id" (3 values 1 principal,2 interest, 3 fee). Another table T2 has Receivable amount (joined to the first table on Receivabletypeid),Similarly other tables were joined on the key fields, then i used the following sql
                      SELECT DISTINCT t1.loannumber, 
                                              t6.clientname, 
                                              t5.receivabletypeid, 
                                              t5.receivableadddate, 
                                              t5.receivableamount, 
                                              Row_number() 
                                                OVER( 
                                                  partition BY t1.loannumber, t5.receivabletypeid 
                                                  ORDER BY t5.receivabletypeid) AS rn 
                              FROM   table1 t1 
                                     LEFT OUTER JOIN table2 t2 
                                                  ON t1.projectid = t2.projectid 
                                     LEFT OUTER JOIN table3 t3 
                                                  ON t1.loanid = t3.accountid 
                                     LEFT OUTER JOIN table4 t4 
                                                  ON t3.transactionid = t4.transactionid 
                                     LEFT OUTER JOIN table5 t5 
                                                  ON t4.receivableid = t5.receivableid 
                                     LEFT OUTER JOIN table6 t6 
                                                  ON t2.clientid = t6.clientid 
                              WHERE  t5.receivabletypeid IS NOT NULL 
                                     AND t1.loannumber = 'ARRA 21-10'
                      Then i got the following dataset
                      loannumber     clientname     receivabletypeid     receivableamount
                      ARRA 21-10     Town of Boones Mill     1     6040.57
                      ARRA 21-10     Town of Boones Mill     1     6114.19
                      ARRA 21-10     Town of Boones Mill     1     6189.4
                      ARRA 21-10     Town of Boones Mill     1     6265.53
                      ARRA 21-10     Town of Boones Mill     2     2759.94
                      ARRA 21-10     Town of Boones Mill     2     2820.6
                      ARRA 21-10     Town of Boones Mill     2     2880.48
                      ARRA 21-10     Town of Boones Mill     2     2938.64
                      ARRA 21-10     Town of Boones Mill     2     3028.08
                      ARRA 21-10     Town of Boones Mill     3     704.04
                      ARRA 21-10     Town of Boones Mill     3     719.52
                      ARRA 21-10     Town of Boones Mill     3     734.82
                      ARRA 21-10     Town of Boones Mill     3     749.68
                      ARRA 21-10     Town of Boones Mill     3     772.45
                      Then in oreder to get the way the report is supposed to be (the report wanted 3 different columns PRINCIPA,INTEREST and FEE) I used the following sql
                      select distinct t1.LoanNumber,t6.ClientName,
                      case when t5.ReceivableTypeId=1 then t5.ReceivableAmount else 0 end as Principal,
                      case when t5.ReceivableTypeId=2 then t5.ReceivableAmount else 0 end as Interest,
                       case when t5.ReceivableTypeId=3 then t5.ReceivableAmount else 0 end as Fee
                      FROM   table1 t1 
                                     LEFT OUTER JOIN table2 t2 
                                                  ON t1.projectid = t2.projectid 
                                     LEFT OUTER JOIN table3 t3 
                                                  ON t1.loanid = t3.accountid 
                                     LEFT OUTER JOIN table4 t4 
                                                  ON t3.transactionid = t4.transactionid 
                                     LEFT OUTER JOIN table5 t5 
                                                  ON t4.receivableid = t5.receivableid 
                                     LEFT OUTER JOIN table6 t6 
                                                  ON t2.clientid = t6.clientid 
                              WHERE  t5.receivabletypeid IS NOT NULL 
                                     AND t1.loannumber = 'ARRA 21-10'
                      And i ended up with the dataset that i mentioned in my initial post, and what i wanted to see is the second table in the post, hope i explained good.

                      Thanks

                      Edited by: thinkingeye on Feb 16, 2013 7:52 PM
                      • 8. Re: Need help with thelogic
                        rp0428
                        Well, you need to wait for someone else to try to help you. I ask you to explain, in English, how to compute the result.
                        >
                        Takes each result line, one at a time, and explain how the values in it are calculated, which rows of source data those values come from and why only those rows are included and not others.
                        >
                        And you just posted more code but that code doesn't compute the correct result.

                        So I can't help you. You can't write code until you have defined how the data needs to be combined to get the correct result.

                        You need to take a small set of the source data you have and show how each of the data pieces is combined to produce one line of the result. After you show how to do that you can try to write code that does the same calculations for all of the data.
                        • 9. Re: Need help with thelogic
                          Etbin
                          Starting from - walking on thin ice
                          loannumber     clientname              receivabletypeid     receivableamount
                          ARRA 21-10     Town of Boones Mill      1                   6040.57
                          ARRA 21-10     Town of Boones Mill      1                   6114.19
                          ARRA 21-10     Town of Boones Mill      1                   6189.4
                          ARRA 21-10     Town of Boones Mill      1                   6265.53
                          ARRA 21-10     Town of Boones Mill      2                   2759.94
                          ARRA 21-10     Town of Boones Mill      2                   2820.6
                          ARRA 21-10     Town of Boones Mill      2                   2880.48
                          ARRA 21-10     Town of Boones Mill      2                   2938.64
                          ARRA 21-10     Town of Boones Mill      2                   3028.08
                          ARRA 21-10     Town of Boones Mill      3                   704.04
                          ARRA 21-10     Town of Boones Mill      3                   719.52
                          ARRA 21-10     Town of Boones Mill      3                   734.82
                          ARRA 21-10     Town of Boones Mill      3                   749.68
                          ARRA 21-10     Town of Boones Mill      3                   772.45
                          you might - NOT TESTED!
                          select loannumber,
                                 clientname,
                                 max(decode(receivabletypeid,1,receivableamount)) principal.
                                 max(decode(receivabletypeid,2,receivableamount)) interest,
                                 max(decode(receivabletypeid,3,receivableamount)) fee
                            from (select loannumber,clientname,receivabletypeid,receivableamount,receivableamount
                                         row_number() over (partition by receivabletypeid order by receivableamount) the_row
                                 )
                           group by loannumber,clientname,the_row
                          or use <tt>pivot</tt> not wanting to appear too old fashioned

                          Regards

                          Etbin
                          • 10. Re: Need help with thelogic
                            thinkingeye
                            Thanks Etin..
                            • 11. Re: Need help with thelogic
                              onedbguru
                              In the future, you may not want to use actual data (Town of Boones Mill - (VA??) been there...). There is a concept called obfuscation. Use it before some company takes you to court for divulging sensitive financial information to the world.