A reader, March 04, 2025 - 10:33 am UTC
Hi Chris,
Does this import also the metadata of the tables?
March 04, 2025 - 1:34 pm UTC
If you use views_as_tables then it exports metadata for the view as-if it's a table. If you use query you'll get metadata from the table(s) the where clause applies to.
Sample sql script to parameter file
A reader, March 04, 2025 - 10:37 am UTC
Here is a sample of my parameter file and sql script,
Hello,
I was able to include a UNION under a subquery
Sample query:
QUERY=CCCC7.CC_RESERVELINE:"WHERE ID IN (
select
rl.ID
from
CCCC7.CC_RESERVELINE rl,
CCCC7.cc_Exposure expo,
CCCC7.cc_claim clm,
CCCC7.cctl_claimstate cst,
CCCC7.cctl_state st
where
expo.ID = rl.EXPOSUREID
and clm.ID = expo.ClaimID
and cst.ID = clm.state
and st.ID = clm.JURISDICTIONSTATE
and st.name = 'Treasury Managed Fund'
union
select
rl.ID
from
CCCC7.CC_RESERVELINE rl,
CCCC7.CCX_QBE_REPORTFINANCIALS crep,
CCCC7.cc_Exposure expo,
CCCC7.cc_claim clm,
CCCC7.cctl_claimstate cst,
CCCC7.cctl_state st
where
crep.QBE_RESERVELINEID = rl.ID
and expo.ID = crep.QBE_EXPOSUREID
and clm.ID = expo.ClaimID
and cst.ID = clm.state
and st.ID = clm.JURISDICTIONSTATE
and st.name = 'Treasury Managed Fund'
union
select
rl.ID
from
CCCC7.CC_RESERVELINE rl,
CCCC7.CC_Transaction txn,
CCCC7.cc_Exposure expo,
CCCC7.cc_claim clm,
CCCC7.cctl_claimstate cst,
CCCC7.cctl_state st
where
txn on txt.RESERVELINEID = rl.ID
and expo.ID = txn.EXPOSUREID
and clm.ID = rl.ClaimID
and cst.ID = clm.state
and st.ID = clm.JURISDICTIONSTATE
and st.name = 'Treasury Managed Fund'
)"
Sample SQL:
select rl.* from CCCC7.CC_RESERVELINE rl
inner join CCCC7.cc_Exposure expo on expo.ID = rl.EXPOSUREID
inner join CCCC7.cc_Claim clm on clm.ID = expo.ClaimID
inner join CCCC7.cctl_claimstate cst on cst.ID = clm.state
inner join CCCC7.cctl_state st on st.ID = clm.JurisdictionState
where st.name='Treasury Managed Fund'
union
select rl.* from CCCC7.CC_RESERVELINE rl
sinner join CCCC7.CCX_QBE_REPORTFINANCIALS crep on crep.QBE_RESERVELINEID = rl.ID
inner join CCCC7.cc_Exposure expo on expo.ID = crep.QBE_EXPOSUREID
inner join CCCC7.cc_Claim clm on clm.ID = expo.ClaimID
inner join CCCC7.cctl_claimstate cst on cst.ID = clm.state
inner join CCCC7.cctl_state st on st.ID = clm.JurisdictionState
where st.name='Treasury Managed Fund'
union
select rl.* from CCCC7.CC_RESERVELINE rl
inner join CCCC7.CC_Transaction txn on txt.RESERVELINEID = rl.ID
inner join CCCC7.cc_Exposure expo on expo.ID = txn.EXPOSUREID
inner join CCCC7.cc_Claim clm on clm.ID = expo.ClaimID
inner join CCCC7.cctl_claimstate cst on cst.ID = clm.state
inner join CCCC7.cctl_state st on st.ID = clm.JurisdictionState
where st.name='Treasury Managed Fund'
Although there are inconsistencies with the select statement and export dump.
Am I converting the sql to parameter file correctly?
March 04, 2025 - 1:37 pm UTC
What are the inconsistencies? Why have you changed from using JOIN to WHERE to do the joins? This makes it harder to tell if the queries are the same.
A reader, March 04, 2025 - 9:50 pm UTC
Hi Chris,
From what I've researched to convert the inner joins to an export parameter file we use the where statements.
Is there a better approach to convert inner joins into a export parameter file?
March 05, 2025 - 3:09 pm UTC
The joins are all in the subquery, why would you be unable to use JOIN syntax? Have you tried doing this?