Link to home
Start Free TrialLog in
Avatar of pardeshirahul
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.payment_method_code,null)) Vendor_Default_Payment_Method,
               MAX(DECODE(SUBSTR(pvs.vendor_site_code,1,3),'EMP',pm.payment_method_code,null)) EMPL,
               MAX(DECODE(pvs.vendor_site_code,'TE',pm.payment_method_code,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')
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Here is the doc link that explains sqlplus substitution variables:
http://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG437
Avatar of pardeshirahul

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.
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.