ASP VBscript Filtering Duplicates in Loop Can Pay for Assistance

[dprichard]

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 (http://forums.aspfree.com/editpost.php?do=editpost&p=687041) early this morning, but haven't heard anything back so I thought I would try here.


<%

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
%>

[Old Pedant]

Why can't you just do
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:

...
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.

[whammy]

I would think Old Pedant's solution should work. I used Select Distinct for years. :)

[Old Pedant]

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?

[dprichard]

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.

[Old Pedant]

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.