No question this time, just two things I wanted to post here to remember, and for later refference for MOD builders.
Some differences between the SQL syntax Oracle 8.1.5.0.0 is using and the syntax that is being used so far:
If you do a self-join you can't use this syntax:
SELECT FIELD1, FIELD2 FROM MEMBERS, MEMBERS AS MEMBER_1
WHERE MEMBERS.FIELD3 = MEMBERS_1.FIELD4
There are two things that need to be changed:
1) Oracle doesn't support the AS for tablenames in a self-join
2) Both tables need to have an alias for this to work.
So the correct syntax would be:
SELECT FIELD1, FIELD2 FROM MEMBERS MEMBERS_1, MEMBERS MEMBER_2
WHERE MEMBERS_1.FIELD3 = MEMBERS_2.FIELD4
A way to make the sql-statement work for all is to add this in config.asp:
if strDBType = "oracle" then
strAs = " "
else
strAs = " AS "
end if
and then use:
"SELECT FIELD1, FIELD2 FROM MEMBERS " & strAs & " MEMBERS_1, MEMBERS " & strAs & " MEMBERS_2
WHERE MEMBERS_1.FIELD3 = MEMBERS_2.FIELD4"
ofcourse with the strTablePrefix / strMemberTablePrefix added if you use it in the actual forum-code.
Just remember NOT to use strTablePrefix for the alias names !
So it would be:
strSql = "SELECT FIELD1, FIELD2 FROM "
strSql = strSql & strMemberTablePrefix & MEMBERS " & strAs & " MEMBERS_1, "
strSql = strSql & strMemberTablePrefix & MEMBERS " & strAs & " MEMBERS_2 "
strSql = strSql & " WHERE MEMBERS_1.FIELD3 = MEMBERS_2.FIELD4"
There shouldn't be strTablePrefixes used in the WHERE condition since you are using the aliasses and not the actual table names.
Pierre<
Forum Locked
Posted - 11 March 2001 : 04:25:30
