(+) in ASP Classic SQL
I am starting a rewrite of a ASP-Classic application into Java.
First off I cannot believe what I am seeing... 1 page with two massive SQL statements... as in 89 lines long! Including 17 sub-selects!
Bad enough, but aside from not being able to follow 17 sub-selects, I find this at the end of the last sub-select:
service.s = status.ssn(+) AND
service.s = lwop.ssn(+) AND
service.s = election_trans.ssn(+);
This may be old style SQL, or maybe due to the classic ASP, I don't know, but I cannot find out what the (+) at the end does. No one in the office seems to know either. So anyone have an idea?
(Due to security issues I cannot paste all the code.)
Thanks in advance.
Was this ASP JScript? (The syntax is not valid for ASP VBScript).
It is VBScript, but this is just a sample from the SQL, 3 of the full 89 lines. This is without the line breaks and such as I am formatting it to go into Java. I really don't think that the (+) is VB or ASP as it is in the SQL block, but I have not seen it in any other SQL in any other Applications I have worked on re-writing, so I am at a loss as to what it would mean. Just hoping some "Old Timer" with experience in the Older forms of SQL might recognize it.
Is this Oracle? It might be a common shorthand, but I found this link which says the (+) indicates which field is the outer join in the statement. In other words, in your case, you must have the service.s value, but you may or may not have the status.ssn, lwop.ssn or election_trans.ssn, so the values of the fields selected from those tables would be NULL
I'm guessing you have a query like this:
that would translate to a more common look of
FROM service, status, lwop, election_trans
WHERE service.s = status.ssn(+)
AND service.s = lwop.ssn(+)
AND service.s = election_trans.ssn(+)
I'll be honest, I used to be the master of the shorthand SQL statements, and it took me FOREVER to transition to the INNER/OUTER JOIN syntax, but even I wouldn't have used an ugly shorthand like this. That's very confusing..... :shifty:
LEFT OUTER JOIN status ON service.s = status.ssn
LEFT OUTER JOIN lwop ON service.s = lwop.ssn
LEFT OUTER JOIN election_trans ON service.s = election_trans.ssn
LOL - I should have looked again earlier Dave, but that is what I came up with too. This is fact the original Oracle proprietary format equivalent to an “Outer Join”, as you say, before they went to the ANSI standard we use today.
My problem was thinking only in terms of the "languages", ie. it must be SQL or ASP (VBScript) and not considering it being Oracle specific until someone mentioned that. Yes I was confused and add that to an SQL query spread over 89 lines, using 17 sub-selects, no schema, just table names when we have over 20 schemas in all. No "AS" before the aliases... everything I have been taught not to do this mess has, but I guess it is the norm for that period. No idea when it was really written, but I guess they used Oracle8i when it was new until Oracle 10g was was already standard which is when I joined the group. So such code is all over the place here still and why we are finally re-writing everything.
I really appreciate both of you taking some time for this! Happy Holidays and Cheers!
SQL is SQL, whatever version/variation. This is not VBscript or ASP
Please do not say that.
Originally Posted by webber123456
SQL for Oracle and SQL for MS SQL is very different and sometimes what perfectly working for one gives you error in another, not the mentioning SQL for MySQl or Access...
And do not let me start on differences in data types there...