Select Distinct Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Distinct Problem

Author

Topic

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-08-06 : 15:59:47

Hello,
I came to your site by request from another message board.

I am trying to eliminate duplicate records but need to use this query string between 2 tables below.

strBRP_ECN = "SELECT Brp_archive.DocNum, Brp_archive.Size, Brp_archive.sheets, Brp_archive.[Cur Rev], Brp_archive.Location, Brp_archive.Class, Brp_archive.OOC, Brp_archive.Title, Hanging_ECN.[Drawing Num], Hanging_ECN.ECN_Rev, Hanging_ECN.ECN_Num FROM Brp_archive INNER JOIN Hanging_ECN ON Brp_archive.DocNum = Hanging_ECN.[Drawing Num] WHERE Hanging_ECN.[Drawing Num] like'" & SaveFile & "'"

The results I am getting from this is the duplicate records also. Is there another way to resolve the duplicate record problem outside of this? I am probably getting the issue because the database has over 10,000 records on one table and 2400 in the other.

Please help
thx,
edb

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 16:05:46

What's the relationship between the 2 tables...Does DocNum or [Drawing Num] Occur more than once in either table?

Brett

8-)

SELECT POST=NewId()

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-06 : 16:20:00

Could you post a sample of the results?

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-08-06 : 17:37:15

Thanks for helping me out.

Drawing Num = DocNum Yes ... there are no other areas to target.

Gee ... I cannot attach an image or word doc to this message board. But I can place 6 records below.

****************************************************************

RECORD 1
8026540 CCA, CABLE INTERFACE
E
1
U F1-A-G15
N
N/A ECN-004133 ECN-004258 ECN-004582 ECN-005392 ECN-005712 ECN-006040

RECORD 2
8026540 CCA, CABLE INTERFACE
E
1
U F1-A-G15
N
N/A ECN-004133 ECN-004258 ECN-004582 ECN-005392 ECN-005712 ECN-006040

RECORD 3
8026540 CCA, CABLE INTERFACE
E
1
U F1-A-G15
N
N/A ECN-004133 ECN-004258 ECN-004582 ECN-005392 ECN-005712 ECN-006040

RECORD 4
8026540 CCA, CABLE INTERFACE
E
1
U F1-A-G15
N
N/A ECN-004133 ECN-004258 ECN-004582 ECN-005392 ECN-005712 ECN-006040

RECORD 5
8026540 CCA, CABLE INTERFACE
E
1
U F1-A-G15
N
N/A ECN-004133 ECN-004258 ECN-004582 ECN-005392 ECN-005712 ECN-006040

****************************************************************

HERE IS MY CODE

****************************************************************

<%
' -------------------------- BEGIN CHECKING FOR RECORDS BETWEEN BRP AND FILEINFO ----------------------------

Dim oldPDFConn

set oldPDFConn=Server.CreateObject("ADODB.Connection")
oldPDFConn.Provider="Microsoft.Jet.OLEDB.4.0"
oldPDFConn = "dsn=test3"

Dim strBRP_ECN
Dim Loca, CLAS, CLAS2, OOC

FilePath = "http://www.dm2000.com"

Set objRS_ECN = Server.CreateObject("ADODB.Recordset")

strBRP_ECN = "SELECT DISTINCT Brp_archive.DocNum, Brp_archive.Size, Brp_archive.sheets, Brp_archive.[Cur Rev], Brp_archive.Location, Brp_archive.Class, Brp_archive.OOC, Brp_archive.Title, Hanging_ECN.[Drawing Num], Hanging_ECN.ECN_Rev, Hanging_ECN.ECN_Num FROM Brp_archive INNER JOIN Hanging_ECN ON Brp_archive.DocNum = Hanging_ECN.[Drawing Num] WHERE Hanging_ECN.[Drawing Num] like'" & SaveFile & "'"

objRS_ECN.Open strBRP_ECN, oldPDFConn, 1,3

DO UNTIL (objRS_ECN.EOF)

Response.write "<TR><td><DIV ALIGN=CENTER><input type=checkbox Checked name=Checkbox value=" & objRS_ECN("docnum") & "#" & 0 & ">" & "</div></SPAN></td>"

'PDF FILE - CHANGE CHARACTER TO VERSION NUMBER BASED ON TWO TABLES
Response.Write "<td><SPAN CLASS=TEXT>YECN"
'Response.write objRS_ECN1("ECN_Rev")

Dim j, k, L
Dim NewFileName, OldFileName, ECN_Length, NewChar, FirstChar

If objRS_ECN("ECN_Rev") > "" then

ECNrev=objRS_ECN("ECN_Rev")
OldFileName= objRS_ECN("Drawing Num")

If InStr(ECNrev,objRS_ECN("Cur Rev")) then
Response.Write "<A href=" & FilePath & FileNamePDF & ">hh" & objRS_ECN("DocNum") & "</a>"

else

L = objRS_ECN("ECN_Rev")

response.write L

j = 0

ECN_Length= len(L)

if (ECN_Length > 1) then
'
' We want to find the second character and shift one previous
FirstChar = mid(L,1,1)
NewChar = mid(L,2,1)

k = asc(ucase(NewChar)) - 1

j = chr(k)

dim NewX

If j = "I" then
NewX = "H"
else
If j = "O" then
NewX = "M"
else
If j = "Q" then
NewX = "P"
else
If j = "S" then
NewX = "R"
else
If j = "X" then
NewX = "W"
else
If j = "Z" then
NewX = "Y"
else
NewX = j
end if
end if
end if
end if
end if
end if

NewFileName = OldFileName & FirstChar & NewX & ".pdf"

else
'
' Shift one previous
j = asc(ucase(L)) - 1

If j = "I" then
NewX = "H"
else
If j = "O" then
NewX = "M"
else
If j = "Q" then
NewX = "P"
else
If j = "S" then
NewX = "R"
else
If j = "X" then
NewX = "W"
else
If j = "Z" then
NewX = "Y"
else
NewX = x
end if
end if
end if
end if
end if
end if

NewFileName = OldFileName & chr(j) & ".pdf"

tmpvar = NewFileName

'NewFileName= NewFileName & "<br>"

Response.write "<A href=" & tmpvar & " class=textlink2>" & objRS_ECN("DocNum") & "</a></font></TD>"
end if
end if
end if

Response.Write "</SPAN></td>"

'End PDF FILE

Response.write "<td><input type=hidden size=20 name=DocumentTitle readonly onfocus=blur() value=" & objRS_ECN("Title") & "><SPAN CLASS=TEXT>" & objRS_ECN("Title") & "</SPAN></td>"
Response.write "<td><SPAN CLASS=TEXT><input type=hidden size=6 name=DocumentSize readonly onfocus=blur() value=" & objRS_ECN("Size") & "><DIV ALIGN=CENTER>" & objRS_ECN("Size") & "</SPAN></td>"
Response.write "<td><SPAN CLASS=TEXT><input type=hidden size=6 name=DocumentSheets readonly onfocus=blur() value=" & objRS_ECN("Sheets") & "><DIV ALIGN=CENTER>" & objRS_ECN("Sheets") & "</div></SPAN></td>"
Response.write "<td><SPAN CLASS=TEXT><input type=hidden size=6 name=DocumentCurRev readonly onfocus=blur() value=" & objRS_ECN("Cur Rev") & "><DIV ALIGN=CENTER>" & objRS_ECN("Cur Rev") & "</div></SPAN></td>"

if IsNull(objRS_ECN("Location")) or objRS_ECN("Location") = "" then
Loca = "N/A"
else
Loca = objRS_ECN("Location")
end if

Response.write "<td><input type=hidden size=6 name=DocumentLocation readonly onfocus=blur() value=" & Loca & "><SPAN CLASS=TEXT>" & Loca & "</SPAN></td>"

if IsNull(objRS_ECN("Class")) or objRS_ECN("Class") = "" then
CLAS = "N/A"
else
CLAS = objRS_ECN("Class")
end if

Response.write "<td><input type=hidden size=6 name=DocumentClass readonly onfocus=blur() value=" & CLAS & "><SPAN CLASS=TEXT><DIV ALIGN=CENTER>" & CLAS & "</div></SPAN></td>"

if IsNull(objRS_ECN("OOC")) or objRS_ECN("OOC") = "" then
OOC = "N/A"
else
OOC = objRS_ECN("OOC")
end if

Response.write "<td><SPAN CLASS=TEXT><input type=hidden size=6 name=DocumentOOC readonly onfocus=blur() value=" & OOC & "><DIV ALIGN=CENTER>" & OOC & "</div></SPAN></td>"

'BEGIN ECN FILE SEARCH

Response.Write "<td><SPAN CLASS=TEXT>"

dim ECNum_RS
Dim strSQL_ECN2
dim ECNnum
dim ECNnumFilePath
dim ECNumConn
dim New2String

'New2String = "802654%"

Set ECNum_RS = Server.CreateObject("ADODB.Recordset")
strSQL_ECN2="SELECT * FROM Hanging_ECN WHERE [Drawing Num] like '" & OldFileName & "'"
ECNum_RS.Open strSQL_ECN2, oldPDFConn, 1,3

do until ECNum_RS.EOF

ECNnumFilePath = "http://webocean1/eng_drawings/ECN/"
ECNnum = ECNum_RS("ECN_Num")

'Response.write ECNnum
Response.Write "<input type=hidden size=6 name=DocumentECNS readonly onfocus=blur() value=" & ECNnum & "><A href=" & ECNnumFilePath & ECNnum & ".pdf" &" CLASS=TEXTLINK2 TARGET=_BLANK>" & ECNnum & "</a>" & " "

ECNum_RS.MoveNext

loop

ECNum_RS.close

Response.Write "</SPAN></td>"

'END ECN SEARCH FOR FILES

objRS_ECN.MoveNext
LOOP

objRS_ECN.close

End if

%>

I appreciate your help :)
thx,
Ed

thx,
Ed

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-07 : 08:50:53

If you have Query Analyzer or iSQL window please run this in the window and paste the results of the query.


SELECT	Brp_archive.DocNum
	, Brp_archive.Size
	, Brp_archive.sheets
	, Brp_archive.[Cur Rev]
	, Brp_archive.Location
	, Brp_archive.Class
	, Brp_archive.OOC
	, Brp_archive.Title
	, Hanging_ECN.[Drawing Num]
	, Hanging_ECN.ECN_Rev
	, Hanging_ECN.ECN_Num 

FROM	Brp_archive INNER JOIN Hanging_ECN 
		ON Brp_archive.DocNum = Hanging_ECN.[Drawing Num] 

WHERE Hanging_ECN.[Drawing Num] like'" & SaveFile & "'"

put in an example for the part

like'" & SaveFile & "'"

It would be best if you put in the example you used to get the results in previous post.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 09:49:10

Cut and paste this in to Query Analyzer and tell me if it returns anything


	SELECT COUNT(*)
	  FROM Brp_archive a 
    INNER JOIN Hanging_ECN b 
	    ON a.DocNum = b.[Drawing Num] 
  HAVING COUNT(*) > 1

And spaces in column names are a pain...

Brett

8-)

SELECT POST=NewId()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources