missing tables returned by sql_metadata.get_query_tables (2)

[janvda]

This might be a duplicate of #112

my DB2 SQL query:

select ca.IDENTIFICATION_CODE identificationCode, 
eo.KBO_NUMBER kboNumber, 
eo.PARTY_NAME,
ca.total_guaranteed totale_borgtocht, 
coalesce(sum(ae1.remainder),0) Saldo, 
coalesce(sum(ae3.remainder),0) uitstel_van_betaling, 
coalesce(sum(ae4.remainder),0) reservering_aangifte, 
coalesce(sum(ae5.remainder),0) reservering_vergunning,
coalesce(sum(ae6.remainder),0) zekerheid_douanevervoer, 
coalesce(sum(ae7.remainder),0) zekerheid_accijnsbeweging,
coalesce(sum(ae8.remainder),0) FRCT 
from CUSTOMER_ACCOUNT ca 
inner join economic_operator eo on eo.id = ca.economic_operator_id 
join contact_details cd on cd.id = ca.contact_details_id 
left join ( ca1_remainder_total_guaranteed crtg 
inner join accounting_entity ae1 on ae1.id = crtg.accounting_entity_id)
on crtg.id = ca.ca1_id 
left join (ca3_credit_account cca inner join accounting_entity ae3 on ae3.id = 
cca.accounting_entity_id) on cca.id = ca.ca3_id 
left join (ca4_reservations_declaration crd inner join accounting_entity ae4 on 
ae4.id = crd.accounting_entity_id) on crd.id = ca.ca4_id 
left join (ca5_reservations_permits crp inner join accounting_entity ae5 on ae5.id 
= crp.accounting_entity_id) on crp.id = ca.ca5_id 
left join (CA6_GUARANTEE_CUSTOMS_TRANSPORT gct inner join accounting_entity ae6 on 
ae6.id = gct.accounting_entity_id) on gct.id = ca.ca6_id 
left join (CA7_GUARANTEE_EXCISE_PRODUCTS gep inner join accounting_entity ae7 on 
ae7.id = gep.accounting_entity_id) on gep.id = ca.ca7_id 
left join (ca8_frct cf inner join ca8_frct_per_discharge cfpd on cfpd.CA8_ID = 
cf.id inner join accounting_entity ae8 on ae8.id = cfpd.accounting_entity_id) on 
cf.id = ca.ca8_id 
group by eo.PARTY_NAME,eo.KBO_NUMBER, ca.IDENTIFICATION_CODE, ca.total_guaranteed 
order by eo.KBO_NUMBER, ca.IDENTIFICATION_CODE 
with ur

As preprocessing step I am replacing the new lines with spaces. So this is the output after the preprocessing step:

select ca.IDENTIFICATION_CODE identificationCode,  eo.KBO_NUMBER kboNumber,  eo.PARTY_NAME, ca.total_guaranteed totale_borgtocht,  coalesce(sum(ae1.remainder),0) Saldo,  coalesce(sum(ae3.remainder),0) uitstel_van_betaling,  coalesce(sum(ae4.remainder),0) reservering_aangifte,  coalesce(sum(ae5.remainder),0) reservering_vergunning, coalesce(sum(ae6.remainder),0) zekerheid_douanevervoer,  coalesce(sum(ae7.remainder),0) zekerheid_accijnsbeweging, coalesce(sum(ae8.remainder),0) FRCT  from CUSTOMER_ACCOUNT ca  inner join economic_operator eo on eo.id = ca.economic_operator_id  join contact_details cd on cd.id = ca.contact_details_id  left join ( ca1_remainder_total_guaranteed crtg  inner join accounting_entity ae1 on ae1.id = crtg.accounting_entity_id) on crtg.id = ca.ca1_id  left join (ca3_credit_account cca inner join accounting_entity ae3 on ae3.id =  cca.accounting_entity_id) on cca.id = ca.ca3_id  left join (ca4_reservations_declaration crd inner join accounting_entity ae4 on  ae4.id = crd.accounting_entity_id) on crd.id = ca.ca4_id  left join (ca5_reservations_permits crp inner join accounting_entity ae5 on ae5.id  = crp.accounting_entity_id) on crp.id = ca.ca5_id  left join (CA6_GUARANTEE_CUSTOMS_TRANSPORT gct inner join accounting_entity ae6 on  ae6.id = gct.accounting_entity_id) on gct.id = ca.ca6_id  left join (CA7_GUARANTEE_EXCISE_PRODUCTS gep inner join accounting_entity ae7 on  ae7.id = gep.accounting_entity_id) on gep.id = ca.ca7_id  left join (ca8_frct cf inner join ca8_frct_per_discharge cfpd on cfpd.CA8_ID =  cf.id inner join accounting_entity ae8 on ae8.id = cfpd.accounting_entity_id) on  cf.id = ca.ca8_id  group by eo.PARTY_NAME,eo.KBO_NUMBER, ca.IDENTIFICATION_CODE, ca.total_guaranteed  order by eo.KBO_NUMBER, ca.IDENTIFICATION_CODE  with ur  

If I then apply method sql_metadata.get_query_tables to it then I am getting:

['CUSTOMER_ACCOUNT', 'economic_operator', 'contact_details', 'accounting_entity', 'ca8_frct_per_discharge']

So it is missing several tables :

• ca1_remainder_total_guaranteed
• ca3_credit_account
• ca4_reservations_declaration
• ca5_reservations_permits
• CA7_GUARANTEE_EXCISE_PRODUCTS
• ca8_frct

FYI these are the acutal python commands I have used:

with open('Requêtes Métier SQL/01. FRCT.sql','r') as file :
  data = file.read().replace('\n', ' ')
  sql = sql_metadata.get_query_tables(data)
sql

[macbre]

Now this one is a complex query 😉 Thanks for reporting an issue. Let's see how #112 improves the situation here.

[macbre]

To be fixed by #121