Re: Optimisation of a sql query

> My actual code doesn't contain select *, it has fixed selections, hence
> what David is mentioning about select * is automatically taken care of.
> About using group by and having, I have to confess that the query I
> have mentioned here is slightily different from the actual query. The
> actual query does contain selections like "SUM(var1)".

So the previous posts were all a waste of everyone's time because you did not post the *actual* SQL statement?!?!?!

> The actual query that I intend to execute is like this
> CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
> SELECT
> DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
> NEW_ISSUER,"DISCOVER" AS ISSUER,
> B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
> SUM(A.AUTH_AMT) AS AUTHORIZED ,
> SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
> CASHOVER ) AS MERCHANDISE
> FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
> ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> GROUP BY B.MCC, B.MERCHANT
> HAVING A.RSPNS_CDE = "00"
> AND A.TST_ACCT_CDE = ""
> AND A.AUTH_TRAN_CDE IN ("0110","0120")
> AND A.AUTH_PROC_1_CDE = "09";
Move the HAVING clause to the WHERE clause instead:

  	CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
  	SELECT
  	 DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
  NEW_ISSUER,"DISCOVER" AS ISSUER,
  	 B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
  SUM(A.AUTH_AMT) AS AUTHORIZED ,
  	 SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - ALCULATED
  CASHOVER ) AS MERCHANDISE
  	FROM rd_at2.AUTH_MAY06 A LEFT JOIN p_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
  	ON   A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
          AND        A.TST_ACCT_CDE = ""
  	AND        A.AUTH_TRAN_CDE  IN ("0110","0120")
  	AND 	   A.AUTH_PROC_1_CDE = "09"
  	GROUP BY   B.MCC, B.MERCHANT;

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown