Date:         Fri, 22 Oct 2004 10:13:39 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Proc SQL ?TOO MANY SORT FIELDS?
Comments: To: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Content-Type: text/plain

Toby:
Just a guess ....
Nested subqueries of the form SELECT DISTINCT * ... get implemented using
sorts. Even if you are SELECT'ing a small set of the column variables in the
main query, a DISTINCT qualifier has a different effect when applied to a
superset of column variables (and may not actually do what you want done).
The SQL query optimizer cannot push the column variable lists in the main
query down to the subquery. The query server may have to sort on a composite
key of too many column variables.

I'd rethink the use of the DISTINCT qualifier. What do you see as its role?
Are you trying to eliminate duplicates accross all column variables in a
row, or duplicates of the 'projection' of the column variable subset being
selected in the main query? If the latter, simply list column variables in
the SELECT DISTINCT clause of the nested subqueries. The server will then
SELECT distinct sets of values of each class.
Sig

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dunn,
Toby
Sent: Thursday, October 21, 2004 4:43 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Proc SQL ?TOO MANY SORT FIELDS?


Anyone know why I get the message that says "WER113A  TOO MANY SORT FIELDS"
when I run this SQL query:



PROC SQL;

 CREATE TABLE GRADE3 AS

  SELECT COALESCE(MARCH.STUDENTI = APRIL.STUDENTI) AS STUDENTI ,

         COALESCE(MARCH.TFNAME   = APRIL.TFNAME  ) AS TFNAME   ,

         COALESCE(MARCH.TLNAME   = APRIL.TLNAME  ) AS TLNAME   ,

         COALESCE(MARCH.BTHDAY   = APRIL.BTHDAY  ) AS BTHDAY   ,

         MARCH.R_SSC , MARCH.R_SCODE , MARCH.R_MET ,

         APRIL.R_SSC AS A_R_SSC , APRIL.R_SCODE AS A_R_SCODE   ,

         APRIL.R_MET AS A_R_MET , APRIL.CAMPUS , APRIL.DISTRICT ,

         APRIL.PEIMSCDC , APRIL.ATRISK , APRIL.BILING , APRIL.DISADV ,

         APRIL.ESLANG , APRIL.ETHNIC , APRIL.GIFTAL , APRIL.LEPROF ,

         APRIL.MIGSTA , APRIL.SEX    , APRIL.SPECED , APRIL.TSLANG ,

         APRIL.VOCED  , APRIL.GRADE

   FROM (SELECT DISTINCT *

          FROM G3M.TAKS3 (READ = MOCHA)) AS MARCH

         FULL JOIN

        (SELECT DISTINCT *

          FROM G3A.TAKS3 (READ = MOCHA)) AS APRIL

    ON (MARCH.STUDENTI = APRIL.STUDENTI) AND

       (MARCH.TFNAME   = APRIL.TFNAME  ) AND

       (MARCH.TLNAME   = APRIL.TLNAME  ) AND

       (MARCH.BTHDAY   = APRIL.BTHDAY  ) ;

QUIT ;



I am running on SAS v8.2 on OS/390.



TIA

Toby Dunn