SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot LSW's Avatar
    Join Date
    Jul 2004
    Location
    Juneau Alaska
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    (+) 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:


    Code:
    ...
    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.
    Thank You, Migwetth, Gunalche’esh, Ha’w'aa, Danke

    Kyle Lamson



  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Was this ASP JScript? (The syntax is not valid for ASP VBScript).
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Zealot LSW's Avatar
    Join Date
    Jul 2004
    Location
    Juneau Alaska
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Cheers
    Thank You, Migwetth, Gunalche’esh, Ha’w'aa, Danke

    Kyle Lamson



  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    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:

    Code:
    SELECT service.field1
         , service.field2
    	 , status.field3
    	 , lwop.field4
    	 , election_trans.fieldX
      FROM service, status, lwop, election_trans
     WHERE service.s = status.ssn(+)
       AND service.s = lwop.ssn(+)
       AND service.s = election_trans.ssn(+)
    that would translate to a more common look of
    Code:
    SELECT service.field1
         , service.field2
    	 , status.field3
    	 , lwop.field4
    	 , election_trans.fieldX
      FROM service
      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
    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.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Zealot LSW's Avatar
    Join Date
    Jul 2004
    Location
    Juneau Alaska
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    http://oracledba.ezpowell.com/oracle/ocp/Joins.html

    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!
    Thank You, Migwetth, Gunalche’esh, Ha’w'aa, Danke

    Kyle Lamson



  6. #6
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL is SQL, whatever version/variation. This is not VBscript or ASP

  7. #7
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webber123456 View Post
    SQL is SQL, whatever version/variation.
    Please do not say that.
    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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •