Unable to perform query using Oracle iSite

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

  • 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