?
Solved

Oracle- If else

Posted on 2011-03-14
7
Medium Priority
?
502 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

762 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