CODEWITHSUNDEEP
sqldelphidelphi-7interbase
Shane
Shane

Reputation: 1397

Interbase SQL statement not working

I am trying to write a SQL Statement for Interbase.

Whats wrong with this SQL?

md_master (trm) = Master Table cd_Med (cdt) = Detail table

SELECT trm.seq_no, trm.recipient_id, trm.payee_fullname, trm.payee_address1, trm.payee_address2, trm.payee_address3, trm.payee_address_city, trm.payee_address_state, trm.recip_zip, trm.recip_zip_4, trm.recip_zip_4_2, trm.check_no, trm.check_date, trm.check_amount,
cdt.com_ss_source_sys, cdt.cd_pay_date, cdt.com_set_amount,
bnk.name, bnk.address, bnk.transit_routing,
act.acct_no
FROM md_master trm, cd_med cdt, accounts act, banks bnk
join cd_med on cdt.master_id = trm.id
join accounts on act.acct_id = trm.account_tag
join banks on bnk.bank_id = act.bank_id
ORDER BY cdt.master_id

I don't get an error, the computer just keeps crunching away and hangs.

Upvotes: 0

Views: 430

Answers (3)

Iridio
Iridio

Reputation: 9271

The error lie in the from clause. You are using half with comma separated tables without a relation in where clause and half with joins.

Just use joins and all should work fine

Upvotes: 0

Johan
Johan

Reputation: 76537

You have been bitten by an anti-pattern called implicit join syntax

SELECT * FROM table_with_a_1000rows, othertable_with_a_1000rows

Will do a cross-join on both tables selecting 1 million rows in the output.

You are doing:

FROM md_master trm, cd_med cdt, accounts act, banks bnk

A cross join on 4 tables (combined with normal joins afterwards), which could easily generate many billions of rows.
No wonder interbase hangs; it is working until the end of time to generate more rows then there are atoms in the universe.

The solution
Never use , after the FROM clause, that is an implicit join and it is evil.
Only use explicit joins, like so:

SELECT 
  trm.seq_no, trm.recipient_id, trm.payee_fullname, trm.payee_address1
  , trm.payee_address2, trm.payee_address3, trm.payee_address_city
  , trm.payee_address_state, trm.recip_zip, trm.recip_zip_4, trm.recip_zip_4_2
  , trm.check_no, trm.check_date, trm.check_amount
  , cdt.com_ss_source_sys, cdt.cd_pay_date, cdt.com_set_amount
  , bnk.name, bnk.address, bnk.transit_routing
  , act.acct_no
FROM md_master trm
join cd_med on cdt.master_id = trm.id
join accounts on act.acct_id = trm.account_tag
join banks on bnk.bank_id = act.bank_id
ORDER BY cdt.master_id

Upvotes: 1

John Rix
John Rix

Reputation: 6673

I don't know about Interbase specifically, but that FROM clause seems a little strange (perhaps just some syntax I'm not familiar with though). Does this help?

...
FROM md_master trm
join cd_med cdt on cdt.master_id = trm.id
join accounts act on act.acct_id = trm.account_tag
join banks bnk on bnk.bank_id = act.bank_id

By the way, you have no WHERE clause so if any of these tables is large, I wouldn't be overly surprised that it takes a long time to run.

Upvotes: 7

Related Questions