Home » SQL & PL/SQL » SQL & PL/SQL » Displaying the entered parameter
Displaying the entered parameter [message #301504] Wed, 20 February 2008 21:22 Go to next message
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 #301514 is a reply to message #301504] Wed, 20 February 2008 23:11 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

please check u query,
p_address is parameter,so cannot select..
Re: Displaying the entered parameter [message #301523 is a reply to message #301504] Wed, 20 February 2008 23:39 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: complex count
Next Topic: RAW Datatype
Goto Forum:
  


Current Time: Thu Dec 08 10:38:23 CST 2016

Total time taken to generate the page: 0.26022 seconds