May 7, 2014 at 12:41 pm
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
from ADTRAN.ad_vat_recon
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)
June 15, 2014 at 12:13 pm
Case sensitivity? (collaction/Oracle case sensitivity)
Mix of datatypes? trim(x)=:comp -> characters? :comp=0 -> number?
Why is the trim added in the last line?
You have supplier_country = receiver_country and :ccty = 0 (without trim)
and then suddenly trim(supplier_country) = :ccty and trim(receiver_country) = :ccty
-> supplier_country = receiver_country and supplier_country=trim(:ccty) ?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply