Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to perform query using Oracle iSite Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 12:29 PM
Points: 3, Visits: 3
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)
Post #1568655
Posted Sunday, June 15, 2014 12:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 1,399, Visits: 6,781
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) ?
Post #1580942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse