3 Replies Latest reply: Aug 24, 2013 12:17 PM by jihuyao RSS

    Query help

    Rdk

      Hi friends,

       

       

      My below query is working fine. But it is not displaying the country, which is not having value.

      How to display all the country and display the value 0 if value is not available.

      Don't use case statment because case statement is not working in reports 6i

       

      Select Decode(t.country,

                    1,

                    'INDIA',

                    2,

                    'PAKISTAN',

                    3,

                    'USA',

                    4,

                    'BRAZIL',,

                    5,

                    'INDONESIA',

                    6,

                    'SRILANKA',

                    7,

                    'CHINA',

                    8,

                    'JAPAN',

                    9,

                    'KOREA',

                    10,

                    'NEPAL',

                    'OTHERS') Country_list,

             sum(decode(substr(t.item_type, 1, 1), '1', t.quantity)) Sugar,

             sum(decode(substr(t.item_type, 1, 1), '2', t.quantity)) Dried fruits,

             sum(decode(substr(t.item_type, 1, 1), '3', t.quantity)) Fruits,

             sum(decode(substr(t.item_type, 1, 1), '4', t.quantity)) Coffee tea,

             sum(decode(substr(t.item_type, 1, 3), '7.1', t.quantity, '7.2', quantity)) Dairy Products,

             sum(quantity) - (sum(decode(substr(t.item_type, 1, 1), '1', t.quantity)) +

                            sum(decode(substr(t.item_type, 1, 1), '2', t.quantity)) +

                            sum(decode(substr(t.item_type, 1, 1), '3', t.quantity)) +

                            sum(decode(substr(t.item_type, 1, 1), '4', t.quantity)) +

                            sum(decode(substr(t.item_type, 1, 3),

                                       '7.1',

                                       t.quantity,

                                       '7.2',

                                       quantity))) Other products

        from received_mstr t

        where t.received_date between '01-jan-2013' and '31-jan-2013'

        and t.impexp_code = 1

       

       

      group by t.country

      order by t.country

       

      Regards

      Rdk

        • 1. Re: Query help
          Frank Kulash

          Hi,

           

          In a DECODE expression, you can compare NULLs just like you compare values, so if you want to display '0' when country is NULL (similar to the way to display 'INDIA' when country is 1) you can say:

           

          SELECT  DECODE ( t.country

                         , NULL , '0'

                         , 1    , 'INDIA'

                         , 2    , 'PAKISTAN'

                         ...

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.

          Simplify the problem as much as possible.   Remove all the parts that you already know how to handle, and don't affect the part you don't know.  If you can show the problem with 2 or 3 values of country, don't post 10.

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: Query help
            SomeoneElse

            > where t.received_date between '01-jan-2013' and '31-jan-2013'

             

            In addition to what Frank said, don't use dates like this.  If t.received_date really is a date type, you should use to_date functions for the literals.

             

            Like this:

             

            where t.received_date between to_date('01-jan-2013','dd-mon-yyyy') and to_date('31-jan-2013','dd-mon-yyyy')

             

            Then you'll be comparing dates with dates.

            • 3. Re: Query help
              jihuyao

              would prefer creating a lookup table/view and let outer join do the job.