The user can put in the following parameters and I'm trying to create an sql statement that would work for the parameters specified.
The :comp is company number; user can enter one company or 'all' which is 0
:year, :fprd, tprd should be self explanatory and
:ccty is for country; the supplier country and receiver company must be the same no matter what so the user can select a certain country or the can select 'all' which is 0
When I run the statement for 'all' on country then I get the records but if I select a country, the query gets an error. What am I doing wrong with this sql statement????
I am having problems with the following sql statement:
select ledger_account, supplier_country, receiver_country, supplier_number, supplier_name, supplier_invoice_number, purchase_order_number, tran_type_po, document_number_po, invoice_amount,
currency, invoice_amount_hc, vat_account_po, vat_amount_po, tran_type_app, document_number_app, vat_account_app, vat_amount_app
where (finance_company = :comp or :comp=0)
and fiscal_year = :year
and fiscal_period between :fprd and :tprd
and (supplier_country = receiver_country and :ccty = 0 or
trim(supplier_country) = :ccty and trim(receiver_country) = :ccty)