Home » SQL & PL/SQL » SQL & PL/SQL » PL SQL Code with variable (Oracle 10 G)
| PL SQL Code with variable [message #645920] |
Wed, 16 December 2015 06:52  |
 |
mashrima
Messages: 5
Registered: December 2015
Location: Bangalore
|
Junior Member |
|
|
Hi Team,
Hope you all doing good !!
here i am trying to execute the code with variable but getting an error. can some one let me know where is the mistake happening.
my doubt is how can i assign the variable in below AND condition with like operator. remember that variable values can be anywhere in that AND column so i need to take like operator.
------------------
DECLARE
V_DATE varchar2(255):= SELECT to_char(sysdate-1, 'DDMONYY') FROM dual
BEGIN
SELECT DISTINCT
ABA.CREATION_DATE "BATCH CREATION DATE",
HOU.NAME "OPERTAING_UNIT",
PV.VENDOR_NAME SUPPLIER,
AIA.INVOICE_NUM "INVOICE NUM",
AIA.ATTRIBUTE3,
PVS.VENDOR_SITE_CODE SITE,
AIA.INVOICE_AMOUNT "INVOICE AMOUNT",
AIA.INVOICE_DATE "INVOICE DATE",
AIA.INVOICE_TYPE_LOOKUP_CODE "TYPE",
PV.SEGMENT1 "SUPPLIER NUM",
ABA.BATCH_NAME "Batch Name",
AIA.INVOICE_CURRENCY_CODE "INVOICE CURRENCY",
APT.DESCRIPTION TERMS,
PHA.SEGMENT1 "PO NUMBER",
AIA.DESCRIPTION,
AIA.PAYMENT_METHOD_CODE,
AIA.REQUESTER_ID,
AIA.TOTAL_TAX_AMOUNT,
AIA.PAYMENT_CURRENCY_CODE "PAYMENT CURRENCY",
AIA.PAY_GROUP_LOOKUP_CODE "PAY GROUP",
AIA.TERMS_DATE,
AIA.GL_DATE,
(SELECT (0 - SUM(NVL(AMOUNT,0)))
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = AIA.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = 'AWT')"WITHHELD AMOUNT",
(SELECT (0 - SUM(NVL(AMOUNT,0)))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = AIA.INVOICE_ID
AND (LINE_TYPE_LOOKUP_CODE = 'PREPAY'
OR LINE_TYPE_LOOKUP_CODE = 'TAX'
AND PREPAY_TAX_PARENT_ID IS NOT NULL)) "PREPAID AMOUNT",
AIA.PAYMENT_CROSS_RATE_DATE "PAYMENT RATE DATE",
AIA.PAYMENT_CROSS_RATE_TYPE "PAYMENT RATE TYPE",
AIA.PAYMENT_CROSS_RATE "PAYMENT RATE",
AIA.EXCHANGE_RATE_TYPE "RATE TYPE",
AIA.EXCHANGE_DATE "EXCHANGE DATE",
AIA.EXCHANGE_RATE "EXCHANGE RATE",
DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', DECODE(AIA.EARLIEST_SETTLEMENT_DATE, '','PERMANENT', 'TEMPORARY')) "PREPAYMENT_TYPE",
AIA.EARLIEST_SETTLEMENT_DATE "SETTLEMENT DATE",
AIA.APPROVAL_READY_FLAG,
PAPF1.FULL_NAME||'.....'||PAPF2.FULL_NAME||'.....'||AIA.ATTRIBUTE4||'.....'||PAPF2.EMAIL_ADDRESS "[ ]"
FROM
APPS.AP_TERMS APT,
APPS.XXCFI_PER_ALL_PEOPLE_F_VW PAPF2,
APPS.XXCFI_PER_ALL_PEOPLE_F_VW PAPF1,
APPS.AP_SUPPLIER_SITES_ALL PVS,
APPS.AP_SUPPLIERS PV,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.AP_INVOICES_ALL AIA,
APPS.PO_DISTRIBUTIONS_ALL PDA,
APPS.PO_HEADERS_ALL PHA,
APPS.AP_BATCHES_ALL ABA ,
APPS.HR_OPERATING_UNITS HOU
WHERE APT.TERM_ID =AIA.TERMS_ID
AND PV.VENDOR_ID =AIA.VENDOR_ID
AND PVS.VENDOR_SITE_ID =AIA.VENDOR_SITE_ID
AND PAPF1.PERSON_ID(+) =AIA.REQUESTER_ID
AND PAPF1.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND PAPF2.PERSON_ID(+) =AIA.ATTRIBUTE8
AND PAPF2.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND AIA.BATCH_ID=ABA.BATCH_ID
AND AIDA.INVOICE_ID(+)=AIA.INVOICE_ID
AND AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID(+)
AND HOU.ORGANIZATION_ID = AIA.ORG_ID
AND ABA.BATCH_NAME LIKE '%V_DATE%'
ORDER BY HOU.NAME,ABA.BATCH_NAME
END
[Updated on: Wed, 16 December 2015 06:55] Report message to a moderator |
|
|
|
| Re: PL SQL Code with variable [message #645921 is a reply to message #645920] |
Wed, 16 December 2015 07:26  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
Firstly, why are you trying query DUAL instead of simple assignment statement?
declare
V_DATE varchar2(255):= to_char(sysdate-1, 'DDMONYY');
begin null; end;
/
Secondly, in PL/SQL, the query result has to be put somewhere (INTO clause of SELECT statement), otherwise the code will fail.
Is the result of the query inside that anonymous PL/SQL block supposed to be processed anyhow? If yes, how and where?
Because now (if succeeded) the result set would be just thrown away.
[Edit: added the last sentence]
[Updated on: Wed, 16 December 2015 07:28] Report message to a moderator |
|
|
|
|
|
|
|
| Re: PL SQL Code with variable [message #645925 is a reply to message #645923] |
Wed, 16 December 2015 08:12 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mashrima wrote on Wed, 16 December 2015 13:57
how can i pass the value of variable in below AND condition with LIKE operator.
By referencing the variable as though it's a variable. You can use concatenation to add the %:
mashrima wrote on Wed, 16 December 2015 13:57
I do not have access other than SELECT so creating any object to hold the select values not possible.
you can suggest me any other method too
Flyboy was talking about PL/SQL variables. If you can write PL/SQL you can create variables.
Where is the result of that query supposed to go? What program are you running it from?
|
|
|
|
| Re: PL SQL Code with variable [message #645927 is a reply to message #645925] |
Wed, 16 December 2015 08:20 |
|
mashrima
Messages: 5
Registered: December 2015
Location: Bangalore
|
Junior Member |
|
|
Hi ,
so now complete program looks like below but getting an error messsage "Error at line 1
ORA-06550: line 73, column 17:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 73, column 21:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quote"
----------------------------------------------------------------
DECLARE
V_DATE varchar2(255):= to_char(sysdate-1, 'DDMONYY');
BEGIN
SELECT DISTINCT
ABA.CREATION_DATE "BATCH CREATION DATE",
HOU.NAME "OPERTAING_UNIT",
PV.VENDOR_NAME SUPPLIER,
AIA.INVOICE_NUM "INVOICE NUM",
AIA.ATTRIBUTE3,
PVS.VENDOR_SITE_CODE SITE,
AIA.INVOICE_AMOUNT "INVOICE AMOUNT",
AIA.INVOICE_DATE "INVOICE DATE",
AIA.INVOICE_TYPE_LOOKUP_CODE "TYPE",
PV.SEGMENT1 "SUPPLIER NUM",
ABA.BATCH_NAME "Batch Name",
AIA.INVOICE_CURRENCY_CODE "INVOICE CURRENCY",
APT.DESCRIPTION TERMS,
PHA.SEGMENT1 "PO NUMBER",
AIA.DESCRIPTION,
AIA.PAYMENT_METHOD_CODE,
AIA.REQUESTER_ID,
AIA.TOTAL_TAX_AMOUNT,
AIA.PAYMENT_CURRENCY_CODE "PAYMENT CURRENCY",
AIA.PAY_GROUP_LOOKUP_CODE "PAY GROUP",
AIA.TERMS_DATE,
AIA.GL_DATE,
(SELECT (0 - SUM(NVL(AMOUNT,0)))
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = AIA.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = 'AWT')"WITHHELD AMOUNT",
(SELECT (0 - SUM(NVL(AMOUNT,0)))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = AIA.INVOICE_ID
AND (LINE_TYPE_LOOKUP_CODE = 'PREPAY'
OR LINE_TYPE_LOOKUP_CODE = 'TAX'
AND PREPAY_TAX_PARENT_ID IS NOT NULL)) "PREPAID AMOUNT",
AIA.PAYMENT_CROSS_RATE_DATE "PAYMENT RATE DATE",
AIA.PAYMENT_CROSS_RATE_TYPE "PAYMENT RATE TYPE",
AIA.PAYMENT_CROSS_RATE "PAYMENT RATE",
AIA.EXCHANGE_RATE_TYPE "RATE TYPE",
AIA.EXCHANGE_DATE "EXCHANGE DATE",
AIA.EXCHANGE_RATE "EXCHANGE RATE",
DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', DECODE(AIA.EARLIEST_SETTLEMENT_DATE, '','PERMANENT', 'TEMPORARY')) "PREPAYMENT_TYPE",
AIA.EARLIEST_SETTLEMENT_DATE "SETTLEMENT DATE",
AIA.APPROVAL_READY_FLAG,
PAPF1.FULL_NAME||'.....'||PAPF2.FULL_NAME||'.....'||AIA.ATTRIBUTE4||'.....'||PAPF2.EMAIL_ADDRESS "[ ]"
FROM
APPS.AP_TERMS APT,
APPS.XXCFI_PER_ALL_PEOPLE_F_VW PAPF2,
APPS.XXCFI_PER_ALL_PEOPLE_F_VW PAPF1,
APPS.AP_SUPPLIER_SITES_ALL PVS,
APPS.AP_SUPPLIERS PV,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.AP_INVOICES_ALL AIA,
APPS.PO_DISTRIBUTIONS_ALL PDA,
APPS.PO_HEADERS_ALL PHA,
APPS.AP_BATCHES_ALL ABA ,
APPS.HR_OPERATING_UNITS HOU
WHERE APT.TERM_ID =AIA.TERMS_ID
AND PV.VENDOR_ID =AIA.VENDOR_ID
AND PVS.VENDOR_SITE_ID =AIA.VENDOR_SITE_ID
AND PAPF1.PERSON_ID(+) =AIA.REQUESTER_ID
AND PAPF1.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND PAPF2.PERSON_ID(+) =AIA.ATTRIBUTE8
AND PAPF2.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND AIA.BATCH_ID=ABA.BATCH_ID
AND AIDA.INVOICE_ID(+)=AIA.INVOICE_ID
AND AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID(+)
AND HOU.ORGANIZATION_ID = AIA.ORG_ID
AND ABA.BATCH_NAME LIKE '%'||V_DATE||'%'
ORDER BY HOU.NAME,ABA.BATCH_NAME
END
|
|
|
|
|
|
|
|
| Re: PL SQL Code with variable [message #645932 is a reply to message #645929] |
Wed, 16 December 2015 08:26 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're missing some semi-colons and an INTO clause.
If you don't understand the INTO clause or why you need one here then you need to stop writing code and crack open a book on PL/SQL. You'll get absolutely nowhere if you don't understand a concept that basic.
|
|
|
|
| Re: PL SQL Code with variable [message #645936 is a reply to message #645923] |
Wed, 16 December 2015 08:52 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
mashrima wrote on Wed, 16 December 2015 14:57Hi ,
thanks for the reply and valuable suggestion.
how can i pass the value of variable in below AND condition with LIKE operator.
I do not have access other than SELECT so creating any object to hold the select values not possible.
you can suggest me any other method too
Hi,
it depends entirely on your client tool abilities. In sqlplus, you may use bind variables, as stated e.g. in SQL*Plus User's Guide and Reference, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html
For 11gR2, the relevant example is placed here: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1211850
By the way, even in the initial try, the variable assignment and SELECT query would have to be in the same PL/SQL block. I see no benefit over passing the value into the query itself.
|
|
|
|
| Re: PL SQL Code with variable [message #645937 is a reply to message #645931] |
Wed, 16 December 2015 09:03 |
|
mashrima
Messages: 5
Registered: December 2015
Location: Bangalore
|
Junior Member |
|
|
Hi ,
I have followed the rules and slightly went through the guideline.
here is code now and an attached is error screen.
--
DECLARE
v_date VARCHAR2(255):= To_char(SYSDATE-1, 'DDMONYY');
BEGIN
SELECT DISTINCT aba.creation_date "BATCH CREATION DATE",
hou.name "OPERTAING_UNIT",
pv.vendor_name supplier,
aia.invoice_num "INVOICE NUM",
aia.attribute3,
pvs.vendor_site_code site,
aia.invoice_amount "INVOICE AMOUNT",
aia.invoice_date "INVOICE DATE",
aia.invoice_type_lookup_code "TYPE",
pv.segment1 "SUPPLIER NUM",
aba.batch_name "Batch Name",
aia.invoice_currency_code "INVOICE CURRENCY",
apt.description terms,
pha.segment1 "PO NUMBER",
aia.description,
aia.payment_method_code,
aia.requester_id,
aia.total_tax_amount,
aia.payment_currency_code "PAYMENT CURRENCY",
aia.pay_group_lookup_code "PAY GROUP",
aia.terms_date,
aia .gl_date,
(
SELECT (0 - SUM(nvl(amount,0)))
FROM ap_invoice_distributions_all
WHERE invoice_id = aia.invoice_id
AND line_type_lookup_code = 'AWT')"WITHHELD AMOUNT",
(
SELECT (0 - SUM(nvl(amount,0)))
FROM apps.ap_invoice_distributions_all
WHERE invoice_id = aia.invoice_id
AND (
line_type_lookup_code = 'PREPAY'
OR line_type_lookup_code = 'TAX'
AND prepay_tax_parent_id IS NOT NULL)) "PREPAID AMOUNT",
aia.payment_cross_rate_date "PAYMENT RATE DATE",
aia.payment_cross_rate_type "PAYMENT RATE TYPE",
aia.payment_cross_rate "PAYMENT RATE",
aia.exchange_rate_type "RATE TYPE",
aia.exchange_date "EXCHANGE DATE",
aia.exchange_rate "EXCHANGE RATE",
decode(aia.invoice_type_lookup_code,
'PREPAYMENT', decode(aia.earliest_settlement_date,
'','PERMANENT',
'TEMPORARY')) "PREPAYMENT_TYPE",
aia.earliest_settlement_date "SETTLEMENT DATE",
aia.approval_ready_flag,
papf1.full_name
||'.....'
||papf2.full_name
||'.....'
||aia.attribute4
||'.....'
||papf2.email_address "[ ]"
FROM apps.ap_terms apt,
apps.xxcfi_per_all_people_f_vw papf2,
apps.xxcfi_per_all_people_f_vw papf1,
apps.ap_supplier_sites_all pvs,
apps.ap_suppliers pv,
apps.ap_invoice_distributions_all aida,
apps.ap_invoices_all aia,
apps.po_distributions_all pda,
apps.po_headers_all pha,
apps.ap_batches_all aba ,
apps.hr_operating_units hou
WHERE apt.term_id =aia.terms_id
AND pv.vendor_id =aia.vendor_id
AND pvs.vendor_site_id =aia.vendor_site_id
AND papf1.person_id(+) =aia.requester_id
AND papf1.effective_end_date(+) > trunc(SYSDATE)
AND papf2.person_id(+) =aia.attribute8
AND papf2.effective_end_date(+) > trunc(SYSDATE)
AND aia.batch_id=aba.batch_id
AND aida.invoice_id(+)=aia.invoice_id
AND aida.po_distribution_id=pda.po_distribution_id(+)
AND pda.po_header_id=pha.po_header_id(+)
AND hou.organization_id = aia.org_id
AND aba.batch_name LIKE '%'
||v_date
||'%'
ORDER BY hou.name,
aba.batch_name
END;
--
-
Attachment: Error.PNG
(Size: 5.91KB, Downloaded 985 times)
|
|
|
|
|
|
|
|
| Re: PL SQL Code with variable [message #645941 is a reply to message #645937] |
Wed, 16 December 2015 09:26 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Besides the fact that SELECT statement requires the INTO clause inside PL/SQL block (as you were told twice) I have the same question again:
As the assignment and query are (and have to be) in the same PL/SQL block, do you see any benefit over accompanying it into the query itself?
SELECT DISTINCT ...
FROM ...
WHERE ...
AND ba.batch_name LIKE '%'||to_char(SYSDATE-1, 'DDMONYY')||'%'
ORDER BY ...; |
|
|
|
Goto Forum:
Current Time: Fri Jun 26 20:04:36 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
