pardeshirahul
asked on
how a use & as a value in thye select statement
I have a following select statement
SELECT pv.vendor_id,
pv.segment1 vendor_num,
pv.vendor_name,
pv.pay_group_lookup_code,
MAX(DECODE(pvs.vendor_site _code,null ,pm.paymen t_method_c ode,null)) Vendor_Default_Payment_Met hod,
MAX(DECODE(SUBSTR(pvs.vend or_site_co de,1,3),'E MP',pm.pay ment_metho d_code,nul l)) EMPL,
MAX(DECODE(pvs.vendor_site _code,'TE' ,pm.paymen t_method_c ode,null)) TE
FROM po_vendors pv,
iby_external_payees_all epa,
iby_ext_party_pmt_mthds pm,
po_vendor_sites_all pvs
WHERE pv.party_id = epa.payee_party_id
AND epa.ext_payee_id = pm.ext_pmt_party_id
AND pm.primary_flag = 'Y'
AND pm.payment_flow = 'DISBURSEMENTS'
AND pvs.vendor_site_id (+)= epa.supplier_site_id
--AND pv.segment1 = '56745'
and vendor_type_lookup_code = 'EMPL'
and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
GROUP BY pv.vendor_id,
pv.segment1 ,
pv.vendor_name,
pv.pay_group_lookup_code
how to use the the value &
and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
SELECT pv.vendor_id,
pv.segment1 vendor_num,
pv.vendor_name,
pv.pay_group_lookup_code,
MAX(DECODE(pvs.vendor_site
MAX(DECODE(SUBSTR(pvs.vend
MAX(DECODE(pvs.vendor_site
FROM po_vendors pv,
iby_external_payees_all epa,
iby_ext_party_pmt_mthds pm,
po_vendor_sites_all pvs
WHERE pv.party_id = epa.payee_party_id
AND epa.ext_payee_id = pm.ext_pmt_party_id
AND pm.primary_flag = 'Y'
AND pm.payment_flow = 'DISBURSEMENTS'
AND pvs.vendor_site_id (+)= epa.supplier_site_id
--AND pv.segment1 = '56745'
and vendor_type_lookup_code = 'EMPL'
and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
GROUP BY pv.vendor_id,
pv.segment1 ,
pv.vendor_name,
pv.pay_group_lookup_code
how to use the the value &
and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know what this document is saying but my question is & is a value and not a lexical parameter oracle sql is implying it as a lexical parameter
The doc link was just a reference to help explain with it is doing.
The first post answers your question: set define off
sqlplus uses the DEFINE character as the trigger for a substitution variable. It defaults to '&'. To use '&' as part of a query, either set DEFINE to something else or turn it off.
The first post answers your question: set define off
sqlplus uses the DEFINE character as the trigger for a substitution variable. It defaults to '&'. To use '&' as part of a query, either set DEFINE to something else or turn it off.
ASKER
if I am using TOAD then
I'm not a Toad person but I believe it will recognize the SET operator.
If not, then do as I suggested in the first post:
If you aren't using sqlplus, look in the help for it to unset the DEFINE character.
If not, then do as I suggested in the first post:
If you aren't using sqlplus, look in the help for it to unset the DEFINE character.
Quick Google showed this:
http://www.toadworld.com/products/toad-for-oracle/f/10/t/9430
http://www.toadworld.com/products/toad-for-oracle/f/10/t/9430
http://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG437