Problem with a nested decode statement

[rogerzebra]

Hi All.

I can't get this nested decode statement to work. I'm not so familiar with Oracle, so please bear with me. I have tried to use a case statement aswell but, I obviously need help with this. So, if someone could give me some directions on how to do this with either a case statement or a decode would much appreciated.
Thanks in advance

This is what I'm trying to do (case when column1 <> '02' and column2 in ('a','b','c') then count polices in column2) end as test

Here is the real query

select decode(column1,'a','b','c',decode(Column,'02','N'))
,count(1)

from (select columns...
       ,sum(column3)
    from table1 a, table b
    where a...= b.
          and b... = ('Program')
          and a.column1('a','b','c')
          and b...date between to_date ('04/01/2006','mm/dd/yyyy') 
          and to_date('12/31/2006','mm/dd/yyyy')  
     group by a.pol
            ,a...
            ,a...
            ,b...           )
group by ..

 

[SantaMufasa]

First, Roger, your textual explanation of what you want seems to contradict your coded example, so please forgive me if my code, below, does not match the spirit of your code...I used your text explanation to create the code. Also, "column2" contains an 'a','b, or 'c', yet your text says that you want to "count polices in column2"; are 'a','b, or 'c' policies?

So, to focus on the syntax of your textual request:

select case column1 <> '02'
        and column2 in ('a','b','c')
            then count(Column2)
        end test
  from (select ...)
 group by column1, column2

Although the above contains the syntax to pass the code interpreter, I don't believe that your logic does quite what you want.

Let us know if this helps to resolve your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

Hey Santa, who else would come to resque

Sorry for my sloppy explanation. It should read

select case column1 <> '02'
       and column2 in ('a','b','c')
       then Column2
        end test
,Count(1)      --this will count all case statement above
  from (select ...
From table1,Table2,Table3
Where ..)
 group by column1, column2

I can't get this to work with either a case statement or decode. How would a correct nested decode look like?
Thanks again Santa

 

[SantaMufasa]

Ahhhh so...Now I understand. Here is the code to make that happen:

select
    count(case column1 <> '02'
           and column2 in ('a','b','c')
          then 1
           end) test --this counts all '1's above
  from (select ...
From table1,Table2,Table3
Where ..)
 group by column1, column2

Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

hey Santa, I start to believe that it is another problem I have, as soon I'm trying to use more than one condition in my case statement it says "invalid Indentifier". What a is this? Do you know what's going on?

 

[SantaMufasa]

Please post a screen copy and paste of the code and the error message, and I'll be glad to troubleshoot it for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

Thanks but, before I do that, it seems that I actually have a problem with the numeric '02' perhaps a conversion to a character string would do the job. Excuse my lazy question. How do you that in Oracle.

 

[SantaMufasa]

it seems that I actually have a problem with the numeric '02'

First, '02' is, in fact, not numeric...it is character...2 is numeric...'2' and '02' are both character strings with very different character values. They do, however, have the same numeric values.


I cannot discern from your previous posts what datatype "column1" is. If Column1 is already numeric, then Oracle automagically performs a character-to-number conversion of '02', making its value simply 2.

And continuing, if Column1 is already numeric, then you can/should be using simply the numeral 2 to compare...no quotes.

In direct response to your questions about explicit Oracle data conversions, here are functions that go either way:

select to_char(2,'0009')x, to_number('000000002')y from dual;

X              Y
----- ----------
 0002          2

1 row selected.

Let us know if the above answers your questions.

I'm also happy to look at your code and its accompanying error message(s), but I must soon leave to run an errand...so please hurry to receive from me a prompt response.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

hmm getting tired, the column is a varchar. Here is the orignial code, there are actually several case statement.

 select     

     count(case when trans_action <> '02' 
                 trans_code in ('a','b','c')
                 then 1
                 end) test
                    
                    
                              
     from (select Polnum
       ,b.program_code
       ,a.edition
       ,a.TRANS_CODE
 from table a, table b, table c
    where a.polnum= b.polnum
          and a.edition = b.edition
          and a.KEY = b.KEY
          and a.Polnum = c.Polnum
          and a.edition = c.edition
          and a.Key = c.Key
          and b.program_code in ('116')
          and a.Trans_Code in ('a','b','c')
          and b.date between to_date ('04/01/2006','mm/dd/yyyy') 
          and to_date('12/31/2006','mm/dd/yyyy')  
     group by a.polnum 
            ,a.edition
            ,a.TRANS_CODE
            ,b.program_code
           )
group by program_code,trans_code

 

[SantaMufasa]

Roger,

Error listing, copied directly from the screen and pasted here, please.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Whoa...Roger...I do not see "trans_action" appearing as a column or column alias within the scope of your SELECT statement. What column is "trans_action" supposed to be named?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

I'm sorry Santa but, I coulnd't find a way to send the print screen, and earlier post perhaps missing some crucial data. The reason is that this data columns is not supposed to be exposed to the public. But, what the h*%^$
Here it is. ERROR MESSAGE A73_Trans_Reason: Invalid Identifier.
That's about it. Thanks again for helping me out, I have a deadline on this thing.

select   count(case when A73_Trans_Reason <> '02' 
                 and d32_trans_code in ('CF','CS','CP')
                 then 1
                 end) test
                    
    from (select a.a00_pnum as Policynum
       ,b.g78_program_code
       ,a.a06_edition as edition
       ,a.D32_TRANS_CODE
    from zjprep.precap_history a, zjprep.pcommon b, zjprep.ptrans c
    where a.A00_PNUM = b.A00_PNUM
          and a.a06_edition = b.a06_edition
          and a.T91_POL_KEY = b.T91_POL_KEY
          and a.A00_PNUM = c.A00_PNUM
          and a.a06_edition = c.a06_edition
          and a.T91_POL_KEY = c.T91_POL_KEY
          and b.g78_program_code in ('116')
          and a.d32_TRANS_CODE in ('CF','CP','CS','RN','N')
          and b.a08_fdate between to_date ('04/01/2006','mm/dd/yyyy') 
          and to_date('12/31/2006','mm/dd/yyyy')  
     group by a.a00_pnum 
            ,a.a06_edition
            ,a.D32_TRANS_CODE
            ,b.g78_program_code
           )
group by g78_program_code,d32_trans_code

 

[SantaMufasa]

Roger,

Your problem relates to the issue I posted in my previous e-mail:

select   count(case when [b]A73_Trans_Reason[/b] <> '02'
...

Notice that nowhere in your "FROM" clause does A73_Trans_Reason appear. Which table do you expect hold that column? Oracle threw that error because it has no clue where A73_Trans_Reason is supposed to be coming from either.

Let us know...I'll hold off on leaving for my errand(s) until I hear back from you (if you are quick) .

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogerzebra]

Thanks Santa,

I was so tired on Thursday so that I even missed to put the column in the from clause. I'm good now though, so thanks so much for your help.
take care
/r