Schema sort ascending , desc

Results 1 to 2 of 2

Thread: Schema sort ascending , desc

  1. #1
    Join Date
    Dec 1969

    Default Schema sort ascending , desc

    Hello,<BR><BR>I am trying to get the data from 6 tables based on the date the records were submitted. It does work(using Conn.OpenSchema (adSchemaTables)) though I seems not be able to arrange the data neither with &#039;ORDER BY&#039; nor &#039;ORDER BY DESC&#039;. What happens is say, I have tables (A,B,C) "A" has a record submitted 01/01/98, "B" - 01/01/97 and "C" - 01/01/2000. With SQL &#039;ORDER BY&#039; I expect to see "B","A", "C". It does not work no matter what I do it always goes as "A, B, C".<BR><BR>PLEASE ADVISE: below is the code:<BR><BR><BR>&#060;%@ Language=VBScript %&#062;<BR>&#060;!--#INCLUDE FILE=""--&#062; <BR>&#060;%<BR>SemiPeriod = Array("12/31/1999", "07/30/2000")<BR>SemiPeriod1 = Array("07/30/1999", "01/30/2000")<BR><BR>For m = LBound(SemiPeriod) to Ubound(SemiPeriod)<BR><BR>Set conn = createObject ("ADODB.Connection")<BR>Conn.Open ""<BR><BR>Set oRs =Conn.OpenSchema (adSchemaTables) &#039;OPEN SCHEMA <BR><BR>while Not oRs.EOF <BR>FldResult = oRs("Table_Name")<BR><BR>If FldResult = "MSysObjects" or fldResult ="MSysACEs" or fldResult ="MSysAccessObjects" or fldResult ="MSysModules" or fldResult ="MSysModules2" or fldResult ="MSysQueries" or fldResult ="MSysRelationships" then<BR> <BR>Else <BR>Set RS = Server.CreateObject("ADODB.Recordset")<BR><BR>SQLS tring = "Select "&fldResult&".*, "&fldResult&".Service From "&fldResult&" where ((("&fldResult&".[LName]) =&#039;"&LName&"&#039;) and (("&fldResult&".Service) Between #"&SemiPeriod1(m)&"# and #"& SemiPeriod(m)&"# )) order by "&fldResult&".Service; "<BR> <BR> rs.Open sqlstring, conn, adOpenStatic <BR> <BR> Do while not rs.eof <BR> <BR> For i = 0 to rs.Fields.Count-1 <BR> FldServiceDate=rs("Service") <BR> <BR> Next<BR> REsponse.write fldResult<BR> Response.write fldServiceDate <BR> rs.MoveNext <BR> Loop <BR> <BR>End If <BR>ors.MoveNext <BR>Wend <BR>Next <BR> ors.close<BR> Set ors=nothing<BR> Set Rs=nothing<BR> Set conn=nothing <BR> %&#062;

  2. #2
    Join Date
    Dec 1969

    Default WHAT sort???

    You aren&#039;t doing any SORT.<BR><BR>You are doing ONE QUERY PER table. Your ORDER BY works fine IN THAT ONE TABLE. But you haven&#039;t combined the tables, so the output comes in the same order in which you process the tables.<BR><BR>Why are you surprised by that?<BR><BR>If you want a cross-table ordering, then you have to put *ALL* the tables into a *SINGLE QUERY*.<BR><BR>But the truth of the matter is...You&#039;ve screwed up if you have to do something like this.<BR><BR>You&#039;ve broken a single table up into multiple tables when you shouldn&#039;t have. You haven&#039;t followed the rulse of normalization. Or you&#039;ve followed them too far. Take your pick.<BR><BR>ANYWAY...<BR><BR>It&#039;s probably too late to inject sanity into your database, so...<BR><BR>The only way I see to do this is to do a UNION of all the individual table queries and then order the entire union.<BR><BR>Personally, I think that is yuck. But it should work.<BR><BR>One more thing...<BR><BR>What in the world is this code for:<BR><BR>For i = 0 to rs.Fields.Count-1 <BR>&nbsp; &nbsp; FldServiceDate=rs("Service") <BR>Next<BR><BR>That&#039;s the same thing as doing<BR><BR>For i = 0 to 100<BR>&nbsp; &nbsp; x = 72<BR>Next<BR><BR>Well, it works. But, then, so would simply<BR><BR>x=72<BR><BR>No?<BR><BR>Do you really want to do the UNION thing?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts