[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle- If else

Posted on 2011-03-14
7
Medium Priority
?
508 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
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.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 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