[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Oracle- If else

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
sunilbains
Asked:
sunilbains
1 Solution
 
slightwv (䄆 Netminder) Commented:
You cannot use 'if' logic like this in Oracle.

What are you trying to do and with what tool/product?
0
 
sunilbainsAuthor Commented:
This is a sybase sql which am trying to convert to oracle and call in unix bash script
0
 
slightwv (䄆 Netminder) Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
subratabiswasCommented:
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
 
slightwv (䄆 Netminder) Commented:
All,

Those appear to be decent 'literal' conversions but does the logic make sense to you?
0
 
subratabiswasCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now