Toad World® Forums

Script automation using global temp tables?

Hi, first post here!

I'm trying to automate a script by using values inserted into global temp tables, that way I can update the data in the temp tables and rerun the scripts without having to modify the scripts.

I'm fairly new to Oracle, my previous experience is in sql server. In sql server it seems a lot easier to create temp tables over Oracle, and I'm struggling to replicate how I would approach automation.

However, the automated script is running for over 90 minutes compared to using hard coded values running for a couple minutes. Here's the code I wrote, for reference:

SELECT
SBU_CLIENT
,SBU_LOB
,PROCEDURECODE
,BRANDNAME
,SUM(ALLOWEDAMT) ALLOWED
,SUM(ASP_ALLOWED) ASP_ALLOWED
,SUM(AWP_ALLOWED) AWP_ALLOWED
,SUM(UNITS) UNITS
,SUM(CLAIM_COUNT) CLAIMS
,COUNT(DISTINCT(MEMBERID)) PATIENTS
FROM PROD_ICORE.MASTER_FINAL A
LEFT OUTER JOIN (
SELECT
MEMBERID||PROCEDURECODE AS NEW_START_KEY
FROM PROD_ICORE.MASTER_FINAL
WHERE
SBU_QUARTER BETWEEN (select distinct start_quarter from dp) and (select distinct end_quarter from dp)
AND PROCEDURECODE in (select distinct HCPS from dp)
AND MEMBERID||PROCEDURECODE NOT IN (
SELECT DISTINCT
(MEMBERID||PROCEDURECODE)
FROM PROD_ICORE.MASTER_FINAL
WHERE
SBU_QUARTER BETWEEN (select distinct beg_lookback from lb) and (select distinct end_lookback from lb)
)
AND ((PROCEDURECODE in (select distinct HCPS from dp))
or (NDCCODE in (select distinct ndc from dp)))
GROUP BY
MEMBERID||PROCEDURECODE
) B ON A.MEMBERID||PROCEDURECODE = B.NEW_START_KEY
WHERE
SBU_CLIENT = (select distinct sbu_client from dp)
AND SBU_QUARTER BETWEEN (select distinct start_quarter from dp) and (select distinct end_quarter from dp)
AND PROCEDURECODE in (select distinct HCPS from dp)
GROUP BY
SBU_CLIENT
,SBU_LOB
,PROCEDURECODE
,BRANDNAME
;

All of the sub-queries are where I'm storing variable data that is intended to change over time/iterations of the script.

I suppose I have two questions:

1.) is this how I should be approaching automation? I'm struggling to find examples of people doing what I'm trying to do, but I'm also struggling to find examples of alternative approaches.

2.)Is there a way that I can speed up the query results? Realistically if this takes hours to run then it isn't an adequate substitute for hard coded values in a script.

Thank you for any advice you can offer!

Also I'm sorry if I put this question in the wrong spot, these forums are arranged in a way that I don't understand.