Advertisement
ovalerio

Using Oracle SYS_CONNECT to obtain total flight times.

Sep 23rd, 2019
997
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- CREATING TABLE FLIHTS
  2. CREATE TABLE FLIGHTS ( SOURCE VARCHAR(20), DESTIN VARCHAR(20), FLIGHT_TIME  NUMBER(3,1));
  3.  
  4. -- POPULATING TABLE FLIGHTS
  5. INSERT INTO FLIGHTS VALUES ('San Jose', 'Los Angeles', 1.3);
  6. INSERT INTO FLIGHTS VALUES ('New York', 'Boston', 1.1);
  7. INSERT INTO FLIGHTS VALUES ('Los Angeles', 'New York', 5.8);
  8.  
  9. -- HACK MAKING USE OF THE PATH AS A REPRESENTATION OF WHAT NEEDS TO BE DONE
  10. -- REF. https://databaseline.bitbucket.io/how-to-multiply-across-a-hierarchy-in-oracle-part-1/
  11. CREATE OR REPLACE FUNCTION eval_expr (expr_in IN VARCHAR2)
  12.   RETURN NUMBER
  13.   AUTHID CURRENT_USER
  14.   DETERMINISTIC
  15.   RESULT_CACHE
  16. IS
  17.   PRAGMA UDF;
  18.   v_res NUMBER;
  19. BEGIN   -- STORE PROCEDURE WAS MODIFIED TO AVOID THE SELECT CLAUSE
  20.   EXECUTE IMMEDIATE 'BEGIN :out := ' || expr_in || '; end;' USING OUT v_res;
  21.   RETURN v_res;
  22. END eval_expr;
  23. /
  24.  
  25. -- USING SYS_CONNECT_BY_PATH TO CREATE THE ACCUMULATED FLIGHT TIME AND REMOVING LEADING + SIGN
  26. SELECT CONNECT_BY_ROOT source, destin,  
  27. EVAL_EXPR(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(flight_time), '+'),2)) AS "Total Flight Time",
  28. SYS_CONNECT_BY_PATH(destin, '/') "Path", LEVEL
  29. FROM FLIGHTS
  30. START WITH source IN (SELECT source FROM flights)
  31. CONNECT BY PRIOR destin = source
  32. ORDER BY LEVEL;
Advertisement
RAW Paste Data Copied
Advertisement