| Displaying the entered parameter [message #301504] |
Wed, 20 February 2008 21:22  |
lovenelza
Messages: 8
Registered: March 2007
|
Junior Member |
|
|
Hi. I'm trying to display a parameter to the screen. I have the following SQL code
Select a.recvd_by_name
, c.ref_policy_no
, TO_CHAR(TO_DATE(b.check_amount, 'J'),'JSP') amtinwords
, TO_CHAR(b.check_amount, '9,999,999.99') maturity_benefit
, p_address
from tbl_matclaims a
, tbl_matclaims_check_claims b
, tbl_policy c
where a.matclaim_id=b.matclaim_id
and a.policy_id=c.policy_id
and a.matclaim_id= 425
and p_address = '&p' -- Address does not have a column in any of the tables because we are not keeping the record of it.
This SQL has the following input: matclaim id = 425, Address = Makati City, Philippines
Output:
Recevd_by_name Ref_policy_no amtinwords Maturity Benefit
John Dela Cruz 00001 One Hundred Pesos 100.00
P_address
Makati City, Philippines
Thank you very much.
Nelza
|
|
|
|
|
|
| Re: Displaying the entered parameter [message #301523 is a reply to message #301504] |
Wed, 20 February 2008 23:39  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> I'm trying to display a parameter to the screen. I have the following SQL code
> Select <...>, p_address
Good, what is wrong with displayed p_address (if this the mentioned parameter) then?
> and p_address = '&p' -- Address does not have a column in any of the tables because we are not keeping the record of it.
You are comparing parameter with substitution variable. As both values do not depend on table row values, the condition is always true or always false. The only effect of this is, that in second case (non-equality) the query returns no rows. Do you really want this?
|
|
|
|
| Re: Displaying the entered parameter [message #301527 is a reply to message #301504] |
Wed, 20 February 2008 23:54 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If this is a stand-alone SQL-query (so, not within a pl/sql-block), p_address will not be known.
If so, you'll want to select &p instead of p_address, and remove the where-clause regarding p_address = &p
|
|
|
|
| Re: Displaying the entered parameter [message #301536 is a reply to message #301527] |
Thu, 21 February 2008 00:16 |
lovenelza
Messages: 8
Registered: March 2007
|
Junior Member |
|
|
| Frank wrote on Thu, 21 February 2008 13:54 | If this is a stand-alone SQL-query (so, not within a pl/sql-block), p_address will not be known.
If so, you'll want to select &p instead of p_address, and remove the where-clause regarding p_address = &p
|
This is a stand-alone SQL-query. I tried it and I got what I want.
Select a.recvd_by_name
, c.ref_policy_no
, TO_CHAR(TO_DATE(b.check_amount, 'J'),'JSP') amtinwords
, TO_CHAR(b.check_amount, '9,999,999.99') maturity_benefit
, '&p_address' Address
from tbl_matclaims a
, tbl_matclaims_check_claims b
, tbl_policy c
where a.matclaim_id=b.matclaim_id
and a.policy_id=c.policy_id
and a.matclaim_id= 425
Output:
RECVD_BY_NAME REF_POLICY_NO AMTINWORDS MATURITY_BENEFIT ADDRESS
JOHN DELA CRUZ 000738 ONE HUNDRED FIFTY THOUSAND 150,000.00 15th Floor CyberOne Bldg., CyberPark, Eastwood City, Libis, Quezon City
Thank you vey much for all of your help. i'm learning a lot from this forum.
Nelza
|
|
|
|
| Re: Displaying the entered parameter [message #301539 is a reply to message #301536] |
Thu, 21 February 2008 00:33 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
