Unable to perform query using Oracle iSite

• michelle.malone

  SSC Enthusiast

  Points: 169

  More actions

  May 7, 2014 at 12:41 pm

  #286475

  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)

• Jo Pattyn

  SSC-Dedicated

  Points: 32430

  More actions

  June 15, 2014 at 12:13 pm

  #1721796

  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) ?