Help with IF THEN ELSE. Be kind I'm new..

[Gustavo]

Looking for help creating an "IF THEN ELSE". I would like to pass my .SQL a variable from SQLPlus using the command line and detemine if the variable has actually been populated (It may or May not be). Something as follows.

Delare
dpart varchar()
begin
dpart:=&1
if dpart is null then
Select Name from mytable1
else
Select Sub_Name from mytable2
end if;
end;

Any help would be appreciated.

 

[KenCunningham]

Are you thinking of the prompt command to ask the user to supply the variable?

 

[Gustavo]

Hi Ken thanks for the response. No what I do is in a DOS command and depending what the DOS command does it will feed the .SQL &1 &2 &3 and &4 may or not be populated with anything.

 

[SantaMufasa]

Gustavo,

Here is "code-cleanup" of your script:

Declare
 dpart varchar(50);
 name_hold mytable1.name%type;
begin
 dpart:='&1';
if dpart is null then
 Select Name into name_hold from mytable1 where <some condition>;
else
 Select Sub_Name into name_hold from mytable2 where <some condition>;;
end if;
end;
/

Let us know if this resolves your need/question.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:52 (06Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:52 (06Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Gustavo]

I think I'getting further. Now I get a
ORA-01422: exact fetch returns more than requested number of rows.
It appears that I will need to get a little bit fancier. I'm just sure of the syntax.

 

[SantaMufasa]

Gustavo,

The error you are receiving is why I included the phrase, "WHERE <some condition>;" PL/SQL can deal with two types of SELECTs: either 1) a "single-row" SELECT that must return exactly one row, or 2) a zero-, one-, or many-row SELECT which uses either an explicit or an implicit CURSOR.

Now, according to your needs, do you expect a single row to return from your SELECT, or not? Depending upon your need, we can engineer a solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:48 (07Feb05) UTC (aka "GMT" and "Zulu"),
@ 11:48 (07Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Gustavo]

I will need to return more then on row. Yeah sorry about that I am trying to figure this whole SQL thing. It appears that my "if statement" will be come more complicated now that I need to return more then one row.

 

[SantaMufasa]

Actually, Gustavo,

If you do not wish to change your logic, you can implement the following WHERE clause that forces only one row maximum to return from your WHERE clause:

if dpart is null then
Select Name into name_hold from mytable1
where <some condition> AND rownum = 1;

This limits the result set to one row. I cannot see how your code will improve by using a multi-row CURSOR just for the sake of avoiding this error, "ORA-01422: exact fetch returns more than requested number of rows."

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:57 (07Feb05) UTC (aka "GMT" and "Zulu"),
@ 12:57 (07Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Gustavo]

A little closer. I believe your code worked. Now it appears it does not like when I am give an &1 from the command line. I receive the following

Enter value for 1:

Is there a SET that I can do to get around this.

 

[SantaMufasa]

Gustavo,

The fact that SQL*Plus is saying, "Enter value for 1:", tells us that all of your "SETs" are properly working.

I just tested your exact script on my machine, and it worked successfully, both the "not null" and the "null" logic forks, so your problem may be with invocation. Could you please post a copy-and-paste of your exact invocation of the script?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:14 (07Feb05) UTC (aka "GMT" and "Zulu"),
@ 14:14 (07Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Gustavo]

This how I am invoking it. from my .bat file the TESTING may or may not be there when the .SQL is being invoked.
sqlplus DimRptSelect/************@DBA @c:\qafileshare\FindCDs_TEST.sql TESTING

I can't thank enough for the help.

 

[SantaMufasa]

Gustavo,

This is puzzling. Here is my code, which I patterned from your code. The only difference is that I added commands (dbms...) to display output to my screen during execution. I saved the code to a file identically named to yours, "C:\qafileshare\FindCDs_TEST.sql":

set serveroutput on format wrap
Declare
 dpart varchar(50);
 name_hold mytable1.name%type;
begin
 dpart:='&1';
if dpart is null then
 Select Name into name_hold from mytable1 where rownum=1;
 dbms_output.put_line(name_hold);
else
 Select Sub_Name into name_hold from mytable2 where rownum=1;
 dbms_output.put_line(name_hold);
end if;
end;
/

Following is my successful test of your code, using identical invocations except for username and password:

D:\> sqlplus test/test @c:\qafileshare\FindCDs_TEST.sql TESTING

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 7 15:10:52 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

old   5:  dpart:='&1';
new   5:  dpart:='TESTING';
Name from MyTable2

PL/SQL procedure successfully completed.

SQL>

Plus, here is my invocation of your code with a NULL entry in place of "TESTING", to show the other logic for:

D:\> sqlplus test/test @c:\qafileshare\FindCDs_TEST.sql ''

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 7 15:22:51 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

old   5:  dpart:='&1';
new   5:  dpart:='';
Name from MyTable1

PL/SQL procedure successfully completed.

SQL> exit

So, my invocations were successful...your invocation was not. How do our two invocation sets differ as far as you can tell?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:25 (07Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:25 (07Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Gustavo]

OK, I believe I see what your doing your passing with ''. I wasn't even placing that on the command line. Currently I have server .BAT files that will call this .SQL. I was hoping I could just modify the .SQL and not effect the .BAT's to add ''. Any way aroudn this?

 

[SantaMufasa]

Gustavo,

Although there may be a way, I don't know what it would be. Is changing the .bat files out of the question? How do the .bat file currently obtain their "not null" values?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:44 (07Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:44 (07Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.