Archives of the TeradataForum

Message Posted: Fri, 13 Sep 2002 @ 19:04:43 GMT

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

I think the problem is that the columns "market_code" and "agent_code" are ambiguous. Add the explicate table name or an alias "mktg_temp.agent.market_code" and "mktg_temp.agent.agnet_code".

As I mentioned before the OUTER JOIN syntax is very finicky. I have modified your SQL using one of our tools (see below). Some other things that trouble me is the use of the INNER JOIN in the Where clause, this MAY cause some problem for the optimizer. Mixing FROM clause joins with WHERE clause joins is not a good practice. Check the Explain.

One more issue. The Table "pacMASTEROWN.MARKET" doesn't appear to be joined to anything, I can't believe that this is intended. Let me know how this is joined to the other tables and I can regenerate the whole statement or you can add it your self.

John Grace
Geppetto's Workshop

SELECT
  pacMASTEROWN.MARKET.MRKT_CD,
  pacMASTEROWN.MARKET.MRKT_NM,count(*)
  agent.agt_channel,agent.agt_group,
  count(distinct pacMASTEROWN.CUSTOMER.BAN_ID) as ltmay

FROM
  pacMASTEROWN.MARKET,
  (
     mktg_temp.agent
     RIGHT JOIN pacMASTEROWN.DEALER_TYPE  Subscriber_DEALER_TYPE ON
       mktg_temp.agent .market_code=Subscriber_Setup_Dealer.MRKT_CD and
       Subscriber_Setup_Dealer.DEALR_CD= mktg_temp.agent.agent_code
         {
          INNER JOIN pacMASTEROWN.DEALER  Subscriber_Setup_Dealer ON

Subscriber_DEALER_TYPE.MRKT_CD=Subscriber_Setup_Dealer.MRKT_CD
and

Subscriber_DEALER_TYPE.DEALR_TYPE_CD=Subscriber_Setup_Dealer.DEALR_TYPE_CD
            (
               RIGHT JOIN pacMASTEROWN.SUBSCRIBERS ON
                 Subscriber_Setup_Dealer.DEALR_CD =
pacMASTEROWN.SUBSCRIBERS.SBSCR_SETUP_DEALR_CD and

Subscriber_Setup_Dealer.MRKT_CD=pacMASTEROWN.SUBSCRIBERS.SBSCR_MRKT_CD
                 (
                    RIGHT JOIN pacMASTEROWN.CUSTOMER ON
                      pacMASTEROWN.CUSTOMER.CUST_MRKT_CD =
pacMASTEROWN.SUBSCRIBERS.SBSCR_MRKT_CD AND
                      pacMASTEROWN.CUSTOMER.CUST_SUB_MRKT_CD =
pacMASTEROWN.SUBSCRIBERS.CUST_SUB_MRKT_CD AND
                      pacMASTEROWN.CUSTOMER.SNPST_PRD_ID =
pacMASTEROWN.SUBSCRIBERS.SNPST_PRD_ID AND
                      pacMASTEROWN.CUSTOMER.BAN_ID =
pacMASTEROWN.SUBSCRIBERS.BAN_ID
                 )
             )
       )
   }

WHERE
    (
  substr(pacMASTEROWN.SUBSCRIBERS.SNPST_PRD_ID,1,6)= '200209'
  AND  substr(pacMASTEROWN.CUSTOMER.SNPST_PRD_ID,1,6)='200209'
  AND  (pacMASTEROWN.SUBSCRIBERS.EMAIL_ADR  <> '    '
  OR   pacMASTEROWN.CUSTOMER.EMAIL_ADR  <> '     '  )
  and  SUBSCRIBERS.SBSCR_STUS_CD  IN  ('A', 'S')
  AND  pacMASTEROWN.SUBSCRIBERS.INITL_ACTVN_DT  <  '2002-05-08')
  group by
  pacMASTEROWN.MARKET.MRKT_CD,
  pacMASTEROWN.MARKET.MRKT_NM,
 agent.agt_channel,agent.agt_group

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