9 Replies Latest reply: Feb 5, 2013 9:53 PM by 989303 RSS

    Need help in writing code for 'when' condition

    989303
      Hi Everyone,

      I am Mythri.I have a problem in running a sql query.I have a dataset with the following details.
      Product name,product status,approval date in table product_details
      I have a code as follows

      select Product_name,
      Product_status,
      approval_date,
      case
      when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
      when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
      when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
      when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
      end as DER_approval_date

      from product_details

      but i have a error in running this code saying character mismatch.Can anybody please help me with this problem.I have been searching for the solution from 2 days but didn't find any.Please help me..

      Thanks for the help in advance..
        • 1. Re: Need help in writing code for 'when' condition
          sb92075
          986300 wrote:
          Hi Everyone,

          I am Mythri.I have a problem in running a sql query.I have a dataset with the following details.
          Product name,product status,approval date in table product_details
          I have a code as follows

          select Product_name,
          Product_status,
          approval_date,
          case
          when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
          when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
          when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
          when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
          end as DER_approval_date

          from product_details

          but i have a error in running this code saying character mismatch.Can anybody please help me with this problem.I have been searching for the solution from 2 days but didn't find any.Please help me..

          Thanks for the help in advance..
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          since we don't have your tables or data, we can't run, test, debug or improve posted SQL
          • 2. Re: Need help in writing code for 'when' condition
            Etbin
            Maybe: doubt about <tt>cast(appoval_date as nvarchar2(30))</tt> too but no Database at hand
            select Product_name,
                   Product_status,
                   approval_date,
                   case when product_status = 'Cancelled' or product_status = 'Stopped' 
            /*          then approval_date = 'N/A'  */
                        then 'N/A'
                        when product_status = 'Active' 
                        then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
                        when product_status = 'Completed' 
                        then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
                        when product_status = 'Planned'
                        then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
                   end as DER_approval_date
              from product_details
            Regards

            Etbin
            • 3. Re: Need help in writing code for 'when' condition
              Frank Kulash
              Hi,

              Welcome to the forum!
              986300 wrote:
              Hi Everyone,

              I am Mythri.I have a problem in running a sql query.I have a dataset with the following details.
              Product name,product status,approval date in table product_details
              I have a code as follows

              select Product_name,
              Product_status,
              approval_date,
              case
              when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
              What are you trying to return above?
              Did you mean
              when product_status ='Cancelled' or product_status ='Stopped' then 'N/A'
              ?
              when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
              when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
              when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
              end as DER_approval_date

              from product_details

              but i have a error in running this code saying character mismatch.Can anybody please help me with this problem.I have been searching for the solution from 2 days but didn't find any.Please help me..

              Thanks for the help in advance..
              There's a problem having some branches of a CASE expression return VARCHAR2s, and others return NVARCHAR2s.

              Why do you need NVARCHAR2 at all? Can't you use CAST the date as a VARCHAR2, or, even better, use TO_CHAR?

              If you really do need NVARCHAR2, then make sure all the THEN clauses return NVARCHAR2s. For example
              ,     case 
                       when product_status = 'Cancelled' 
                         or product_status = 'Stopped'   then  CAST ('N/A' AS NVARCHAR2 (30))
                       when product_status = 'Active'    then  NVL2 ( approval_date
                                                                   , cast (appoval_date as nvarchar2(30))
                                                  , 'Null'
                                                  )
              ...
              The VARCHAR2 literals inside NVL2 don't seem to be a problem. Apparantly, when the 2nd argument to NVL2 is amn NVARCHAR2, it can implicitly convert a VARCHAR2.

               

              I hope this answers your question.
              If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
              Explain, using specific examples, how you get those results from that data.
              Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              • 4. Re: Need help in writing code for 'when' condition
                Alan3
                Problem is in your cast.
                Is there a problem with using to_char?

                select Product_name,
                Product_status,
                approval_date,
                case when product_status = 'Cancelled' or product_status = 'Stopped'
                /* then approval_date = 'N/A' */
                then 'N/A'
                when product_status = 'Active'
                then NVL2(approval_date,to_char(approval_date),'Null')
                when product_status = 'Completed'
                then NVL2(approval_date,to_char(approval_date),'Null1')
                when product_status = 'Planned'
                then NVL2(approval_date,to_char(approval_date),'Null2')
                end as DER_approval_date
                from (select 'Name' product_name,
                'Active' product_status,
                sysdate approval_date
                from dual)
                • 5. Re: Need help in writing code for 'when' condition
                  989303
                  HI,

                  SORRY AND THANKS FOR THE PROMPT REPLY..BUT, I CAN PROVIDE ONLY SAMPLE DATA.PLEASE HELP ME..

                  MY DATASET IS IN THE FOLLOWING FORM

                  Product_name Product_status     approval_date
                  NAO001     Active     10/1/2011
                  SAP112     Active     
                  MAL345     Active     
                  HSP998     Active     13/4/2000
                  DAP345     Cancelled     12/7/1997
                  SMDRR9     Planned     10/10/2013
                  KKL006     Completed     11/12/2012
                  MMS123     Stopped     10/11/1999
                  LPV568     Active     12/10/2012
                  KDC453     Active     13/4/2011
                  ASDF34     Active     
                  HAED567     Cancelled     
                  JDK890     Cancelled     
                  YOG678     Cancelled     11/11/2011
                  NJPR88     Cancelled     14/06/2000
                  MHDE44     Cancelled     
                  LKA678     Planned     
                  LPHA34     Stopped     
                  PLAV67     Planned     1/1/2013
                  NALDR3     Stopped     

                  and I want the output in this form

                  Product_name     Product_status     approval_date     der_approval_date
                  NAO001     Active     10/1/2011     10/1/2011
                  SAP112     Active          Null
                  MAL345     Active          Null
                  HSP998     Active     13/4/2000     13/4/2000
                  DAP345     Cancelled     12/7/1997     N/A
                  SMDRR9     Planned     10/10/2013     10/10/2013
                  KKL006     Completed     11/12/2012     11/12/2012
                  MMS123     Stopped     10/11/1999     N/A
                  LPV568     Active     12/10/2012     12/10/2012
                  KDC453     Active     13/4/2011     13/4/2011
                  ASDF34     Active          Null
                  HAED567     Cancelled          N/A
                  JDK890     Cancelled          N/A
                  YOG678     Cancelled     11/11/2011     N/A
                  NJPR88     Cancelled     14/06/2000     N/A
                  MHDE44     Cancelled          N/A
                  LKA678     Planned          Null2
                  LPHA34     Stopped          N/A
                  PLAV67     Planned     1/1/2013     1/1/2013
                  NALDR3     Stopped          N/A

                  I want the der_approval_date to be N/A for stopped and cancelled status of product if approval date is present or empty and for empty values of active , completed and planned status it should be 'null' and if date is present the date should come. so I had the following code

                  select Product_name,
                  Product_status,
                  approval_date,
                  case
                  when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
                  when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
                  when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
                  when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
                  end as DER_approval_date

                  from product_details
                  but i have a error in running this code saying character mismatch.Can anybody please help me with this problem.I have been searching for the solution from 2 days but didn't find any.Please help me..

                  Thanks for the help in advance..
                  • 6. Re: Need help in writing code for 'when' condition
                    Frank Kulash
                    Hi,
                    986300 wrote:
                    HI,

                    SORRY AND THANKS FOR THE PROMPT REPLY..BUT, I CAN PROVIDE ONLY SAMPLE DATA.PLEASE HELP ME..
                    Sample data would be perfect.
                    Post CREATE TABLE and INSERT statements.
                    Always do this whenever you have a problem. Always post a complete test script that people can run to re-create the problem and try their ideas.
                    MY DATASET IS IN THE FOLLOWING FORM

                    Product_name Product_status     approval_date
                    NAO001     Active     10/1/2011
                    SAP112     Active     
                    MAL345     Active     
                    HSP998     Active     13/4/2000
                    DAP345     Cancelled     12/7/1997
                    SMDRR9     Planned     10/10/2013
                    KKL006     Completed     11/12/2012
                    MMS123     Stopped     10/11/1999
                    LPV568     Active     12/10/2012
                    KDC453     Active     13/4/2011
                    ASDF34     Active     
                    HAED567     Cancelled     
                    JDK890     Cancelled     
                    YOG678     Cancelled     11/11/2011
                    NJPR88     Cancelled     14/06/2000
                    MHDE44     Cancelled     
                    LKA678     Planned     
                    LPHA34     Stopped     
                    PLAV67     Planned     1/1/2013
                    NALDR3     Stopped     

                    and I want the output in this form

                    Product_name     Product_status     approval_date     der_approval_date
                    NAO001     Active     10/1/2011     10/1/2011
                    SAP112     Active          Null
                    MAL345     Active          Null
                    HSP998     Active     13/4/2000     13/4/2000
                    DAP345     Cancelled     12/7/1997     N/A
                    SMDRR9     Planned     10/10/2013     10/10/2013
                    KKL006     Completed     11/12/2012     11/12/2012
                    MMS123     Stopped     10/11/1999     N/A
                    LPV568     Active     12/10/2012     12/10/2012
                    KDC453     Active     13/4/2011     13/4/2011
                    ASDF34     Active          Null
                    HAED567     Cancelled          N/A
                    JDK890     Cancelled          N/A
                    YOG678     Cancelled     11/11/2011     N/A
                    NJPR88     Cancelled     14/06/2000     N/A
                    MHDE44     Cancelled          N/A
                    LKA678     Planned          Null2
                    LPHA34     Stopped          N/A
                    PLAV67     Planned     1/1/2013     1/1/2013
                    NALDR3     Stopped          N/A
                    It's very hard to tell what data is supposed to be in what columns.
                    Use \
                     tags, as described in the forum FAQ {message:id=9360002}
                    
                    I want the der_approval_date to be N/A for stopped and cancelled status of product if approval date is present or empty and for empty values of active , completed and planned status it should be 'null' and if date is present the date should come. so I had the following code

                    select Product_name,
                    Product_status,
                    approval_date,
                    case
                    when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
                    when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
                    when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
                    when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
                    end as DER_approval_date

                    from product_details
                    but i have a error in running this code saying character mismatch.Can anybody please help me with this problem.
                    We're trying. You still haven't answered my question about why you're using NVARCHAR2 here.
                    I have been searching for the solution from 2 days but didn't find any.Please help me..
                    This does what you described:
                    ,     CASE
                         WHEN product_status IN ( 'Cancelled'
                                        , 'Stopped'
                                        )     THEN 'N/A'
                         WHEN product_status IN ( 'Active'
                                        , 'Completed'
                                        , 'Planned'
                                        ) THEN NVL ( TO_CHAR ( approval_date
                                                       , 'fmDD/MM/YYYY'
                                                       )
                                                  , 'Null'
                                                  )
                         END      AS der_approval_date
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    • 7. Re: Need help in writing code for 'when' condition
                      Alan3
                      Will this not work?

                      select Product_name,
                      Product_status,
                      approval_date,
                      case when product_status = 'Cancelled' or product_status = 'Stopped'
                      then 'N/A'
                      when product_status = 'Active'
                      then NVL2(approval_date,to_char(approval_date,'DS'),'Null')
                      when product_status = 'Completed'
                      then NVL2(approval_date,to_char(approval_date,'DS'),'Null1')
                      when product_status = 'Planned'
                      then NVL2(approval_date,to_char(approval_date,'DS'),'Null2')
                      end as DER_approval_date
                      from product_details
                      • 8. Re: Need help in writing code for 'when' condition
                        989303
                        Thanks a lot for the answer.The code is working.Thank you so much.While I was trying to use VARCHAR2 function it didn't work.So I used nvarchar2.I am new to sql.So trying to learn the different functions. Anywayss thanks you....
                        • 9. Re: Need help in writing code for 'when' condition
                          989303
                          Thanks a lot everyone who spent their time in understanding my question and answering me...That was really helpful..I was very vague at my question.I am very sorry for that...I will not repeat it the other time..

                          Thank you so much....