Home » SQL & PL/SQL » SQL & PL/SQL » Function or filter in hierarchy query (12c)
Function or filter in hierarchy query [message #667375] Tue, 26 December 2017 03:33 Go to next message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
Hello Experts,

I have a table storing the data in hierarchy , like both child and parent , my query is to filter only level 2 that is name of mch_code and mch_name whose OBJ_LEVEL='L2: Customer Site', The problem is i can pass any mch_code but it should return 'L2: Customer Site' value , for example if i pass 'AE-AAN-CAU-0011-CAR-WTS' or 'AE-AAN-CAU-0011-CAR' it must return 'AE-AAN-CAU-0011' 'BAWADI MALL'.
In Second example if i pass 'AE-DXBAZD-PPRCWT' or 'AE-DXBAZD-PPR' it should return 'AE-DXBAZD','TIM MALL'.



create table equipment_Tab(CONTRACT VARCHAR2(10),MCH_CODE VARCHAR2(100),MCH_NAME VARCHAR2(100),SUP_MACH_CODE VARCHAR2(100),OBJ_LEVEL VARCHAR2(30));

--example 1

INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011','BAWADI MALL','AE' ,'L2: Customer Site');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR','CARIBOU','AE-AAN-CAU-0011' ,'L3: Outlet/Area');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR-WTS','WATER TREATMENT SYSTEM','AE-AAN-CAU-0011-CAR' ,'L4: System');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR-WTS-FS','FILTRATION','AE-AAN-CAU-0011-CAR-WTS' ,'L5: Technology');
--example 2
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD','TIM MALL','AE' ,'L2: Customer Site');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD-PPR','Pump/ Plant Room','AE-DXBAZD' ,'L3: Outlet/Area');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD-PPRCWT','Chemical Water Treatment','AE-DXBAZD-PPR' ,'L4: System');
commit;

select * from equipment_tab


CONTRACT	MCH_CODE	MCH_NAME	SUP_MACH_CODE	OBJ_LEVEL
C02	AE-AAN-CAU-0011	BAWADI MALL	AE	L2: Customer Site
C02	AE-AAN-CAU-0011-CAR	CARIBOU	AE-AAN-CAU-0011	L3: Outlet/Area
C02	AE-AAN-CAU-0011-CAR-WTS	WATER TREATMENT SYSTEM	AE-AAN-CAU-0011-CAR	L4: System
C02	AE-AAN-CAU-0011-CAR-WTS-FS	FILTRATION	AE-AAN-CAU-0011-CAR-WTS	L5: Technology
C02	AE-DXBAZD	TIM MALL	AE	L2: Customer Site
C02	AE-DXBAZD-PPR	Pump/ Plant Room	AE-DXBAZD	L3: Outlet/Area
C02	AE-DXBAZD-PPRCWT	Chemical Water Treatment	AE-DXBAZD-PPR	L4: System


SELECT  MCH_CODE,MCH_NAME,OBJ_LEVEL
         FROM EQUIPMENT_TAB
         WHERE sup_mach_code IS NOT NULL AND contract = 'C02'
                AND OBJ_LEVEL='L2: Customer Site'
                  START WITH mch_code = 'AE-DXBAZD-PPRCWT'
                CONNECT BY mch_code = PRIOR sup_mach_code;




Report message to a moderator

Re: Function or filter in hierarchy query [message #667382 is a reply to message #667375] Tue, 26 December 2017 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Well done for your format, go on I think you can make it more unreadable.

Report message to a moderator

Re: Function or filter in hierarchy query [message #667386 is a reply to message #667382] Tue, 26 December 2017 04:09 Go to previous messageGo to next message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
i am sorry i copied whatever i tried, in short what ever the start with value i am providing it should return MCH_CODE of OBJ_LEVEL='L2: Customer Site'


create table equipment_Tab(CONTRACT VARCHAR2(10),MCH_CODE VARCHAR2(100),MCH_NAME VARCHAR2(100),SUP_MACH_CODE VARCHAR2(100),OBJ_LEVEL VARCHAR2(30));

--example 1

INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011','BAWADI MALL','AE' ,'L2: Customer Site');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR','CARIBOU','AE-AAN-CAU-0011' ,'L3: Outlet/Area');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR-WTS','WATER TREATMENT SYSTEM','AE-AAN-CAU-0011-CAR' ,'L4: System');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-AAN-CAU-0011-CAR-WTS-FS','FILTRATION','AE-AAN-CAU-0011-CAR-WTS' ,'L5: Technology');
--example 2
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD','TIM MALL','AE' ,'L2: Customer Site');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD-PPR','Pump/ Plant Room','AE-DXBAZD' ,'L3: Outlet/Area');
INSERT INTO EQUIPMENT_TAB(CONTRACT,MCH_CODE,MCH_NAME ,SUP_MACH_CODE ,OBJ_LEVEL) VALUES ('C02','AE-DXBAZD-PPRCWT','Chemical Water Treatment','AE-DXBAZD-PPR' ,'L4: System');
commit;


    SELECT *
      FROM EQUIPMENT_OBJECT_TAB
     WHERE sup_mch_code IS NOT NULL AND contract = 'CE01'
START WITH mch_code = 'AE-DXBAZD-PPRCWT' AND OBJ_LEVEL = 'L2: Customer Site'
CONNECT BY mch_code = PRIOR sup_mch_code;



CREATE OR REPLACE FUNCTION Get_MACHINE_LEVE2 (mch_code_   IN VARCHAR2,
                                              contract_   IN VARCHAR2)
   RETURN VARCHAR2
IS
   sup_obj_list_   VARCHAR2 (10000);
   counter_        NUMBER;

   CURSOR get_superior_objects
   IS
          SELECT sup_mach_code
            FROM equipment_tab
           WHERE sup_mach_code IS NOT NULL AND contract = contract_
      START WITH mch_code = mch_code_
      CONNECT BY mch_code = PRIOR sup_mach_code;
BEGIN
   sup_obj_list_ := '';
   counter_ := 0;


   FOR sup_object_ IN get_superior_objects
   LOOP
      IF (counter_ > 0)
      THEN
         sup_obj_list_ := sup_obj_list_ || ',';
      END IF;

      sup_obj_list_ :=
         sup_obj_list_ || '''' || sup_object_.sup_mach_code || '''';
      counter_ := counter_ + 1;
   END LOOP;

   RETURN sup_obj_list_;
END;


select Get_MACHINE_LEVE2 ('AE-DXBAZD-PPRCWT','C02')  from dual;  

'AE-DXBAZD-PPR','AE-DXBAZD','AE'

-- I need to extract the second string in this 'AE-DXBAZB'
 
--In the second example
select Get_MACHINE_LEVE2 ('AE-AAN-CAU-0011-CAR-WTS-FS','C02')  from dual 

'AE-AAN-CAU-0011-CAR-WTS','AE-AAN-CAU-0011-CAR','AE-AAN-CAU-0011','AE'

-- i need to extract 'AE-AAN-CAU-0011'

[Updated on: Tue, 26 December 2017 04:38]

Report message to a moderator

Re: Function or filter in hierarchy query [message #667389 is a reply to message #667382] Tue, 26 December 2017 05:29 Go to previous messageGo to next message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
Hello Michael,

i did using function , but i am wondering if the same can be done using query.


/* Formatted on 26/12/2017 3:24:10 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FUNCTION Get_MACHINE_LEVEL (mch_code_   IN VARCHAR2,
                                              contract_   IN VARCHAR2)
   RETURN VARCHAR2
IS
   sup_obj_list_   VARCHAR2 (10000);
   counter_        NUMBER;

   CURSOR get_superior_objects
   IS
          SELECT *
            FROM equipment_tab
           WHERE sup_mach_code IS NOT NULL AND contract = contract_
      START WITH mch_code = mch_code_
      CONNECT BY mch_code = PRIOR sup_mach_code;
BEGIN
   sup_obj_list_ := '';
   counter_ := 0;

   FOR sup_object_ IN get_superior_objects
   LOOP
      IF sup_object_.OBJ_LEVEL = 'L2: Customer Site'
      THEN
         sup_obj_list_ := sup_object_.MCH_NAME;
        
      END IF;
   END LOOP;

   RETURN sup_obj_list_;
END;


select Get_MACHINE_LEVEL ('AE-AAN-CAU-0011-CAR-WTS-FS','C02')  from dual

BAWADI MALL

Report message to a moderator

Re: Function or filter in hierarchy query [message #667489 is a reply to message #667389] Tue, 02 January 2018 02:09 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
As always I might be way off, but how about:
SELECT *
  FROM EQUIPMENT_TAB
 WHERE (
            'AE-AAN-CAU-0011-CAR-WTS' LIKE MCH_CODE || '%' OR 
            'AE-AAN-CAU-0011-CAR' LIKE MCH_CODE || '%'
       ) AND 
       OBJ_LEVEL LIKE 'L2%'

Report message to a moderator

Previous Topic: dense_rank performance
Next Topic: Query contain MODEL cause "column not allowed here" in select statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 24 22:36:51 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.