Home » SQL & PL/SQL » SQL & PL/SQL » Oracle ad-hoc report issues (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0, Windows 10 Professional)
Oracle ad-hoc report issues [message #671433] Wed, 29 August 2018 15:23 Go to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
I have a query which basically gives me all the details from a table called "Assessment:" What the query does is, whenever there has been an Assessment, it checks to see for that same Assessment, if there has been a Reassessment. If there has been then it retrieves all those results (including for cases where there has not been any Reassessment).

What I actually want is, to retrieve the values where in TP_DUE_DATE for an Assessment and for the Reassessment are different. (Basically the TP_DUE_DATE for Assessment and Reassessment should never change; however it is changing).
Example is Assessment number: 190. If you look at the chain of data, the original assessment for 190, was as follows:

Assessment Number        Reassessment Number     TP_DUE_DATE     Comments

189                         NULL                    20-08-2017    The tax payer filed his assessment and the number was 189
189                          190                    24-08-2017    The tax payer's case was reassessed and it became 190
190                         NULL		    24-08-2017    The same tax payer's reassessment number (which was 190 becomes 						                                                   assessment) 

If you see(in the screenshot attached) the tp_due_date has changed from 20th Aug to 24th August. It should not change. My quest is to find the records that have had their tp_due_dates changed and the query I used was:

SELECT TAX_PERIOD_OBJ(tax_period_no).get_desc() TP,
       assess_no AS "Assessment Number",
       assess_new_assess_no AS "Reassessment Number",
       CONCAT(LPAD(' ', LEVEL*3-3),assess_no) AS "Hierarchy" ,
       LEVEL,
       assess_type_no,
       tax_payer_no,
       tax_type_no,
       tax_account_no,
       tax_period_no, 
       entry_date,
       reception_date,
       tp_due_date,
       tax_to_pay,
       tax_paid,pen_to_pay,
       pen_paid, 
       int_to_pay,
       int_paid,
       balance,
       tp_start_date,
       tp_end_date, 
       int_date,
       pay_pen_date,
       file_pen_date,
       reassess_reason_no,
       update_date,
       update_user,
       tax_credit,
       pen_credit,
       int_credit, 
       tax_paym,
       pen_paym,
       int_paym
FROM   assessment A
--WHERE TRUNC(tp_due_date) <> TRUNC(tp_payment_date)
CONNECT BY PRIOR assess_no = assess_new_assess_no
START WITH assess_new_assess_no IS NULL
ORDER BY assess_no DESC;

I did use an in-line query but it never worked i.e. gave me errors saying "ORA-00904: "A"."TP_DUE_DATE": invalid identifier". Can any one help me?
  • Attachment: Capture.PNG
    (Size: 46.00KB, Downloaded 236 times)
Re: Oracle ad-hoc report issues [message #671434 is a reply to message #671433] Wed, 29 August 2018 16:35 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
As usual, these questions tend to get answered much quicker if you supply some test data in the form of create table and insert statements.
Re: Oracle ad-hoc report issues [message #671435 is a reply to message #671434] Wed, 29 August 2018 16:43 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 16:35
As usual, these questions tend to get answered much quicker if you supply some test data in the form of create table and insert statements.
True. But with what I have mentioned along with screen shot can you or to that matter no one help me?
Re: Oracle ad-hoc report issues [message #671436 is a reply to message #671435] Wed, 29 August 2018 17:41 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
So to summarise, if an assessment has >1 date associated with it, then it is a problem, yes? And you want to identify that Assessment.

[Updated on: Wed, 29 August 2018 17:41]

Report message to a moderator

Re: Oracle ad-hoc report issues [message #671437 is a reply to message #671436] Wed, 29 August 2018 17:48 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 17:41
So to summarise, if an assessment has >1 date associated with it, then it is a problem, yes? And you want to identify that Assessment.
No. Slightly different. If an assessment has a reassessment (so in this case, 189 which is the original assessment number had an reassessment and that number is 190) then the TP_DUE_DATE should not change. In the figure/attachment if you see, the TP_DUE_DATE has changed from "20-08-2017" to "24-08-2017".

So in other words, if the assessment has reassessment and it's TP_DUE_DATES change, then those are the cases that I am interested in. If it has greater than 1 TP_DUE_DATE but the dates are same, then I am not interested in them for my output.

Hope this helps.
Re: Oracle ad-hoc report issues [message #671438 is a reply to message #671437] Wed, 29 August 2018 17:50 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Yeah, that was my intended meaning.
Why not just count, for each assessment, the distinct number of dates and filter on any where the count is > 2?
Re: Oracle ad-hoc report issues [message #671439 is a reply to message #671438] Wed, 29 August 2018 17:51 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 17:50
Yeah, that was my intended meaning.
Why not just count, for each assessment, the distinct number of dates and filter on any where the count is > 2?
How do you do that? Can you explain with Syntax? Also the query that I came up with, won't it help me with my cause? or do I have to write something new?
Re: Oracle ad-hoc report issues [message #671440 is a reply to message #671439] Wed, 29 August 2018 17:56 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
No problem, just supply some sample data and I'll write a quick example
Re: Oracle ad-hoc report issues [message #671441 is a reply to message #671440] Wed, 29 August 2018 17:58 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 17:56
No problem, just supply some sample data and I'll write a quick example
Well, I currently do not have access to that data as I am at home, plus there are so many rows in that table, hence I uploaded a screenshot. Can you not use the screenshot as sample data and then help me out?
Re: Oracle ad-hoc report issues [message #671442 is a reply to message #671441] Wed, 29 August 2018 18:02 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
syntax:
count(distinct date_column) over (partition by assessment_id)

Based on the data you 'supplied' that'll count the number of dates for any given assessment_id.

[Edit] As an aside, it is perfectly feasible to write create and insert statements on a notepad doc, no need to have access to your data.

[Updated on: Wed, 29 August 2018 18:03]

Report message to a moderator

Re: Oracle ad-hoc report issues [message #671443 is a reply to message #671442] Wed, 29 August 2018 18:03 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 18:02
syntax:
count(distinct date_column) over (partition by assessment_id)

Based on the data you 'supplied' that'll count the number of dates for any given assessment_id.

[Edit] As an aside, it is perfectly feasible to write create and insert statement on a notepad doc.
well, when I said "number of dates", I meant I need the details of the data (the columns that I mentioned in my SELECT clause). That will give me the COUNT. I did not mean COUNT as a function. Sorry for the confusion.
Re: Oracle ad-hoc report issues [message #671444 is a reply to message #671443] Wed, 29 August 2018 18:07 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Yes, and selecting al rows where the count is >1 will do that for you.
Have a look http://www.orafaq.com/node/55 for a few examples of analytics

[Updated on: Wed, 29 August 2018 18:08]

Report message to a moderator

Re: Oracle ad-hoc report issues [message #671445 is a reply to message #671444] Wed, 29 August 2018 18:10 Go to previous messageGo to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
pablolee wrote on Wed, 29 August 2018 18:07
Yes, and selecting al rows where the count is >1 will do that for you.
Have a look http://www.orafaq.com/node/55 for a few examples of analytics
Sure. Thanks Pablolee. I will try the method that you suggested and also go through the example now, so that I know what I am doing. I will let you know how it goes.
Re: Oracle ad-hoc report issues [message #671446 is a reply to message #671445] Wed, 29 August 2018 18:10 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Good luck.
Re: Oracle ad-hoc report issues [message #671496 is a reply to message #671446] Fri, 31 August 2018 11:58 Go to previous message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
Hi Pablolee,

I attempted my question to the problem through a PL/SQL program. I just could not resolve it via a SQL query. Not sure, if the mission could have been accomplished through a SQL query(may be it is totally possible). Anyways, my solution is posted below:

DECLARE
        /*
          The cursor below gives me a list of Assessments and its Reassessments 
          along the original assessment due date(TP_DUE_DATE)
          It does not give me ONLY Assessments that have had NO reassessments 
        */
    CURSOR cur_assess IS
       SELECT TAX_PERIOD_OBJ(tax_period_no).get_desc() TP,
             assess_no ,
             assess_new_assess_no,
             LEVEL,
             tp_due_date
        FROM assessment 
       WHERE TAX_PERIOD_OBJ(tax_period_no).get_desc() IS NOT NULL
          AND NOT (LEVEL = 1 AND assess_new_assess_no IS NULL)
          AND LEVEL = 1
      --CONNECT BY PRIOR assess_new_assess_no = assess_no
      CONNECT BY NOCYCLE PRIOR assess_new_assess_no = assess_no
      ORDER BY assess_no DESC;
 
      /*The below cursor looks at the Assessments retrieved from the above
        and then fetches it's due date (TP_DUE_DATE) of Reassessment 
        and then validations are carried out in the LOOPS below
      */
      CURSOR cur_assess_reassess (MAIN_ASSESSMENT_ID IN varchar2) IS
        SELECT assess_no           ,
               assess_new_assess_no,
               LEVEL,
               tp_due_date
        FROM assessment 
        WHERE TAX_PERIOD_OBJ(tax_period_no).get_desc() IS NOT NULL
        AND NOT (LEVEL = 1 AND assess_new_assess_no IS NULL)
        CONNECT BY PRIOR assess_new_assess_no = assess_no
        START WITH ASSESS_NO = MAIN_ASSESSMENT_ID;
 
lv_DtDueDate                 DATE;
lv_NbrDueDateChanged         NUMBER:=0;
lv_NbrDueDateNotChanged      NUMBER:=0;
 
BEGIN
 
 FOR MAIN_LIST_REC IN cur_assess LOOP
   DBMS_OUTPUT.put_line('******************************************************************************************************');
   DBMS_OUTPUT.put_line('Tax Period : '||MAIN_LIST_REC.TP||
                        ' Assessment no: '||MAIN_LIST_REC.assess_no||
                        ' - It''s Reassessment no: '||MAIN_LIST_REC.assess_new_assess_no||
                        ' and TP Due Date is: '||TO_CHAR(MAIN_LIST_REC.tp_due_date,'DD-MON-YYYY')
                        );
                        
          FOR SUB_LIST_REC IN cur_assess_reassess (MAIN_LIST_REC.assess_no) LOOP
              IF SUB_LIST_REC.LEVEL = 1 THEN 
                lv_DtDueDate := TRUNC(SUB_LIST_REC.tp_due_date);
              ELSE
                IF trunc(lv_DtDueDate) != TRUNC(SUB_LIST_REC.tp_due_date) THEN 
                  DBMS_OUTPUT.put_line('Assessment no: '||SUB_LIST_REC.assess_no||' - Reassessment no: '||
                                       NVL(TO_CHAR(SUB_LIST_REC.assess_new_assess_no),'No further Re-assessment(s)')|| ' and TP Due Date is: '||TO_CHAR(SUB_LIST_REC.tp_due_date,'DD-MON-YYYY')
                                       );
                   lv_NbrDueDateChanged:=lv_NbrDueDateChanged+1;
                   DBMS_OUTPUT.put_line('******************************************************************************************************');
                    DBMS_OUTPUT.put_line('');
                ELSIF trunc(lv_DtDueDate) = TRUNC(SUB_LIST_REC.tp_due_date) THEN 
                       DBMS_OUTPUT.put_line('Since the due dates are the same, this record is not counted/displayed.');  
                       lv_NbrDueDateNotChanged:=lv_NbrDueDateNotChanged+1;                 
                END IF;
              END IF;
                 
          END LOOP;
 
  END LOOP;
  DBMS_OUTPUT.put_line('');
  DBMS_OUTPUT.put_line('Total Number of records that have their TP_DUE_DATE changed: '||lv_NbrDueDateChanged);
  DBMS_OUTPUT.put_line('Total Number of records where TP_DUE_DATE is not changed: '||lv_NbrDueDateNotChanged);
 
END;
 


Previous Topic: Query to group data
Next Topic: Urgent : SQL Queries
Goto Forum:
  


Current Time: Thu Jul 18 14:35:22 CDT 2019