You Asked

Hi, Ask TOM Team

I have a hierachical query where I use two user functions as follows:

Sample data:
Document = M310000000001 where 31 is the document type.
Document = M330000000001 where 33 is the document type and can reference to M310000000001.

--Function 1

CREATE OR REPLACE FUNCTION APP1.GET_DOCUMENT_TYPE (
  P_DOCUMENT IN VARCHAR2) 
  RETURN NUMBER AS
  DOCUMENT_TYPE NUMBER;
BEGIN
    IF(LENGTH(P_DOCUMENT) IN (11, 13)) THEN 
       DOCUMENT_TYPE := SUBSTR(P_DOCUMENT, 2, 2);
    ELSIF( LENGTH(P_DOCUMENT) = 19) THEN
       DOCUMENT_TYPE := SUBSTR(P_DOCUMENT, 10, 2);
    ELSE RETURN NULL;
    END IF;    
  RETURN DOCUMENT_TYPE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END GET_DOCUMENT_TYPE;
/

--Function 2

CREATE OR REPLACE FUNCTION APP1.GET_REFERENCED(
    P_CLIENT VARCHAR2,
    P_REFERENCED_DOCUMENT IN VARCHAR2) 
    RETURN VARCHAR2 AS
    REFERENCED_DOCUMENT VARCHAR2(19);
  BEGIN
    SELECT hr.modified_document
      INTO REFERENCED_DOCUMENT
      FROM (SELECT LEVEL,
                   h.client,
                   h.document,
                   ri.modified_document,
                   ri.modification_reason,
                   ri.modification_code_id,
                   ri.modified_document_date,
                   ri.client_other_payer
              FROM app1.header h LEFT JOIN app1.header_b2b h2
                ON h.header_id = eb.header_b2b_id LEFT JOIN app1.referenced_informations ir
                ON h2.header_b2b_id = ri.referenced_information_id
             WHERE h.client = P_CLIENT
             AND ri.modified_document IS NOT NULL         
             START WITH ri.modified_document = P_REFERENCED_DOCUMENT
             CONNECT BY NOCYCLE PRIOR ri.modified_document = h.document
             ORDER BY LEVEL DESC) hr WHERE ROWNUM = 1;
  RETURN REFERENCED_DOCUMENT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END GET_REFERENCED;
/

This query runs and never ends. I think is because the GET_REFERENCED function and the hierarchical query. Is there any way to avoid the Cyclic redundancy and process the data with no problem? And if you have some improvement for performance...

SELECT 
       h.client, 
       h.buyer, 
       h.document_type, 
       h.document, 
       h.issued_date,
       h2.total_tax, 
       h2.amount_taxed_total,
       h2.exempt_amount,
       h2.income_type_id,
       h2.total_amount_tax_retained,
       h2.total_amount_retention_revenue, 
       h2.total_amount_tax_perceived, 
       h2.total_amount_perception_revenue, 
       h2.header_b2b_id,
       h.id_foreign,
       ri.modified_document,
       ri.client_other_payer,
       h2.credit_note_indicator,
      TO_NUMBER(TO_CHAR(h.issued_date ,'YYYYMM')) period,
       h.registration_date registration_date_source
FROM   app1.header h
JOIN app1.header_b2b h2
    ON h.header_id = h2.header_b2b_id
LEFT JOIN app1.referenced_informations ir
          ON h2.eheader_b2b_id = ri.information_reference_id
WHERE TRUNC(h.registration_date) = TRUNC(SYSDATE-1)
     AND h.document_type NOT IN (41, 43, 47)
     AND h.client = '12345678' and h.document='M330000000003' --> If I change to anoher document of the same client, the query show the result very quickly so the problem is when the functions, especifically get_referenced function.
     AND h.document_type IN (33,34)
     AND (CASE WHEN app1.get_document_type(ri.modified_document) IN (33,34) THEN         
                    app1.get_document_type(app1.get_referenced(NVL(ri.client_other_payer,h.client), ri.modified_document))                  
               ELSE app1.get_document_type(ri.modified_document) 
          END NOT IN (11, 13, 17, 41, 43, 47) OR ri.modified_document IS NULL)

Thanks in advanced.

Regards,

and Connor said...

No DDL
No test data
No test case

I see a "header" table, a "header_b2b" table, a "referenced_informations " ....what are they? What is in them? How do they fit into this?

We want to help you but when there is no real way to help, that makes it hard :-(

Rating

(5 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

cwj, August 12, 2024 - 6:06 pm UTC

That's not a link to the printer maker. It's some guy in india.
I've been coming here for 20 years. first spam I've seen.

August 19, 2024 - 2:27 am UTC

Just unfortunate timing.

We get LOTS of spam but we monitor for it and wipe it to keep the site as clean as possible

Follow Up

A reader, August 13, 2024 - 6:26 pm UTC

Here are everything you need:

drop user app1 cascade;
create user app1 identified by app1;
grant create session to app1;
grant create any table to app1;
GRANT UNLIMITED TABLESPACE TO app1;

CREATE TABLE app1.header
(
header_id NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE) NOT NULL,
client VARCHAR2(11 BYTE) NOT NULL,
document VARCHAR2(19 BYTE) NOT NULL,
buyer VARCHAR2(13 BYTE) NOT NULL,
document_type NUMBER(2) NOT NULL,
amount NUMBER(18,2) NOT NULL,
issued_date DATE NOT NULL,
status NUMBER(1) NOT NULL,
registration_date DATE DEFAULT SYSDATE NOT NULL
);

CREATE UNIQUE INDEX app1.PK_header ON app1.header(header_id);

CREATE UNIQUE INDEX app1.UK_header_client_doc ON app1.header (client, document);

ALTER TABLE app1.header ADD (
CONSTRAINT PK_header
PRIMARY KEY
(header_id)
USING INDEX app1.PK_header
ENABLE VALIDATE
, CONSTRAINT UK_header_client_doc
UNIQUE (client, document)
USING INDEX app1.UK_header_client_doc
ENABLE VALIDATE);

CREATE TABLE app1.header_b2b
(
header_b2b_id NUMBER NOT NULL,
income_type_id NUMBER(2),
credit_note_indicator NUMBER(2),
total_tax NUMBER(18,2),
exempt_amount NUMBER(18,2),
total_amount_tax_retained NUMBER(18,2),
total_amount_retention_revenue NUMBER(18,2),
total_amount_tax_perceived NUMBER(18,2),
total_amount_perception_revenue NUMBER(18,2)
);

CREATE UNIQUE INDEX app1.PK_header_b2b ON app1.header_b2b (header_b2b_id);

ALTER TABLE app1.header_b2b ADD (
CONSTRAINT PK_header_b2b
PRIMARY KEY
(header_b2b_id)
USING INDEX app1.PK_header_b2b
ENABLE VALIDATE);

ALTER TABLE app1.header_b2b ADD (
CONSTRAINT FK_header_header_b2b
FOREIGN KEY (header_b2b_id)
REFERENCES app1.header (header_id)
ENABLE VALIDATE);

CREATE TABLE app1.referenced_informations
(
information_reference_id NUMBER NOT NULL,
modified_document VARCHAR2(19 BYTE) NOT NULL,
modified_document_date DATE,
modification_code_id NUMBER(1) NOT NULL,
modification_reason VARCHAR2(90 BYTE),
client_other_payer VARCHAR2(11 BYTE)
);

CREATE UNIQUE INDEX app1.PK_referenced_informations ON app1.referenced_informations (information_reference_id);

ALTER TABLE app1.referenced_informations ADD (
CONSTRAINT PK_referenced_informations
PRIMARY KEY
(information_reference_id)
USING INDEX app1.PK_referenced_informations
ENABLE VALIDATE);

ALTER TABLE app1.referenced_informations ADD (
CONSTRAINT FK_header_ref_info
FOREIGN KEY (information_reference_id)
REFERENCES app1.header_b2b (header_b2b_id)
ENABLE VALIDATE);

CREATE OR REPLACE FUNCTION APP1.GET_DOCUMENT_TYPE (
P_DOCUMENT IN VARCHAR2)
RETURN NUMBER AS
DOCUMENT_TYPE NUMBER;
BEGIN
IF(LENGTH(P_DOCUMENT) IN (11, 13)) THEN
DOCUMENT_TYPE := SUBSTR(P_DOCUMENT, 2, 2);
ELSIF( LENGTH(P_DOCUMENT) = 19) THEN
DOCUMENT_TYPE := SUBSTR(P_DOCUMENT, 10, 2);
ELSE RETURN NULL;
END IF;
RETURN DOCUMENT_TYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END GET_DOCUMENT_TYPE;
/

CREATE OR REPLACE FUNCTION APP1.GET_REFERENCED(
P_CLIENT VARCHAR2,
P_REFERENCED_DOCUMENT IN VARCHAR2)
RETURN VARCHAR2 AS
REFERENCED_DOCUMENT VARCHAR2(19);
BEGIN
SELECT hr.modified_document
INTO REFERENCED_DOCUMENT
FROM (SELECT LEVEL,
h.client,
h.document,
ri.modified_document,
ri.modification_reason,
ri.modification_code_id,
ri.modified_document_date,
ri.client_other_payer
FROM app1.header h LEFT JOIN app1.header_b2b h2
ON h.header_id = h2.header_b2b_id LEFT JOIN app1.referenced_informations ri
ON h2.header_b2b_id = ri.information_reference_id
WHERE h.client = P_CLIENT
AND ri.modified_document IS NOT NULL
START WITH ri.modified_document = P_REFERENCED_DOCUMENT
CONNECT BY NOCYCLE PRIOR ri.modified_document = h.document
ORDER BY LEVEL DESC) hr WHERE ROWNUM = 1;
RETURN REFERENCED_DOCUMENT;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END GET_REFERENCED;
/

---Test data:

-----1
Insert into APP1.HEADER
(HEADER_ID, CLIENT, DOCUMENT, BUYER, DOCUMENT_TYPE,
AMOUNT, ISSUED_DATE, STATUS, REGISTRATION_DATE)
Values
(163576022, '00118950161', 'E310000000015', '132345479', 31,
59, TO_DATE('7/19/2024', 'MM/DD/YYYY'), 1, TO_DATE('7/19/2024 1:19:19 PM', 'MM/DD/YYYY HH:MI:SS AM'));

Insert into APP1.HEADER_B2B
(HEADER_B2B_ID, INCOME_TYPE_ID, TOTAL_TAX)
Values
(163576022, 1, 9);
COMMIT;

-----2
Insert into APP1.HEADER
(HEADER_ID, CLIENT, DOCUMENT, BUYER, DOCUMENT_TYPE,
AMOUNT, ISSUED_DATE, STATUS, REGISTRATION_DATE)
Values
(163576249, '00118950161', 'E340000000002', '132345479', 34,
70.8, TO_DATE('7/19/2024', 'MM/DD/YYYY'), 1, TO_DATE('7/19/2024 1:21:23 PM', 'MM/DD/YYYY HH:MI:SS AM'));

Insert into APP1.HEADER_B2B
(HEADER_B2B_ID, INCOME_TYPE_ID, CREDIT_NOTE_INDICATOR, TOTAL_TAX)
Values
(163576249, 1, 0, 10.8);
COMMIT;

-----3
Insert into APP1.HEADER
(HEADER_ID, CLIENT, DOCUMENT, BUYER, DOCUMENT_TYPE,
AMOUNT, ISSUED_DATE, STATUS, REGISTRATION_DATE)
Values
(163584265, '00118950161', 'E330000000003', '132345479', 33,
11.8, TO_DATE('7/19/2024', 'MM/DD/YYYY'), 1, TO_DATE('7/19/2024 2:16:07 PM', 'MM/DD/YYYY HH:MI:SS AM'));

Insert into APP1.HEADER_B2B
(HEADER_B2B_ID, INCOME_TYPE_ID, TOTAL_TAX)
Values
(163584265, 1, 1.8);
COMMIT;

-----3: REFERENCED_INFORMATIONS
Insert into APP1.REFERENCED_INFORMATIONS
(INFORMATION_REFERENCE_ID, MODIFIED_DOCUMENT, MODIFIED_DOCUMENT_DATE, MODIFICATION_CODE_ID)
Values
(163576249, 'E310000000015', TO_DATE('7/19/2024', 'MM/DD/YYYY'), 1);

Insert into APP1.REFERENCED_INFORMATIONS
(INFORMATION_REFERENCE_ID, MODIFIED_DOCUMENT, MODIFIED_DOCUMENT_DATE, MODIFICATION_CODE_ID)
Values
(163584265, 'E340000000002', TO_DATE('7/19/2024', 'MM/DD/YYYY'), 3);
COMMIT;

--Query that hangs:

SELECT
h.client,
h.buyer,
h.document_type,
h.document,
h.issued_date,
h2.total_tax,
h2.exempt_amount,
h2.income_type_id,
h2.total_amount_tax_retained,
h2.total_amount_retention_revenue,
h2.total_amount_tax_perceived,
h2.total_amount_perception_revenue,
h2.header_b2b_id,
h.buyer,
ri.modified_document,
ri.client_other_payer,
h2.credit_note_indicator,
TO_NUMBER(TO_CHAR(h.issued_date ,'YYYYMM')) period,
h.registration_date registration_date_source
FROM app1.header h
JOIN app1.header_b2b h2
ON h.header_id = h2.header_b2b_id
LEFT JOIN app1.referenced_informations ri
ON h2.header_b2b_id = ri.information_reference_id
WHERE h.client = '00118950161' and h.document='E330000000003' --> I thnink there is a circular redundancy
AND h.document_type NOT IN (41, 43, 47)
AND (CASE WHEN app1.get_document_type(ri.modified_document) IN (33,34) THEN
app1.get_document_type(app1.get_referenced(NVL(ri.client_other_payer,h.client), ri.modified_document))
ELSE app1.get_document_type(ri.modified_document)
END NOT IN (11, 13, 17, 41, 43, 47) OR ri.modified_document IS NULL)

Regards,

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Follow Up

More to Explore

PL/SQL demos

PL/SQL docs

Avoid Cyclic redundancy in hierarchical query

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Follow Up

More to Explore

PL/SQL demos

PL/SQL docs