Web Analytics Made Easy -
StatCounter ASP VBscript Filtering Duplicates in Loop Can Pay for Assistance - CodingForum

Announcement

Collapse
No announcement yet.

ASP VBscript Filtering Duplicates in Loop Can Pay for Assistance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ASP VBscript Filtering Duplicates in Loop Can Pay for Assistance

    I have some code I need some help with. I am working with an ODBC driver that won't let me do subqueries or limit results so I am trying to figure out how to limit eliminate duplicates as it is looping through the results of the query.

    I am trying to only show one line per site sku and just ignore the duplicates.

    Any help would be greatly appreciated. I need a pretty quick turnaround on this one and would be willing to compensate for a complete working solution. If you are looking for compensation, please IM me to discuss compensation prior to posting solution please.

    I posted this in one other forum early this morning, but haven't heard anything back so I thought I would try here.

    Code:
    <%
    
    Dim objConn2         
    Dim objRS2          
    Dim strSQL2 
    Dim filename2
    Dim customDomain2, customDSN2, customCID2, customSUBID2, customAID2
    
    customDomain2 = Request.QueryString("domain")
    customDSN2 = "DSN=" & Request.QueryString("co")
    customCID2 = Request.QueryString("cid")
    customSUBID2 = Request.QueryString("subid")
    customAID2 = Request.QueryString("aid")
    
    filename2 = "Temp\commodities.xml"
    
    strSQL2 = "SELECT sclev.MktLvl3 AS SiteSku, 'http://" & customDomain & "/go.asp?prog=lvl3&lastprog=&pend=lvl3&mktlvl3=' + sclev.MktLvl3 + '&regprc=' + {fn CONVERT(xw481.MktSalePrice, SQL_VARCHAR)} + '&embflag=' + xw481.EmbroidFlag AS BUYURL, sclev.MktDesc AS CategoryName, sclev.MktDesc AS description"
    strSQL2 = strSQL2 & " FROM (sclev LEFT OUTER JOIN sclevidx ON sclev.MktLvl1=sclevidx.MktLvl1 AND sclev.MktLvl2=sclevidx.MktLvl2 AND sclev.MktLvl3=sclevidx.MktLvl3) LEFT OUTER JOIN xw481 ON sclev.MktLvl1=xw481.MktLvl1 AND sclev.MktLvl2=xw481.MktLvl2 AND sclev.MktLvl3=xw481.MktLvl3"
    strSQL2 = strSQL2 & " WHERE sclev.MktLvl3 <> ''" ' AND xw481.MktSalePrice<>0  GROUP BY sclev.MktLvl3, xw481.MktSalePrice, sclev.MktDesc"
    
    SET objConn2 = Server.CreateObject("ADODB.Connection")
    SET objRS2 = Server.CreateObject("ADODB.recordset")
    
    objConn2.Open customDSN2
    objRS2.Open strSQL2, objConn2
    
    Dim objFSO2, objFile2
    SET objFSO2 = Server.CreateObject("Scripting.FileSystemObject")
    SET objFile2 = objFSO2.CreateTextFile(Server.MapPath(filename2), true)
    
    objFile2.Write "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    objFile2.Write "<commodity-list xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""C:\Documents and Settings\lwilliams\Desktop\Borderfree Internal - Catalog v2 Specifications\commodity.xsd"">" & vbCrLf & vbCrLf
    objFile2.Write "<!-- When developing and running process make sure to include schema definition (see below) to ensure validity of xml-->" & vbCrLf
    objFile2.Write "<!--<commodity-list xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""C:\commodity.xsd""> -->" & vbCrLf & vbCrLf
    objFile2.Write "<!-- minimum commodity definition-->" & vbCrLf
    objFile2.Write "<!-- -->" & vbCrLf
    
    Dim limit2
    limit2 = objRS2.fields.Count - 2
    Do until objRS2.eof
    	For i = 0 To limit
    		'objFile.Write objRS.fields(i).value
    	Next
    	objFile2.Write vbTab & "<commodity>" & vbCrLf
    	objFile2.Write vbTab & vbTab & vbTab & "<site-sku>" & TRIM(objRS2.fields("SiteSku").value) & "</site-sku>" & vbCrLf
    	objFile2.Write vbTab & vbTab & vbTab & "<name>" & REPLACE(objRS2.fields("CategoryName").value,"&","and") & "</name>" & vbCrLf
    	objFile2.Write vbTab & vbTab & vbTab & "<description>" & REPLACE(objRS2.fields("description").value,"&","and") & "</description>" & vbCrLf
    	objFile2.Write vbTab & vbTab & vbTab & "<url><![CDATA[" & objRS2.fields("BUYURL").value & "]]></url>" & vbCrLf
    	objFile2.Write vbTab & "</commodity>" & vbCrLf
    	
    	objFile2.WriteLine
    	objRS2.MoveNext
    Loop
    objFile2.Write "</commodity-list>"
    
    objConn2.Close
    objFile2.Close
    
    SET objFile2 = Nothing
    SET objFSO2 = Nothing
    SET objRS2 = Nothing
    SET objConn2 = Nothing
    %>
    Last edited by dprichard; Apr 1, 2009, 02:24 PM.
    David A. Prichard

    Computer Support Tampa - Computer Support Chicago - Technology Blog

  • #2
    Why can't you just do
    Code:
    SELECT DISTINCT ...
    ???

    Are you saying this DB doesn't support that, either?

    How come CategoryName and description have the same value (that is, both are really sclev.MktDesc)??

    And why are you bothering with the LEFT JOIN to sclevidx??? Since none of the results will depend on that table (you don't SELECT any field from it and the LEFT JOIN ensures it won't matter if a matching record from that table exists or not)???

    It's certainly personal preference, but I would build up the URL in the ASP code, not in the SQL. Thus:
    Code:
    ...
    strSQL2 = "SELECT DISTINCT L.MktLvl3 AS SiteSku, X.MktSalePrice, X.EmbroidFlag, L.MktDesc " _
            & " FROM sclev AS L " _
            & " LEFT OUTER JOIN xw481 AS X ON L.MktLvl1=X.MktLvl1 AND L.MktLvl2=X.MktLvl2 AND L.MktLvl3=X.MktLvl3 "
            & " WHERE L.MktLvl3 <> ''" 
    
    SET objConn2 = Server.CreateObject("ADODB.Connection")
    objConn2.Open customDSN2
    
    Set objRS2 = objConn2.Execute( SQL )
    
    ...
    
    Do until objRS2.eof
        sku = Trim(objRS2("SiteSKU"))
        buyurl = "http://" & customDomain & "/go.asp?prog=lvl3&lastprog=&pend=lvl3&mktlvl3=" _
               & sku & "&regprc=" & objRS2("MktSalePrice" & "&embflag=" & objRS2("EmbroidFlag")
        desc = Replace( objRS2("MktDesc"), "&", "and" )
        catname = desc 
        objFile2.Write vbTab & "<commodity>" & vbCrLf
        objFile2.Write vbTab & vbTab & vbTab & "<site-sku>" & sku & "</site-sku>" & vbCrLf
        objFile2.Write vbTab & vbTab & vbTab & "<name>" & catname & "</name>" & vbCrLf
        objFile2.Write vbTab & vbTab & vbTab & "<description>" & desc & "</description>" & vbCrLf
        objFile2.Write vbTab & vbTab & vbTab & "<url><![CDATA[" & buyurl & "]]></url>" & vbCrLf
        objFile2.Write vbTab & "</commodity>" & vbCrLf
    	
        objFile2.WriteLine
        objRS2.MoveNext
    Loop
    objFile2.Write "</commodity-list>"
    ....
    If SELECT DISTINCT doesn't work, it's because you have multiple records in the xw481 table that match a single record in the sclev table, in which case how do you decide which of those multiple records you want to include in the XML? Yes, you could choose just one. But you have to tell the code WHICH one to choose.
    Be yourself. No one else is as qualified.

    Comment


    • #3
      I would think Old Pedant's solution should work. I used Select Distinct for years.
      Former ASP Forum Moderator - I'm back!

      If you can teach yourself how to learn, you can learn anything. ;)

      Comment


      • #4
        Well, couldn't have been all that urgent, since it's now been 4 days since he posted.

        He *DID* say he was using an oddball ODBC-based driver, and there *are* some really weird ones out there, ones hooked up to strange DBs. Or ancient DBs, such a DBase III! Did you know that's still available from the Control Panel?
        Be yourself. No one else is as qualified.

        Comment


        • #5
          I am sorry I didn't respond. I got Microsoft support involved and they couldn't even get it working. I gave up on it for now. The ODBC driver the client is using is a joke.
          David A. Prichard

          Computer Support Tampa - Computer Support Chicago - Technology Blog

          Comment


          • #6
            Quite frankly, I'm amazed that the driver supports LEFT JOIN if it's that much of a joke. DISTINCT is much easier to support than LEFT JOIN.

            But you know, it's pretty trivial to implement the DISTINCT in VBS code if you can't get it to work in the SQL code.

            For that matter, you can also implement LEFT JOIN via VBS code, albeit a bit more clumsily.

            I'd bet a few shekels I could get it to work if I had more details.
            Be yourself. No one else is as qualified.

            Comment

            Working...
            X