Home » SQL & PL/SQL » SQL & PL/SQL » A count query problem.
A count query problem. [message #292275] Tue, 08 January 2008 06:47 Go to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi all

I am running into some problems with a query i am writing.
I have two views that are the same but 1 has historical data.
In these two view's i have the following column's defined.

MESSAGE_ID VARCHAR2 (16)
BUSINESS_UNIT_NM VARCHAR2 (50)
COMMON_REFERENCE_NB VARCHAR2 (35)
VALUE_DT VARCHAR2 ( 8 ) ( has a date in it )
TRADE_DT VARCHAR2 ( 8 ) ( has a date in it )

The code i have is the following.
select  ndf_1.bu			bus
	, times.tim			date1
    	, count ( ndf_1.BU ) countvd
 FROM
 	 (select   distinct common_reference_NB     CR
		   , To_date (trade_dt,'YYYYMMDD')  TD
	           , To_date (value_dt,'YYYYMMDD')  VD
	   	   , business_unit_nm      BU
	    from TMS_VIEW_FX
	    where       
	         cls_product_nm = 'NDF' and
	   	 business_unit_nm <> 'Control Branch'
	   
	   union all

	  select   distinct common_reference_NB     CR
      		   , To_date (trade_dt,'YYYYMMDD')  TD
	 	   , To_date (value_dt,'YYYYMMDD')  VD
	 	   , business_unit_nm      BU
	     from TMS_VIEW_FX_HIS
	     where
       	          cls_product_nm = 'NDF' and
   	   	  business_unit_nm <> 'Control Branch'
	) NDF_1 ,
	(select 
		(to_date(20080101,'YYYYMMDD'))+level tim 
   	  from 
	  	dual 
	  connect by level < 5
	) times
Where
   ndf_1.vd(+) = times.tim 
   group by  ( times.tim , NDF_1.bu ) 
   order by  times.tim 
;


This gives the following output.

BUS	         DATE1	     COUNTVD

BEARUS33XXX	1/2/2008	5
ABNANL2AXXX	1/3/2008	8
BEARUS33XXX	1/3/2008	15
ABNANL2AXXX	1/4/2008	7
BEARUS33XXX	1/4/2008	10
	        1/5/2008	0



I now have two problems that i need to adres.

1 I am now counting every record where a VALUE_DT is the same as my calender ( times.tim ) i also need a count for the TRAD_DT. In the same selection.

2 There is no bus record on 1/5/2008. I need to have a record for every BUS. The problem with this is that i do not know how many BUS there are. ( from this table )

I have tried to solve problem 1 with creating the same select query as above but then the outer join on de TRADE_DT. Then make both of the query's a input for a other query that selected all the data out of the two. This became just a jungle of join's and it did not give me the data i wanted.

For problem 2 i was think along the lines of getting the BUS out of a different table. Called TMS_BUSINESS_UNIT and column BUSINESS_UNIT_NM. In the table there are just the BUSINESS units i need. The only one i need to exlude is the Control Branch.
And then loop untill it has read all the BUSISNESS units.
But i never worked with a loop before so all i am trying is failing.

Example of out put i would like to get it can be differently sorted that is no problem.


BUS	         DATE1	     COUNTVD      COUNTTD

BEARUS33XXX	1/2/2008	5            1
ABNANL2AXXX	1/2/2008	0            0
ABNANL2AXXX	1/3/2008	8            0
BEARUS33XXX	1/3/2008	15           0
ABNANL2AXXX	1/4/2008	7            2 
BEARUS33XXX	1/4/2008	10           6
ABNANL2AXXX	1/5/2008	0            0 
BEARUS33XXX	1/5/2008	0            6




If anybody has a suggestion i am open for it.

Marcel
Re: A count query problem. [message #292281 is a reply to message #292275] Tue, 08 January 2008 07:07 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
For the first issue I would say that you can either join the whole query to your calendar twice (once for each date) or use an analytical function (you can look up the details for that in the SQL reference).

As for you second issue: I'm not sure what the issue is, could you perhaps try to explain it again, with some sample data and desired output?
Re: A count query problem. [message #292477 is a reply to message #292281] Wed, 09 January 2008 00:51 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
hi ( I just noticed that my formating did not work )

Problem 1

When i try to add the join of the second date to this query i get results i do not want.

Example 1 ( Changed the where clause )

ndf_1.vd(+) = times.tim  and
ndf_1.td(+) = times.tim 



his gives the following result
BUS	DATE1	COUNTVD

	1/2/2008	0
	1/3/2008	0
	1/4/2008	0
	1/5/2008	0


And i tried
ndf_1.vd(+) = times.tim  and
ndf_1.td = times.tim  


That gave no output at all.


Problem 2

The second problem is that when there are no counts and it gives zero. I need a zero for all the differnt value's of "ndf_1.bu".
I do not know what value's ill find in that column but i can get these out of a different table called TMS_BUSISSNES_UNIT.

The output i would like to get in the end is something like this ( Was also in the first post )
BUS	         DATE1	     COUNTVD      COUNTTD

BEARUS33XXX	1/2/2008	5            1
ABNANL2AXXX	1/2/2008	0            0
ABNANL2AXXX	1/3/2008	8            0
BEARUS33XXX	1/3/2008	15           0
ABNANL2AXXX	1/4/2008	7            2 
BEARUS33XXX	1/4/2008	10           6
ABNANL2AXXX	1/5/2008	0            0 
BEARUS33XXX	1/5/2008	0            6


Marcel


[Updated on: Wed, 09 January 2008 02:00] by Moderator

Report message to a moderator

Re: A count query problem. [message #292559 is a reply to message #292275] Wed, 09 January 2008 03:44 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
HI

I have made some changes to my query. But its getting very big and there should be a easier way to do this.

Here is my query

select   T2.date1
	   , t2.bus
	   , T1.counttd
	   , T2.countvd

from

(select  ndf_1.bu			bus
	, times.tim			date1
    	, count ( ndf_1.BU ) counttd
         
	 FROM
 	 (select   distinct common_reference_NB     CR
 		   , To_date (trade_dt,'YYYYMMDD')  TD
 		   , To_date (value_dt,'YYYYMMDD')  VD
 		   , business_unit_nm      BU
	  from TMS_VIEW_FX
	  where       
      	        cls_product_nm = 'NDF' and
   		business_unit_nm <> 'Control Branch'
	   
	  union all

	  select   distinct common_reference_NB     CR
 		   , To_date (trade_dt,'YYYYMMDD')  TD
 		   , To_date (value_dt,'YYYYMMDD')  VD
 		   , business_unit_nm      BU
	  from TMS_VIEW_FX_HIS
	  where
	       cls_product_nm = 'NDF' and
 	       business_unit_nm <> 'Control Branch'
	) NDF_1 ,
	(select 
		(to_date(20080101,'YYYYMMDD'))+level tim 
	 from 
	   dual 
	  connect by level < 5
	) times
		 
Where
      ndf_1.td(+) = times.tim 
group by  ( times.tim , NDF_1.bu ) 
order by  times.tim 
) T1 ,

( select  ndf_1.bu			bus
	, times.tim			date1
    	, count ( ndf_1.BU ) countvd
         
   FROM
 	 (select   distinct common_reference_NB     CR
		   , To_date (trade_dt,'YYYYMMDD')  TD
 		   , To_date (value_dt,'YYYYMMDD')  VD
 		   , business_unit_nm      BU
	  from TMS_VIEW_FX
	  where       
    	        cls_product_nm = 'NDF' and
	   	business_unit_nm <> 'Control Branch'
	   
	  union all

	  select   distinct common_reference_NB     CR
 		   , To_date (trade_dt,'YYYYMMDD')  TD
 		   , To_date (value_dt,'YYYYMMDD')  VD
 		   , business_unit_nm      BU
	  from TMS_VIEW_FX_HIS
	  where
                cls_product_nm = 'NDF' and
     	        business_unit_nm <> 'Control Branch'
	  ) NDF_1 ,
	  (select 
	   	 (to_date(20080101,'YYYYMMDD'))+level tim 
            from 
	 	  dual 
	    connect by level < 5
	   ) times
		 
Where
      ndf_1.vd(+) = times.tim 
group by  ( times.tim , NDF_1.bu ) 
order by  times.tim 
) T2	  
	  
where
	 T1.date1 (+) = T2.date1 and
	 T1.bus(+) = t2.bus 
	 order by t2.date1
;


The output now becomes

DATE1	           BUS	     COUNTTD	COUNTVD

1/2/2008	BEARUS33XXX	9	5
1/3/2008	BEARUS33XXX	24	15
1/3/2008	ABNANL2AXXX		8
1/4/2008	ABNANL2AXXX	7	7
1/4/2008	BEARUS33XXX	10	10
1/5/2008			0


This is a step closer to what i need.
I am missing a few things.

I have blank spots where i would like to get "0" by the counttd and countvd. Also i am missing a zero line for ABNANL2AXXX on 1/2/2008. Also am i missing the sets on 1/5/2008.
I would like to get the following
DATE1	           BUS	     COUNTTD	COUNTVD

1/2/2008	BEARUS33XXX	9	5
1/2/2008	ABNANL2AXXX	0	0
1/3/2008	BEARUS33XXX	24	15
1/3/2008	ABNANL2AXXX	0       8
1/4/2008	ABNANL2AXXX	7	7
1/4/2008	BEARUS33XXX	10	10
1/5/2008	ABNANL2AXXX	0       0
1/5/2008	BEARUS33XXX	0       0


Anything? anybody?

Marcel
Re: A count query problem. [message #292578 is a reply to message #292275] Wed, 09 January 2008 04:31 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Okay next update

I have added the missing zero's by tweaking the first part of the query.

select   T2.date1
	   , t2.bus
	   , case when T1.counttd > 0  then t1.counttd
	         else 0
	     end as TD
	   , case when T2.countvd > 0  then t2.countvd
	         else 0
	     end as VD


This gives me the output

DATE1	        BUS	       TD	VD

1/2/2008	BEARUS33XXX	9	5
1/3/2008	BEARUS33XXX	24	15
1/3/2008	ABNANL2AXXX	0	8
1/4/2008	ABNANL2AXXX	7	7
1/4/2008	BEARUS33XXX	10	10
1/5/2008		        0	0


Now i am only missing the lines on 1/2/2008 and 1/5/2008.
See example in last post.

Marcel
Re: A count query problem. [message #292580 is a reply to message #292275] Wed, 09 January 2008 04:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It looks big to analyse . Anyway try to use FULL OUTER JOIN in the inner queries (T1 and T2) instead of Normal Outer join. Then Result may come more closer , I hope .

Thumbs Up
Rajuvan

[Updated on: Wed, 09 January 2008 04:40]

Report message to a moderator

Re: A count query problem. [message #292611 is a reply to message #292275] Wed, 09 January 2008 06:39 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Because this is getting way to complicated.
I have created test data and hope that somebody can come up with a easier way to do things.

create table TMS_VIEW_FX
(
COMMON_REFERENCE_NB 	VARCHAR2 (35),
VALUE_DT 		VARCHAR2 (8),
TRADE_DT 		VARCHAR2 (8),
BUSINESS_UNIT_NM 	VARCHAR2 (50)
)


insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA21 , 1/1/2008 , 1/4/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA22 , 1/2/2008 , 1/2/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA23 , 1/2/2008 , 1/2/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA24 , 1/5/2008 , 1/4/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA25 , 1/4/2008 , 1/1/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA26 , 1/5/2008 , 1/4/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA27 , 1/4/2008 , 1/4/2008 , ABNANL2AXXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA28 , 1/5/2008 , 1/4/2008 , ABNANL2AXXX);

insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA31 , 1/2/2008 , 1/4/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA32 , 1/2/2008 , 1/2/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA33 , 1/2/2008 , 1/2/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA34 , 1/5/2008 , 1/4/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA35 , 1/4/2008 , 1/1/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA36 , 1/5/2008 , 1/4/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA37 , 1/4/2008 , 1/4/2008 , BEARUS33XXX);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA38 , 1/5/2008 , 1/4/2008 , BEARUS33XXX);

insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA46 , 1/5/2008 , 1/4/2008 , Control Branch);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA47 , 1/4/2008 , 1/4/2008 , Control Branch);
insert into TMS_VIEW_FX (COMMON_REFERENCE_NB , TRADE_DT , VALUE_DT , BUSINESS_UNIT_NM ) VALUES (ABNA2A4925ABNA48 , 1/5/2008 , 1/5/2008 , Control Branch);


CREATE TABLE TMS_BUSINESS_UNIT
(
  BUSINESS_UNIT_ID               VARCHAR2(12 BYTE) NOT NULL,
  BUSINESS_UNIT_NM               VARCHAR2(50 BYTE) NOT NULL
)

INSERT INTO tms_business_unit ( BUSINESS_UNIT_ID , BUSINESS_UNIT_NM ) VALUES ( BNALA3428265 , Control Branch );
INSERT INTO tms_business_unit ( BUSINESS_UNIT_ID , BUSINESS_UNIT_NM ) VALUES ( BNALA3428266 , ABNANL2AXXX );
INSERT INTO tms_business_unit ( BUSINESS_UNIT_ID , BUSINESS_UNIT_NM ) VALUES ( BNALA3428267 , BEARUS33XXX );



What i want as a output is something like. ( do not care about the order i do get these records i just want al these records.)

BUS            date        trade_DT     value_DT
                            count        count
ABNANL2AXXX    1/1/2008       1            1
ABNANL2AXXX    1/2/2008       2            2
ABNANL2AXXX    1/3/2008       0            0
ABNANL2AXXX    1/4/2008       2            5
ABNANL2AXXX    1/5/2008       3            0
BEARUS33XXX    1/1/2008       0            1
BEARUS33XXX    1/2/2008       3            2
BEARUS33XXX    1/3/2008       0            0
BEARUS33XXX    1/4/2008       2            4
BEARUS33XXX    1/5/2008       3            1



I hope somebody can help me with this because i am getting in over my head.

I think i took a wrong turn somewhere.

Marcel

[Updated on: Wed, 09 January 2008 06:41]

Report message to a moderator

Re: A count query problem. [message #292668 is a reply to message #292275] Wed, 09 January 2008 09:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You might be trying to get something like


SQL> select * from TMS_VIEW_FX;

COMMON_REFERENCE_NB  VALUE_DT TRADE_DT BUSINESS_UNIT_NM
-------------------- -------- -------- --------------------
ABNA2A4925ABNA21     1/4/2008 1/1/2008 ABNANL2AXXX
ABNA2A4925ABNA22     1/2/2008 1/2/2008 ABNANL2AXXX
ABNA2A4925ABNA23     1/2/2008 1/2/2008 ABNANL2AXXX
ABNA2A4925ABNA24     1/4/2008 1/5/2008 ABNANL2AXXX
ABNA2A4925ABNA25     1/1/2008 1/4/2008 ABNANL2AXXX
ABNA2A4925ABNA26     1/4/2008 1/5/2008 ABNANL2AXXX
ABNA2A4925ABNA27     1/4/2008 1/4/2008 ABNANL2AXXX
ABNA2A4925ABNA28     1/4/2008 1/5/2008 ABNANL2AXXX
ABNA2A4925ABNA21     1/4/2008 1/2/2008 BEARUS33XXX
ABNA2A4925ABNA22     1/2/2008 1/2/2008 BEARUS33XXX
ABNA2A4925ABNA23     1/2/2008 1/2/2008 BEARUS33XXX
ABNA2A4925ABNA24     1/4/2008 1/5/2008 BEARUS33XXX
ABNA2A4925ABNA25     1/1/2008 1/4/2008 BEARUS33XXX
ABNA2A4925ABNA26     1/4/2008 1/5/2008 BEARUS33XXX
ABNA2A4925ABNA27     1/4/2008 1/4/2008 BEARUS33XXX
ABNA2A4925ABNA28     1/4/2008 1/5/2008 BEARUS33XXX
ABNA2A4925ABNA26     1/4/2008 1/5/2008 Control Branch
ABNA2A4925ABNA27     1/4/2008 1/4/2008 Control Branch
ABNA2A4925ABNA28     1/5/2008 1/5/2008 Control Branch

19 rows selected.

SQL>    With dataset AS(
  2     SELECT distinct BUSINESS_UNIT_NM  BUS ,tim
  3     FROM TMS_VIEW_FX  ,
  4                 (select
  5                             (to_date('20080101','YYYYMMDD'))+level-1 tim
  6                      from
  7                        dual
  8                       connect by level <= 5
  9                     ) STIME
 10     where BUSINESS_UNIT_NM <>'Control Branch')
 11     SELect d.tim , d.BUS,
 12                ( SELECT COUNT(COMMON_REFERENCE_NB )
 13                  from  TMS_VIEW_FX t
 14                  WHERE d.tim = TO_DATE (t.TRADE_DT(+),'MM/DD/YYYY')
 15                  AND  d.BUS  = t.BUSINESS_UNIT_NM (+) ) TD,
 16                ( SELECT COUNT(COMMON_REFERENCE_NB )
 17                  from  TMS_VIEW_FX t
 18                  WHERE d.tim = TO_DATE (t.VALUE_DT(+),'MM/DD/YYYY')
 19                  AND  d.BUS  = t.BUSINESS_UNIT_NM (+) ) VD
 20     FROM  DATASET  d
 21     group by d.tim , d.BUS;

TIM       BUS                               TD         VD
--------- ------------------------- ---------- ----------
02-JAN-08 ABNANL2AXXX                        2          2
04-JAN-08 BEARUS33XXX                        2          5
05-JAN-08 ABNANL2AXXX                        3          0
02-JAN-08 BEARUS33XXX                        3          2
05-JAN-08 BEARUS33XXX                        3          0
01-JAN-08 ABNANL2AXXX                        1          1
03-JAN-08 ABNANL2AXXX                        0          0
03-JAN-08 BEARUS33XXX                        0          0
04-JAN-08 ABNANL2AXXX                        2          5
01-JAN-08 BEARUS33XXX                        0          1

10 rows selected.

SQL>


Thumbs Up
Rajuvan.

[Updated on: Thu, 10 January 2008 03:09]

Report message to a moderator

Re: A count query problem. [message #292931 is a reply to message #292275] Thu, 10 January 2008 03:25 Go to previous message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi

And thanks Rajuvan.

I never worked with a dataset before and i did not know i could use a select statment in that way.

The real world is never as easy as de test data so i create a query from your test query.
And this is the code.

With dataset AS
    (
      SELECT distinct BUSINESS_UNIT_NM  BUS 
	     ,tim
       FROM 
        (select BUSINESS_UNIT_NM BUSINESS_UNIT_NM
	  from TMS_VIEW_FX
	union all
	 select BUSINESS_UNIT_NM BUSINESS_UNIT_NM
	   from TMS_VIEW_FX_HIS
	) ,
        (select
               (to_date('20080101','YYYYMMDD'))+level-1 tim
           from
               dual
         connect by level <= 5
        ) STIME
       where BUSINESS_UNIT_NM <>'Control Branch'
    )
    SELECT d.tim , 
           d.BUS,
           (SELECT COUNT(COMMON_REFERENCE_NB )
              from
		( select distinct 
                         COMMON_REFERENCE_NB COMMON_REFERENCE_NB ,
			 TRADE_DT TRADE_DT ,
                         BUSINESS_UNIT_NM BUSINESS_UNIT_NM    
		     from TMS_VIEW_FX 
		     where cls_product_nm = 'NDF'
		   union 
		  select distinct 
                         COMMON_REFERENCE_NB COMMON_REFERENCE_NB ,
		         TRADE_DT TRADE_DT ,
                         BUSINESS_UNIT_NM    BUSINESS_UNIT_NM    
		   from TMS_VIEW_FX_HIS 
		   where cls_product_nm = 'NDF'
                 ) t
	    WHERE d.tim = TO_DATE (t.TRADE_DT(+),'YYYYMMDD')
                  AND  d.BUS  = t.BUSINESS_UNIT_NM (+) ) TD,
          
	  ( SELECT COUNT(COMMON_REFERENCE_NB )
               from  ( select distinct 
                              COMMON_REFERENCE_NB COMMON_REFERENCE_NB ,
			      VALUE_DT VALUE_DT,
                              BUSINESS_UNIT_NM    BUSINESS_UNIT_NM    
			 from TMS_VIEW_FX 
			where cls_product_nm = 'NDF'
			  union
		       select distinct 
                              COMMON_REFERENCE_NB COMMON_REFERENCE_NB ,
			      VALUE_DT VALUE_DT,
                              BUSINESS_UNIT_NM    BUSINESS_UNIT_NM    
			 from TMS_VIEW_FX_HIS 
			 where cls_product_nm = 'NDF' ) t
            WHERE d.tim = TO_DATE (t.VALUE_DT(+),'YYYYMMDD')
                  AND  d.BUS  = t.BUSINESS_UNIT_NM (+) ) VD
    
	FROM  DATASET  d 
	   

group by d.tim , d.BUS;


This gives the result i need.

TIM	          BUS	        TD	VD

1/1/2008	ABNANL2AXXX	0	0
1/1/2008	BEARUS33XXX	0	0
1/2/2008	ABNANL2AXXX	10	0
1/2/2008	BEARUS33XXX	8	5
1/3/2008	ABNANL2AXXX	0	5
1/3/2008	BEARUS33XXX	18	15
1/4/2008	ABNANL2AXXX	4	7
1/4/2008	BEARUS33XXX	7	9
1/5/2008	ABNANL2AXXX	2	0
1/5/2008	BEARUS33XXX	0	0



Again thanks.

Marcel
Previous Topic: sequence in procedure
Next Topic: insert
Goto Forum:
  


Current Time: Wed Dec 07 03:05:18 CST 2016

Total time taken to generate the page: 0.19122 seconds