Archives of the TeradataForum

Message Posted: Thu, 24 Aug 2006 @ 12:53:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Thursday, August 24, 2006 08:07 -->

Hi All

My Apoloigies to you all regarding the formatting ...

As far the query after breaking my head too much I have also found the answer to the query now ........

In the whole query all the tables were joined using a Inner Join and one of the table DP_SB_CA.X_RATE_HIST ( which has only 2000 records) was the one creating the problem.

So I used a Left Join to join to that particular table after confirming with the Users and its working perfectly fine now .... and the explain plan is also consistent for all IN conditions

For all of you to have a look at the same query and to provide any suggestion and for getting the answers consistently using Inner join itself I have placed the query again and the part it gives problem is commented as below

     ***************************************************************
     /* Doesn't Complete ,, Gives Error No More Spool Space */
     /* Works Fine  answer is in 2 mins*/
     ***************************************************************


     *****************
     QUERY
     ***************
     sel c.CA_Cross_Sell_Group As Pdt_Group,
     Count(Distinct a.main_acct_holder_party_id) #Customers,
     Count(Distinct a.account_num||a.account_modifier_num) #Placements,
     Sum(v0521.Event_Amt*cast(x_rate.global_to_source_currency_rate As
     decimal(10,5))) Sum_Rollover_Amt_SGD,
     Avg(v0521.Event_Amt*cast(x_rate.global_to_source_currency_rate As
     decimal(10,5))) Avg_Rollover_Amt_SGD
     From
     (sel Account_Num,
     Account_Modifier_Num,
     Main_Acct_Holder_Party_Id
     From         VM_ME_TERM_DEPOSIT
     Where      Period_ID='2006-07-31'
        And   Last_Renewal_Dt>='2006-07-01'
        And   Last_Renewal_Dt<='2006-07-31') a

     Join
     (
     Select   v0300.account_num,
     v0300.account_modifier_num,b.ca_cross_sell_group
     From
     V0300_AGREEMENT v0300
     Join V0200_MAP v0200
        On   v0200.EDW_Value=v0300.Product_Id
     Join dp_sb_ca.pdt_group b
        On   v0200.Source_Value=b.host_prod_id
        And   v0200.Source_Number=b.Source_Number
     Where      CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU Fixed
     Deposit')                                          /* Doesn't Complete
     ,, Gives Error No More Spool Space */
     -- Where      CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU
     Fixed Deposit','' )                          /* Works Fine  answer is in
     2 mins*/
     -- Where      CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU
     Fixed Deposit','Structured Deposit' )   /* Works Fine  answer is in 2
     mins*/

     ) c
     on c.account_num = a.account_num
     and c.account_modifier_num = a.account_modifier_num

     Join
     (sel Party_Id, segment_desc
     From         VM_ME_PARTY_INDIVIDUAL
     Where      (segment_desc is Null
        Or     segment_desc='Mass Individual')
        And   period_id='2006-07-31'
     Union
     sel Party_Id, segment_desc
     From         VM_ME_PARTY_ORGANISATION
     Where      (segment_desc is Null
        Or     segment_desc='Mass Individual')
        And   period_id='2006-07-31') party
     On   party.party_id=a.Main_Acct_Holder_Party_Id

     Join
     (sel event_id, account_num, account_modifier_num
     From         V0504_ACCOUNT_EVENT) v0504
        On   v0504.account_num = a.account_num
        And   v0504.account_modifier_num = a.account_modifier_num
     Join
     (sel event_id, event_start_dt, Event_Activity_Type_Cd
     From         V0500_EVENT
     Where      Event_Start_Dt>='2006-07-01'
        And   Event_Start_Dt<='2006-07-31'
        And   Event_Activity_Type_Cd In ('FD000104','ACU00121')) v0500
        On   v0500.event_id = v0504.event_id
     Join
     V0521_FINANCIAL_EVENT v0521
        On   v0521.Event_Id=v0504.event_id
Join
dp_sb_ca.x_rate_hist x_rate
   On
Trim(x_rate.source_currency_cd)=v0521.Event_Currency_Cd
   And   x_rate.period_id = '2006-07-31'
group by 1

	<Prev	Next>		<<First	<Prev	Next>	Last>>