Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Passing parameter (oracle 9i)
Problem: Passing parameter [message #330820] Tue, 01 July 2008 07:24 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have written a PL/SQL for the below criterion.Want to know if it can be done using SQL.Suggesion please!

Criteria is that

if p_cbo_value is "Last Name" and p_txt_val='Somevalue' then it would show the result based on USR.LAST_NAME column

when p_cbo_value is "First Name" and p_txt_val='Somevalue' then it would show the result based on USR.FIRST_NAME column

when p_cbo_value is "User Id" and p_txt_val='Somevalue' then it would show the result based on USR.USER_CD column

when p_cbo_value is "Role Id" and p_txt_val='Somevalue' then it would show the result based on ROLE_CD column

when p_cbo_value is "Updated by" and p_txt_val='Somevalue' then it would show the result based on USR.UPD_ID column
CREATE OR REPLACE PROCEDURE PROC_GET
( p_cbo_val IN VARCHAR2,
  p_txt_val   IN VARCHAR2,
  RC  OUT gPkg.RCT)

AS			  

BEGIN
   
	OPEN RC FOR 
		SELECT ROWNUM AS SL, USR.USER_CD AS "User Id",USR.LAST_NAME AS "Last Name",USR.FIRST_NAME "First Name",LOC_ID AS "Location Id",
		AP.APP_DESC AS Application,RT.ROLE_DESC AS Role, USR.UPD_ID AS "Modified By"
		FROM USER_TBL USR,USR_ROLE UR,APP_ROLE AR,ROLE_TBL RT,APPLICATION AP
		WHERE USR.USER_CD=UR.USER_CD
		AND UR.ROLE_CD=AR.ROLE_CD
		AND AR.ROLE_CD=RT.ROLE_CD
		AND AR.APP_ID = AP.APP_ID
		AND (APP.APP_ID='01')
		AND 
		/* Based on selection criterion */
		( 
		( p_cbo_val ='01'  OR(USR.LAST_NAME  ='p_txt_val' OR USR.LAST_NAME LIKE '%p_txt_val%'))
		OR ( p_cbo_val='02' OR (USR.FIRST_NAME ='p_txt_val' OR USR.FIRST_NAME LIKE '%p_txt_val%'))
		OR(p_cbo_val='03' OR (USR.USER_CD ='p_txt_val' OR USR.USER_CD LIKE '%p_txt_val%'))
		OR ( p_cbo_val='04' OR (RT.ROLE_CD ='p_txt_val' OR RT.ROLE_CD LIKE '%p_txt_val%'))
		OR (p_cbo_val='05' OR (RT.ROLE_DESC ='p_txt_val' OR RT.ROLE_DESC LIKE '%p_txt_val%'))
		OR (p_cbo_val='06' OR (USR.UPD_ID ='p_txt_val' OR USR.UPD_ID LIKE '%p_txt_val%'))
		)
		ORDER BY USR.USER_CD;
EXCEPTION 
	WHEN OTHERS THEN 
	NULL;

END PROC_GET; 
/

Regards,
Oli

[Updated on: Tue, 01 July 2008 07:46] by Moderator

Report message to a moderator

Re: Problem: Passing parameter [message #330824 is a reply to message #330820] Tue, 01 July 2008 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you wrote in words and in PL/SQL don't match and have no relation.
Correct that.

Regards
Michel
Re: Problem: Passing parameter [message #330827 is a reply to message #330820] Tue, 01 July 2008 07:52 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Olivia wrote on Tue, 01 July 2008 08:24

OR(p_cbo_val='03' OR (USR.USER_CD ='p_txt_val' OR USR.USER_CD LIKE '%p_txt_val%'))



I have to admit, I didn't really read the question too closely, but just looking at the code, I would say this is incorrect. Items in single quotes are literals.
Re: Problem: Passing parameter [message #330828 is a reply to message #330824] Tue, 01 July 2008 07:53 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for pointing out the error Michel.

Corrected!


I have written a PL/SQL for the below criterion.Want to know if it can be done using SQL.Need your suggesion.

Criteria is that

if p_cbo_val is '01' (i.e, Last name) and p_txt_val='Somevalue' then it would show the result based on USR.LAST_NAME column

when p_cbo_val is '02' (First Name) and p_txt_val='Somevalue' then it would show the result based on USR.FIRST_NAME column

when p_cbo_val is '03' (User Id) and p_txt_val='Somevalue' then it would show the result based on USR.USER_CD column

when p_cbo_val is '04' (Role Id) and p_txt_val='Somevalue' then it would show the result based on ROLE_CD column

when p_cbo_val is '05' (Updated by) and p_txt_val='Somevalue' then it would show the result based on USR.UPD_ID column
.....
.....

CREATE OR REPLACE PROCEDURE PROC_GET
( p_cbo_val IN VARCHAR2,
  p_txt_val   IN VARCHAR2,
  RC  OUT gPkg.RCT)

AS			  

BEGIN
   
	OPEN RC FOR 
		SELECT ROWNUM AS SL, USR.USER_CD AS "User Id",USR.LAST_NAME AS "Last Name",USR.FIRST_NAME "First Name",LOC_ID AS "Location Id",
		AP.APP_DESC AS Application,RT.ROLE_DESC AS Role, USR.UPD_ID AS "Modified By"
		FROM USER_TBL USR,USR_ROLE UR,APP_ROLE AR,ROLE_TBL RT,APPLICATION AP
		WHERE USR.USER_CD=UR.USER_CD
		AND UR.ROLE_CD=AR.ROLE_CD
		AND AR.ROLE_CD=RT.ROLE_CD
		AND AR.APP_ID = AP.APP_ID
		AND (APP.APP_ID='01')
		AND 
		/* Based on selection criterion */
		( 
		( p_cbo_val ='01'  OR(USR.LAST_NAME  =p_txt_val OR USR.LAST_NAME LIKE '%p_txt_val%'))
		OR ( p_cbo_val='02' OR (USR.FIRST_NAME ='p_txt_val' OR USR.FIRST_NAME LIKE '%p_txt_val%'))
		OR(p_cbo_val='03' OR (USR.USER_CD =p_txt_val OR USR.USER_CD LIKE '%p_txt_val%'))
		OR ( p_cbo_val='04' OR (RT.ROLE_CD =p_txt_val OR RT.ROLE_CD LIKE '%p_txt_val%'))
		OR (p_cbo_val='05' OR (RT.ROLE_DESC =p_txt_val OR RT.ROLE_DESC LIKE '%p_txt_val%'))
		OR (p_cbo_val='06' OR (USR.UPD_ID =p_txt_val OR USR.UPD_ID LIKE '%p_txt_val%'))
		)
		ORDER BY USR.USER_CD;
EXCEPTION 
	WHEN OTHERS THEN 
	NULL;

END PROC_GET; 
/

Regards,
Oli

[Updated on: Tue, 01 July 2008 07:58]

Report message to a moderator

Re: Problem: Passing parameter [message #330829 is a reply to message #330820] Tue, 01 July 2008 07:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Olivia wrote on Tue, 01 July 2008 14:24
EXCEPTION 
	WHEN OTHERS THEN 
	NULL;



Remove that. It will hide each and every error from you, leaving you to wonder why your procedure does not do what you expect it to
Re: Problem: Passing parameter [message #330831 is a reply to message #330828] Tue, 01 July 2008 07:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You still need to address @Joy_Divisions point, and change
( p_cbo_val ='01'  OR(USR.LAST_NAME  ='p_txt_val' OR USR.LAST_NAME LIKE '%p_txt_val%'))
		OR ( p_cbo_val='02' OR (USR.FIRST_NAME ='p_txt_val' OR USR.FIRST_NAME LIKE '%p_txt_val%'))
		OR(p_cbo_val='03' OR (USR.USER_CD ='p_txt_val' OR USR.USER_CD LIKE '%p_txt_val%'))
		OR ( p_cbo_val='04' OR (RT.ROLE_CD ='p_txt_val' OR RT.ROLE_CD LIKE '%p_txt_val%'))
		OR (p_cbo_val='05' OR (RT.ROLE_DESC ='p_txt_val' OR RT.ROLE_DESC LIKE '%p_txt_val%'))
		OR (p_cbo_val='06' OR (USR.UPD_ID ='p_txt_val' OR USR.UPD_ID LIKE '%p_txt_val%'))
to
( p_cbo_val ='01'  OR(USR.LAST_NAME  =p_txt_val OR USR.LAST_NAME LIKE '%'||p_txt_val||'%'))
		OR ( p_cbo_val='02' OR (USR.FIRST_NAME =p_txt_val OR USR.FIRST_NAME LIKE '%'||p_txt_val||'%'))
		OR(p_cbo_val='03' OR (USR.USER_CD =p_txt_val OR USR.USER_CD LIKE '%'||p_txt_val||'%'))
		OR ( p_cbo_val='04' OR (RT.ROLE_CD =p_txt_val OR RT.ROLE_CD LIKE '%'||p_txt_val||'%'))
		OR (p_cbo_val='05' OR (RT.ROLE_DESC =p_txt_val OR RT.ROLE_DESC LIKE '%'||p_txt_val||'%'))
		OR (p_cbo_val='06' OR (USR.UPD_ID = p_txt_val OR USR.UPD_ID LIKE '%'||p_txt_val||'%'))
Re: Problem: Passing parameter [message #330832 is a reply to message #330828] Tue, 01 July 2008 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
( p_cbo_val ='01'  OR(USR.LAST_NAME  ='p_txt_val' OR USR.LAST_NAME LIKE '%p_txt_val%'))
		OR ( p_cbo_val='02' OR (USR.FIRST_NAME ='p_txt_val' OR USR.FIRST_NAME LIKE '%p_txt_val%'))
		OR(p_cbo_val='03' OR (USR.USER_CD ='p_txt_val' OR USR.USER_CD LIKE '%p_txt_val%'))
		OR ( p_cbo_val='04' OR (RT.ROLE_CD ='p_txt_val' OR RT.ROLE_CD LIKE '%p_txt_val%'))
		OR (p_cbo_val='05' OR (RT.ROLE_DESC ='p_txt_val' OR RT.ROLE_DESC LIKE '%p_txt_val%'))
		OR (p_cbo_val='06' OR (USR.UPD_ID ='p_txt_val' OR USR.UPD_ID LIKE '%p_txt_val%'))

If you only have OR I don't see how you make a choice.
I think there you should be some AND:
( p_cbo_val ='01' AND (USR.LAST_NAME ='p_txt_val' OR USR.LAST_NAME LIKE '%p_txt_val%'))
OR ( p_cbo_val='02' AND (USR.FIRST_NAME ='p_txt_val' OR USR.FIRST_NAME LIKE '%p_txt_val%'))
OR (p_cbo_val='03' AND (USR.USER_CD ='p_txt_val' OR USR.USER_CD LIKE '%p_txt_val%'))
OR ( p_cbo_val='04' AND (RT.ROLE_CD ='p_txt_val' OR RT.ROLE_CD LIKE '%p_txt_val%'))
OR (p_cbo_val='05' AND (RT.ROLE_DESC ='p_txt_val' OR RT.ROLE_DESC LIKE '%p_txt_val%'))
OR (p_cbo_val='06' AND (USR.UPD_ID ='p_txt_val' OR USR.UPD_ID LIKE '%p_txt_val%'))


Of course I assume that p_txt_val is a literal not a variable.

Regards
Michel
Re: Problem: Passing parameter [message #330835 is a reply to message #330832] Tue, 01 July 2008 08:11 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Not getting records after modifying to "AND". Here p_cbo_val is a parameter only.


And also p_txt_val is a parameter.We are passing value..



eg, get the list of records based on LAST NAME/FIRST NAME/USER ID
(p_cbo_val).If last name p_cbo_val='01' something like that.
And also p_txt_val is equal to/like some value say 'MI'

[Updated on: Tue, 01 July 2008 08:14]

Report message to a moderator

Re: Problem: Passing parameter [message #330848 is a reply to message #330835] Tue, 01 July 2008 08:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

p_cbo_val IN VARCHAR2



Modified to

p_cbo_val IN NUMBER


And below code has been modified as suggested by Jrowbottom,Michel and Joy...

Thanks to you people. Can it be done with SQL only?


....

         ( p_cbo_val =1  OR(USR.LAST_NAME  =p_txt_val AND USR.LAST_NAME LIKE '%'||p_txt_val||'%'))
		OR ( p_cbo_val=2 AND (USR.FIRST_NAME =p_txt_val OR USR.FIRST_NAME LIKE '%'||p_txt_val||'%'))
		OR(p_cbo_val=3 AND (USR.USER_CD =p_txt_val OR USR.USER_CD LIKE '%'||p_txt_val||'%'))
		OR ( p_cbo_val=4 AND (RT.ROLE_CD =p_txt_val OR RT.ROLE_CD LIKE '%'||p_txt_val||'%'))
		OR (p_cbo_val= 5 AND (RT.ROLE_DESC =p_txt_val OR RT.ROLE_DESC LIKE '%'||p_txt_val||'%'))
		OR (p_cbo_val=6 AND(USR.UPD_ID = p_txt_val OR USR.UPD_ID LIKE '%'||p_txt_val||'%'))

...

[Updated on: Tue, 01 July 2008 08:30]

Report message to a moderator

Re: Problem: Passing parameter [message #330850 is a reply to message #330848] Tue, 01 July 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT is SQL.

Regards
Michel
Re: Problem: Passing parameter [message #330851 is a reply to message #330850] Tue, 01 July 2008 08:37 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Razz No, I meant if it can be done using SQL only ( without PL/SQL)



Thanks,
Oli
Re: Problem: Passing parameter [message #330860 is a reply to message #330820] Tue, 01 July 2008 08:53 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> if it can be done using SQL only ( without PL/SQL)
What exactly do you mean as "it"? You showed just PL/SQL procedure returning cursor based on SQL statement. Did you try to run the standalone SELECT statement (that would be pure SQL)?
If so, what is the problem? As you did not post the expected procedure usage, it is hard to tell more.
Previous Topic: Decode to display result of the columns in one row
Next Topic: Sequential Processing of Entire Table
Goto Forum:
  


Current Time: Thu Dec 08 00:15:53 CST 2016

Total time taken to generate the page: 0.10118 seconds