Solved

Oracle- If else

Posted on 2011-03-14
7
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

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 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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