Solved

Oracle- If else

Posted on 2011-03-14
7
470 Views
Last Modified: 2012-05-11
Am getting inavlid sql, when trying foll:
Plz advise-
if (select 1
             from custodymktval_inbound_status fs,
                  custodymktval_inbound f
            where fs.clientId=f.clientId
              and fs.cobDate='03/14/2011'
              and fs.isProcessedFlag='N' )
select
        clientId||'*'||
        clientName||'*'||
        publishtoPVFlag
  from custodymktval_inbound f
 where fileActiveInd='Y'
   and exists (select 1 from custodymktval_inbound_status fs
                           where fs.cobDate='03/14/2011'
                             and fs.clientId=f.clientId
                             and fs.isProcessedFlag='N' )
                           
   else

select
         clientId||'*'||
        clientName||'*'||
fileActiveInd||'*'||
publishtoPVFlag
  from custodymktval_inbound f
 where fileActiveInd='Y'
   and not exists (select 1 from custodymktval_inbound_status fs
                           where fs.cobDate='03/14/2011'
                             and fs.clientId=f.clientId
                             and fs.isProcessedFlag='Y' )
0
Comment
Question by:sunilbains
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35130235
You cannot use 'if' logic like this in Oracle.

What are you trying to do and with what tool/product?
0
 

Author Comment

by:sunilbains
ID: 35130437
This is a sybase sql which am trying to convert to oracle and call in unix bash script
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35130588
I'm really not understanding what that is supposed to return:

What are the valid values for fs.isProcessedFlag?

The upper does 'exists' fs.isProcessedFlag='N'

the 'else' part does: 'not exists' and fs.isProcessedFlag='Y'

I'm thinking these can be combined but I'm not understanding the logic yet.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35134665
try the below :

-- declare a variable dvar as number "dvar number;" in the declare section.
-- also each select should a variable to assign the value which you are fetching.

-- declare myfetched_var as varchar2(2000) in the declare section. change the length accordingly
-- as i am not sure about the concatenated length of the result value.

begin
select 1
into dvar
             from custodymktval_inbound_status fs,
                  custodymktval_inbound f
            where fs.clientId=f.clientId
              and fs.cobDate='03/14/2011'
              and fs.isProcessedFlag='N';
exception when no_data_found then
  dvar:=0;
end;

if (dvar = 1 ) then
select
        clientId||'*'||
        clientName||'*'||
        publishtoPVFlag
    into myfetched_var
  from custodymktval_inbound f
 where fileActiveInd='Y'
   and exists (select 1 from custodymktval_inbound_status fs
                           where fs.cobDate='03/14/2011'
                             and fs.clientId=f.clientId
                             and fs.isProcessedFlag='N' );                          
   else
select
         clientId||'*'||
        clientName||'*'||
fileActiveInd||'*'||
publishtoPVFlag
    into myfetched_var
  from custodymktval_inbound f
 where fileActiveInd='Y'
   and not exists (select 1 from custodymktval_inbound_status fs
                           where fs.cobDate='03/14/2011'
                             and fs.clientId=f.clientId
                             and fs.isProcessedFlag='Y' )
end if;
exception when no_data_found then
   myfetched_var := 'no data was fetched by the select statements';
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35134697
A possible solution below:

var out_string varchar2(200);
declare
    cursor cur is
        select 1 from custodymktval_inbound_status fs, custodymktval_inbound f
            where fs.clientId = f.clientId
              and fs.cobDate = to_date('03/14/2011', 'mm/dd/rrrr')
              and fs.isProcessedFlag = 'N';
    rec cur%rowtype;
begin
    open cur;
    fetch cur into rec;
    if SQL%FOUND then
        begin
            select clientId||'*'|| clientName||'*'|| publishtoPVFlag
                into :out_string
                from custodymktval_inbound f
                where fileActiveInd='Y'
                  and exists (select 1 from custodymktval_inbound_status fs
                                where fs.cobDate = to_date('03/14/2011', 'mm/dd/rrrr')
                                  and fs.clientId=f.clientId
                                  and fs.isProcessedFlag='N');
        exception
            when no_data_found then
                :out_string := 'No Data Found from ''if''';
        end;
    else
        begin
            select clientId||'*'|| clientName||'*'|| fileActiveInd||'*'|| publishtoPVFlag
                into :out_string
                from custodymktval_inbound f
                where fileActiveInd='Y'
                  and not exists (select 1 from custodymktval_inbound_status fs
                                    where fs.cobDate = to_date('03/14/2011', 'mm/dd/rrrr')
                                      and fs.clientId=f.clientId
                                      and fs.isProcessedFlag='Y');
        exception
            when no_data_found then
                :out_string := 'No Data Found from ''else''';
        end;
    end if;
    close cur;
end;
/
select :out_string from dual;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35134707
All,

Those appear to be decent 'literal' conversions but does the logic make sense to you?
0
 
LVL 4

Accepted Solution

by:
subratabiswas earned 500 total points
ID: 35135536
1) open cur;
   fetch cur into rec;
   if SQL%FOUND then


   Check if there is a record in the status table which has
      a) ProcessFlag = 'N'
      b) cobDate = 'Mar-14-2011'

2) if TRUE then select the concatenated string from master and detail together where

      a) activeIndicator is 'N'

      b) and exists (select 1 from custodymktval_inbound_status fs
                                where fs.cobDate = to_date('03/14/2011', 'mm/dd/rrrr')
                                  and fs.clientId=f.clientId
                                  and fs.isProcessedFlag='N');

           redundant check, we are in this branch because second check is already TRUE

3) If FALSE then return  the concatenated string from join of master and detail where

      a) activeIndicator = 'Y;
      
      b) and not exists (select 1 from custodymktval_inbound_status fs
                                    where fs.cobDate = to_date('03/14/2011', 'mm/dd/rrrr')
                                      and fs.clientId=f.clientId
                                      and fs.isProcessedFlag='Y');


           The second check looks redundant, but  it is not. Even though the check in the cursor has confirmed that there is no FS row with processedFlag = 'N', here we are confirming that there is no row with processedFlag  = 'Y' also. So this is a valid check

As slightwv pointed, this is a literal translation from the original source and inherits the flaws from the original query. For example, there if the SELECT statements without the cursor (inside the IF and ELSE) returns more than one row(s), that is not handled.

One objective of the given solution is to show the supported syntax. Whether there are bugs, or what to do in case of EXCEPTIONs, whether it is required to use TRUNC on cobDate before date comparison etc. will be additional activity that will depend on application requirements, data in the database etc.

Hopefully I did not make it more confusing.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now