6 Replies Latest reply: Sep 20, 2013 7:29 AM by marcusafs RSS

    error in case when

    Rajnish Chauhan

      hi friends

       

          i have below query which is throwing error  'missing expression' which is on bold.

          please correct the query.

       

         SELECT max(lv.lv_lastupdate)  INTO v_old_date FROM loanable_value lv

         WHERE lv.lv_rm_cust_id= p_gfcid AND lv.lv_year= p_year

         AND  (CASE  WHEN     p_Quarter ='Q1'  THEN    lv.lv_month IN ('Jan','Feb','Mar')  END ) ;

        • 1. Re: error in case when
          SomeoneElse

          You can't use case that way.  A case expression returns a value, not a portion of a where clause.

          • 2. Re: error in case when
            SomeoneElse

            Maybe something like this instead?  (month/year in separate columns?)

             

            SELECT  max(lv.lv_lastupdate) 

            INTO    v_old_date

            FROM    loanable_value lv

            WHERE   lv.lv_rm_cust_id = p_gfcid

            AND     lv.lv_year = p_year

            AND    (

                       (p_Quarter = 'Q1' and lv.lv_month IN ('Jan','Feb','Mar'))

                    or (p_Quarter = 'Q2' and lv.lv_month IN ('Apr','May','Jun'))

                    or (p_Quarter = 'Q3' and lv.lv_month IN ('Jul','Aug','Sep'))

                    or (p_Quarter = 'Q4' and lv.lv_month IN ('Oct','Nov','Dec'))

                   )

            ;

            • 3. Re: error in case when
              Paul  Horth

              RajnishChauhan wrote:

               

              hi friends

               

                  i have below query which is throwing error  'missing expression' which is on bold.

                  please correct the query.

               

                 SELECT max(lv.lv_lastupdate)  INTO v_old_date FROM loanable_value lv

                 WHERE lv.lv_rm_cust_id= p_gfcid AND lv.lv_year= p_year

                 AND  (CASE  WHEN     p_Quarter ='Q1'  THEN    lv.lv_month IN ('Jan','Feb','Mar')  END ) ;

              How do you expect us to correct it if we don't know what you want?

               

              However, I'm guessing you want

               

              and (

              (p_quarter = 'Q1' and lv.lv_month in ('Jan','Feb','Mar'))

              or (p_quarter = 'Q2' and lv.lv_month in ('Apr','May','Jul'))

              or..

              )

               

              difficulties you could have avoided if you'd not split things up as lv_year,lv_month etc. and just kept the field as a single DATE.

              Then it would be a simple as

               

              where to_char(lv.lv_date,'Q')=p_quarter

               

              where p_quarter is 1,2,3 or 4

              • 4. Re: error in case when
                Frank Kulash

                Hi,

                 

                I think Paul assumes that p_quarter can't be NULL.  If it can be NULL, you may want something like this:

                 

                AND  (    NVL (p_Quarter, 'OK')  <> 'Q1'

                     OR   lv.lv_month            IN ('Jan', 'Feb', 'Mar')

                     )

                As Paul said, information about dates belongs in DATE columns.  Using VARCHAR2 columns for date information leads to convoluted, inefficient, error-prone code, as well as invalid data.

                • 5. Re: error in case when
                  Paul  Horth

                  FrankKulash wrote:

                   

                  Hi,

                   

                  I think Paul assumes that p_quarter can't be NULL. 

                  That was checked for in preceding code

                  • 6. Re: error in case when
                    marcusafs

                    You need to have expression = something in a where clause.  If you want to return the latest update for p_Quarter this case will work.  If this isn't what you are looking for please tell us what you are trying to do.

                     

                    SELECT max(lv.lv_lastupdate)  INTO v_old_date FROM loanable_value lv 
                       WHERE lv.lv_rm_cust_id= p_gfcid AND lv.lv_year= p_year 
                       AND  (CASE  WHEN   lv.lv_month IN ('Jan','Feb','Mar' )'  THEN   'Q1  END ) = p_Quarter;