5 Replies Latest reply: Feb 13, 2013 2:21 AM by IvanBlanarik RSS

    how to satisfy GROUP BY

    894936
      first view  :B2BFEB11_TEST
      second view : B2BFEB11_TEST2
      
      can any one tell me how do i make these two views into one. in B2BFEB11_TEST i can take send_date as timestamp but should be included in group by.
      My condition is i should use send_date as timestamp but i should not use them in group by cluase.
      
      My group by CLAUSE should consists of only below mentioned fields.
                  A.STO_NO,
                  A.CUR_CODE,
                  A.RECEIPT_NO,
                  A.CASH_NO,
                  A.SALES_DATE
      
      can you help me how to implement this timestamp in my view .... because i need to use this timestamp column  in another table.
      CREATE OR REPLACE FORCE VIEW B2BFEB11_TEST
      (
      SEND_DATE,
      BU_CODE,
      BU_TYPE,
      CUST_NO,
      CUR_CODE,
      SALES_DATE,
      RECEIPT_NO,
      TILL_NO,
      CARD_NO,
      INVOICE_TOTAL,
      AMOUNT_OF_GOODS,
      AMOUNT_NON_GOODS,
      AMOUNT_ADVANCE_PAY,
      AMOUNT_DISCOUNTS,
      ON_HOLD_FLAG,
      IDRS_NAME,
      RUN_DATE,
      FIX_DATE,
      RUN_DATE_PREV
      )
      AS
      SELECT DISTINCT
      NULL AS TIME_STAMP,
      CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
      CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
      CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
      CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
      TO_DATE (A.SALES_DATE, 'YYMMDD'),
      CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
      CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
      CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
      CASE
      WHEN A.cur_code IN ('JPY', 'HUF')
      THEN
      CAST (SUM (sold_amount) AS NUMBER (11, 2)) * 1
      ELSE
      CAST (SUM (sold_amount) AS NUMBER (11, 2)) / 100
      END
      AS invoice_total,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
      'N' AS ON_HOLD_FLAG,
      'F6581001' AS IDRS_NAME,
      NULL AS RUN_DATE,
      NULL AS FIX_DATE,
      NULL AS RUN_DATE_PREV
      FROM I_0470002_LOG_T A
      WHERE A.SALES_DATE IN
      ('121115',
      '121116',
      '121117',
      '121118',
      '121119',
      '121120',
      '130130',
      '130131')
      AND A.SOLD_AMOUNT != 0
      GROUP BY A.STO_NO,
      A.CUR_CODE,
      A.RECEIPT_NO,
      A.CASH_NO,
      A.SALES_DATE

      CREATE OR REPLACE FORCE VIEW B2BFEB11_TEST2
      (
      SEND_DATE,
      BU_CODE,
      BU_TYPE,
      CUST_NO,
      CUR_CODE,
      SALES_DATE,
      RECEIPT_NO,
      TILL_NO,
      CARD_NO,
      INVOICE_TOTAL,
      AMOUNT_OF_GOODS,
      AMOUNT_NON_GOODS,
      AMOUNT_ADVANCE_PAY,
      AMOUNT_DISCOUNTS,
      ON_HOLD_FLAG,
      IDRS_NAME,
      RUN_DATE,
      FIX_DATE,
      RUN_DATE_PREV)
      AS

      SELECT DISTINCT
      timestamp AS TIME_STAMP, ---------------------------------------------------------i want timestamp as one column
      CAST (null AS VARCHAR2 (5 CHAR)) AS BU_CODE,
      CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
      CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
      CAST (null AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
      null as sales_date,
      CAST (null AS VARCHAR2 (10 BYTE)),
      CAST (null AS VARCHAR2 (5 BYTE)) AS TILL_NO,
      CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
      null as invoice_total,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
      'N' AS ON_HOLD_FLAG,
      'F6581001' AS IDRS_NAME,
      SYSDATE AS RUN_DATE,
      SYSDATE AS FIX_DATE,
      SYSDATE AS RUN_DATE_PREV
      FROM I_0470002_LOG_T A

      Edited by: 891933 on 12-Feb-2013 22:18

      Edited by: 891933 on 12-Feb-2013 22:43

      Edited by: 891933 on 12-Feb-2013 22:52
        • 1. Re: how to satisfy GROUP BY
          894936
          Hi could you please help me out.
          • 2. Re: how to satisfy GROUP BY
            IvanBlanarik
            Hi,
            could you please tell me what exactly you want to do?

            1. you don't want to group by SEND_DATE, right?
            2. you want to use SEND_DATE instead of TIME_STAMP?
            3. if there are more send_date for one combination of sto_no, cur_code, receipt_no, cash_no... you want to return only one row with min(send_date), max(send_date), or you want to return multiple rows for every distinct send_date?
            • 3. Re: how to satisfy GROUP BY
              Purvesh K
              IMV, applying a MIN/MAX on timestamp should do the Job. Because Timestamp is evaluated once for a select statement, so MIN/MAX of timestamp should be equal to its actual value.
              • 4. Re: how to satisfy GROUP BY
                894936
                Hi,
                
                1:Yes i should not use send_date in my group by clause.
                2: I should use timestamp clumn which is aviable in my Master table "i_047...._log_t".
                3: yes for all these combination i should return the min(send_date).
                
                Now i am using the two views views like this in the below script. But i want to reduce my code. by implementing into one view which shd satisfy by group by condition.
                If i use send_date in my group by i will get wrong values. which does not satify the req.
                
                please let me know if you need any other information. Thanks in advance.
                is it possible by using like inner query?
                Edited by: 891933 on 13-Feb-2013 01:27
                • 5. Re: how to satisfy GROUP BY
                  IvanBlanarik
                  I think that the min(time_stamp) will be sufficient:
                  SELECT MIN(time_stamp)               AS TIME_STAMP,
                    CAST(A.STO_NO AS   VARCHAR2(5 CHAR)) AS BU_CODE,
                    CAST('STO' AS      VARCHAR2(3 CHAR)) AS BU_TYPE,
                    CAST(NULL AS       VARCHAR2(7 BYTE)) AS CUST_NO,
                    CAST(A.CUR_CODE AS VARCHAR2(3 BYTE)) AS CUR_CODE,
                    TO_DATE(A.SALES_DATE, 'YYMMDD'),
                    CAST(A.RECEIPT_NO AS VARCHAR2(10 BYTE)),
                    CAST(A.CASH_NO AS    VARCHAR2(5 BYTE)) AS TILL_NO,
                    CAST(NULL AS         VARCHAR2(2 BYTE)) AS CARD_NO,
                    CASE
                      WHEN A.cur_code IN('JPY', 'HUF')
                      THEN CAST(SUM(sold_amount) AS NUMBER(11, 2)) * 1
                      ELSE CAST(SUM(sold_amount) AS NUMBER(11, 2)) / 100
                    END                         AS invoice_total,
                    CAST(NULL AS NUMBER(11, 2)) AS AMOUNT_ADVANCE_PAY,
                    CAST(NULL AS NUMBER(11, 2)) AS AMOUNT_OF_GOODS,
                    CAST(NULL AS NUMBER(11, 2)) AS AMOUNT_NON_GOODS,
                    CAST(NULL AS NUMBER(11, 2)) AS AMOUNT_DISCOUNTS,
                    'N'                         AS ON_HOLD_FLAG,
                    'F6581001'                  AS IDRS_NAME,
                    NULL                        AS RUN_DATE,
                    NULL                        AS FIX_DATE,
                    NULL                        AS RUN_DATE_PREV
                  FROM I_0470002_LOG_T A
                  WHERE A.SALES_DATE IN('121115', '121116', '121117', '121118', '121119', '121120', '130130', '130131')
                  AND A.SOLD_AMOUNT != 0
                  GROUP BY A.STO_NO,
                    A.CUR_CODE,
                    A.RECEIPT_NO,
                    A.CASH_NO