Prev: Optimizer issue in 11g
Next: New dbTrends Software Product for Oracle AWR and STATSPACK Released
From: gazzag on 4 Feb 2010 07:03
On 4 Feb, 11:12, raja <dextersu...(a)gmail.com> wrote:
> I have tried an example.
>
<snip>
>
> Please help...

If you want people to help, why not do as Mark D Powell said in
response to your OP?

1. Post the DDL to create the table.
2. Post the DML to populate the table with some test data.

You're asking us to do a lot of work on your behalf otherwise.

HTH
-g
From: raja on 4 Feb 2010 07:32
Thanks for your response.
Sorry missed the ddl and dml.

DDL and DML are as follows :

CREATE TABLE TRANSACTION
(
CURRENCY_FUNC_COD NUMBER(12) NOT NULL,
DATA_TYPE_COD NUMBER(12) NOT NULL,
GL_POST_ACCN_COD NUMBER(12) NOT NULL,
PRDMKT_COD NUMBER(12) NOT NULL,
SCENARIO_COD NUMBER(12) NOT NULL,
ACCN_PERIOD_COD NUMBER(12) NOT NULL,
TRANS_DES_VC VARCHAR2(300 BYTE),
CURRENCY_TRAN_COD NUMBER(12) NOT NULL,
REP_ENT_COD NUMBER(12) NOT NULL,
COST_CENTER_COD NUMBER(12) NOT NULL,
ACCN_PERIOD_COD_1 NUMBER(12) NOT NULL,
ACCN_YEAR_COD_VC VARCHAR2(100 BYTE) NOT NULL,
CURR_ACCN_PERIOD_START_DT DATE,
FUNCT_AMNT_FAA_NB NUMBER
);


Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
7.47);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
43.53);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
47.79);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1.58);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
-43.53);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
7.41);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
42.32);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2.44);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
13.91);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
8.36);
COMMIT;
From: Mark D Powell on 4 Feb 2010 09:31
On Feb 4, 7:32 am, raja <dextersu...(a)gmail.com> wrote:
> Thanks for your response.
> Sorry missed the ddl and dml.
>
> DDL and DML are as follows :
>
> CREATE TABLE TRANSACTION
> (
>   CURRENCY_FUNC_COD    NUMBER(12)         NOT NULL,
>   DATA_TYPE_COD        NUMBER(12)         NOT NULL,
>   GL_POST_ACCN_COD     NUMBER(12)         NOT NULL,
>   PRDMKT_COD           NUMBER(12)         NOT NULL,
>   SCENARIO_COD         NUMBER(12)         NOT NULL,
>   ACCN_PERIOD_COD      NUMBER(12)         NOT NULL,
>   TRANS_DES_VC         VARCHAR2(300 BYTE),
>   CURRENCY_TRAN_COD    NUMBER(12)         NOT NULL,
>   REP_ENT_COD          NUMBER(12)         NOT NULL,
>   COST_CENTER_COD      NUMBER(12)         NOT NULL,
>   ACCN_PERIOD_COD_1    NUMBER(12)         NOT NULL,
>   ACCN_YEAR_COD_VC     VARCHAR2(100 BYTE) NOT NULL,
>   CURR_ACCN_PERIOD_START_DT  DATE,
>   FUNCT_AMNT_FAA_NB    NUMBER
> );
>
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 7.47);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 0.51);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 0.51);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 43.53);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 47.79);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 0.51);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 1.58);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> -43.53);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 7.41);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 42.32);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 2.44);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 13.91);
> Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
>    (CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
> SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
> REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
> CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
>  Values
>    (525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
> '2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> 8.36);
> COMMIT;

You realize that all the insert statements are all wrong. You failed
to match the insert table to the sample table_name.
Easy fix. I do not know how much if any time I will get today but I
will try to give this a shot for you. Someone will probably beat me
to a solution.

HTH -- Mark D Powell --

From: raja on 4 Feb 2010 12:08
Thanks for your immediate reply.

I tried to do summation for month-wise ( using over clause - tried
example query, is present in the above posts )
But, i dont know whether that is correct or not !!!

Now, i have to try another summation of the same type ; to get year-
wise summation, with the data acquired from month wise summation,
So, i want to rewrite a single query, so that, i get double summation
for a column grouped by all the other columns.

Can that be done !!!! :-)

Hope you understand my need.

Thanks.
From: Mark D Powell on 5 Feb 2010 16:43
On Feb 4, 12:08 pm, raja <dextersu...(a)gmail.com> wrote:
> Thanks for your immediate reply.
>
> I tried to do summation for month-wise ( using over clause - tried
> example query, is present in the above posts )
> But, i dont know whether that is correct or not !!!
>
> Now, i have to try another summation of the same type ; to get year-
> wise summation, with the data acquired from month wise summation,
> So, i want to rewrite a single query, so that, i get double summation
> for a column grouped by all the other columns.
>
> Can that be done !!!! :-)
>
> Hope you understand my need.
>
> Thanks.

I got busy and have not been able to return to this but I will post
what I have in case it might be of help. You have more columns in the
transaction table that in your intiail post and you do not identify
them. Normally when you summ a value, say sales, for a date, MTD, and
YTD you do it by product or general ledger account. I cannot tell
from your post what you actually want and your sample data is all the
same acct, date, etc so I made a couple of changes to have more than
one data and more than one gl acct.

SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT),
2 sum(FUNCT_AMNT_FAA_NB) DAY
3 from transaction
4 group by GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT)
5 /

GL_POST_ACCN_COD TRUNC(CUR DAY
---------------- --------- ----------
474093 27-FEB-06 7.47
474094 20-FEB-06 7.41
474094 25-FEB-06 .51
474094 27-FEB-06 17.15
474094 27-MAR-06 42.83
474094 27-FEB-07 13.91
474094 27-FEB-08 43.53

7 rows selected.

SQL>
SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'MM'),
2 sum(FUNCT_AMNT_FAA_NB) MTD
3 from transaction
4 group by GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'MM')
5 /

GL_POST_ACCN_COD TRUNC(CUR MTD
---------------- --------- ----------
474093 01-FEB-06 7.47
474094 01-FEB-06 25.07
474094 01-MAR-06 42.83
474094 01-FEB-07 13.91
474094 01-FEB-08 43.53

SQL>
SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'YYYY'),
2 sum(FUNCT_AMNT_FAA_NB) YTD
3 from transaction
4 group by GL_POST_ACCN_COD,
trunc(CURR_ACCN_PERIOD_START_DT,'YYYY')
5 /

GL_POST_ACCN_COD TRUNC(CUR YTD
---------------- --------- ----------
474093 01-JAN-06 7.47
474094 01-JAN-06 67.9
474094 01-JAN-07 13.91
474094 01-JAN-08 43.53


If you placed each of these queries in the FROM clause as an inline
view and joined the results together on the gl_post_accn_cod column
you would have day, MTD, and YTD figures.

Like I said I changed the data but the idea should be clear since no
one else has posted a more eligant solution I went ahead and posted
this.

HTH -- Mark D Powell --




First  |  Prev  | 
Pages: 1 2
Prev: Optimizer issue in 11g
Next: New dbTrends Software Product for Oracle AWR and STATSPACK Released