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 |
buggleboy007
Messages: 282 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 1493 times)
|
|
|
|
Re: Oracle ad-hoc report issues [message #671435 is a reply to message #671434] |
Wed, 29 August 2018 16:43 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 16:35As 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 #671437 is a reply to message #671436] |
Wed, 29 August 2018 17:48 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 17:41So 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 #671439 is a reply to message #671438] |
Wed, 29 August 2018 17:51 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 17:50Yeah, 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 #671441 is a reply to message #671440] |
Wed, 29 August 2018 17:58 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 17:56No 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 |
pablolee
Messages: 2882 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 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 18:02syntax:
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 #671445 is a reply to message #671444] |
Wed, 29 August 2018 18:10 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
pablolee wrote on Wed, 29 August 2018 18:07Yes, 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 #671496 is a reply to message #671446] |
Fri, 31 August 2018 11:58 |
buggleboy007
Messages: 282 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;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:40:30 CDT 2024
|