I am attempting to use a loop in my Oracle SQL code that will change the name of the schema slightly each time the loop is run. I have tried the SQL code below, creating a NUMBER variable that counts up from 1 to 3 and then concatenating it with the schema name, but I keep receiving a long error message. The schema name should be 'x_rm_mt1_d' the first loop, 'x_rm_mt2_d' the second loop, and 'x_rm_mt3_d' on the final loop. The select statement in the code below is just a small example of what I am trying to do, I didn't think it would be pertinent to include the entire query that I am looking to use the variable for.
DECLARE
l_counter NUMBER := 0;
BEGIN
LOOP
l_counter := l_counter + 1;
IF l_counter > 3 THEN
EXIT;
END IF;
DEFINE StrSchema = 'x_rm_mt' || l_counter || '_d'
WITH aed AS (
SELECT DISTINCT SSN , STATE FROM (
--Effective Date During Quarter
SELECT *
FROM
(SELECT * FROM ADDRESS_EFF_DATE
WHERE STATE = 'VT'
AND EFF_DATE >= '01-JAN-20'
AND EFF_DATE < '01-APR-20'
AND ADDRESS_KEY = '0'
)aed
WHERE aed.EFF_DATE = (
SELECT MAX(aed2.EFF_DATE)
FROM ADDRESS_EFF_DATE aed2
WHERE aed2.CONTROL_ID = aed.CONTROL_ID
AND aed2.SSN = aed.SSN)
--Effective Date Prior to Quarter
UNION
SELECT *
FROM
(SELECT *
FROM Strschema.ADDRESS_EFF_DATE
WHERE STATE = 'VT'
AND EFF_DATE < '01-JAN-20'
AND ADDRESS_KEY = '0') aed
WHERE aed.EFF_DATE = (
SELECT MAX(aed1.EFF_DATE)
FROM Strschema.ADDRESS_EFF_DATE aed1
WHERE aed.ssn=aed1.ssn
AND aed.EFF_DATE < '01-JAN-20'
AND aed1.EFF_DATE < '01-JAN-20')) )
,
--Select all records from Employee Eff Date Where Latest Hire Date is before 4/1/20
--AND Term Date is Null or Term Date is Greater than 1/1/20 eed AS (
SELECT *
FROM
(SELECT *
FROM Strschema.EMPLOYEE_EFF_DATE eed
WHERE eed.CONTROL_ID = 'SMLMKT'
AND eed.EFF_DATE = (
SELECT MAX(eed1.EFF_DATE)
FROM Strschema.EMPLOYEE_EFF_DATE eed1
WHERE eed.SSN = eed1.SSN
AND eed1.CONTROL_ID = 'SMLMKT')
) eed3
WHERE eed3.LATEST_HIRE_DATE <= '04-APR-20'
AND (eed3.LAST_TERM_DATE > '01-JAN-20'
OR eed3.LAST_TERM_DATE is NULL) ) ,
ebe AS (
SELECT *
FROM Strschema.emp_ben_elects ebe
WHERE ebe.control_id = 'SMLMKT'
AND ebe.BENEFIT_ID = 'MEDICAL'
AND ebe.life_event_date = (
SELECT MAX(x.life_event_date)
FROM Strschema.employee_life_events x
WHERE x.ssn = ebe.ssn
AND x.control_id = 'SMLMKT'
AND ebe.p_company_id_i = x.p_company_id_i
AND x.life_event_status = 'C')
AND ebe.le_seq_no = (
SELECT MAX(x.le_seq_no)
FROM Strschema.employee_life_events x
WHERE x.ssn = ebe.ssn
AND x.control_id = 'SMLMKT'
AND ebe.p_company_id_i = x.p_company_id_i
AND x.life_event_status = 'C'
AND ebe.life_event_date = x.life_event_date)
)
SELECT cmp.NAME as "Client Name" , cssn.REAL_SSN as "Employee SSN"
--, aed.SSN as "FAKE SSN REMOVE" , eed.LAST_NAME as "Last Name" , eed.FIRST_NAME as "First Name" , aed.STATE as "Resident State" , eed.LATEST_HIRE_DATE as "Hire Date" , pi.DESCR1 as "Plan Name" , ebe.OPTION_ID as "Tier" , ebe.BENEFIT_EFF_DATE as "Coverage Start Date" , eed.LAST_TERM_DATE as "Coverage End Date"
--, eed.LAST_TERM_DATE
FROM eed INNER JOIN aed ON eed.SSN = aed.SSN
LEFT JOIN ebe ON eed.SSN = ebe.SSN
JOIN Strschema.COMP_SSN cssn ON eed.SSN = cssn.SSN
LEFT JOIN Strschema.PLAN_INFO pi ON ebe.PLAN_ID = pi.PLAN_ID AND ebe.BENEFIT_ID = pi.BENEFIT_ID
JOIN Strschema.COMPANY cmp ON eed.CURRENT_CO = cmp.COMPANY_ID
END LOOP;
END;
defineis a client (SQL Developer) command, not part of PL/SQL, and you'll need to use dynamic SQL if the schema isn't known until runtime. But what will you do with the selected data? You need to select into something (a collection if you're getting multiple rows) or use a cursor. That will affect how the dynamic SQL is written and executed. If you actually have three fixed schema names, which just happen to have a number in, why not use static SQL and union the three queries together - just because your realy query is too long to repeat?