Re: Optimisation of a sql query

Guys,
Thanks a lot for your time , comments , suggestions and remarks! I think there are couple of things about a couple of things that I can safely conclude from here.
1. I should not post a query of SAS related stuff on oracle group, even if its finally a sql query that I ve been talking about. 2. I should post the actual query coz u never know what important stuff are u missing while u r modifying your query. 3. Having clause is a waste here. I should go ahead with where clause as suggested by almost everybody.

Once again, thanks a lot for your time and concern. Sincere apologies to all of you to provide you with the wrong query in my first mail and wasting your time.
I ll try look for a sas group in google now.

Thanks and Regards,
Abhijat.

Brian Peasland wrote:
> > 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
Received on Thu Jul 20 2006 - 01:46:59 CDT