Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Need some HELP

  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Unanswered: Need some HELP

    What is the equivalent between DB2 JOIN TABLE and MS SQL :

    In DB2:

    Code:
    select * from
    table1 t1 
    JOIN TABLE
    (select t2.* 
       from table2 t2
     where t2.clmn2 = t1.clmn1
    and t2.clmn3 = t1.clmn3) tt
    on  1 = 1
    Show me how it'll look in MS SQL .

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That cannot be how joins are done in DB2. Do you know who wrote that SQL?

    I can show you how to do as close to that in SQL Server (i.e. with as few changes as possible) but frankly anyone who read it would be thinking "WTF....?".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft SQL Server processes JOIN syntax just like DB/2 does.

    Code:
    SELECT *
       FROM table1 AS t1
       JOIN table2 AS tt
          ON (tt.clmn2 = t1.clmn1
          AND tt.clmn3 = t1.clmn3)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Poots: I use zOS DB/2 all of the time, and I've seen some doozies of SQL syntax go through it. The example above is by no means representative of "normal" DB/2 syntax.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah, it is possible to do pretty much the same in SQL Server
    Code:
    select * from
    table1 t1 
    JOIN
    (select t2.* 
       from table2 t2
     where t2.clmn2 = t1.clmn1
    and t2.clmn3 = t1.clmn3) tt
    on  1 = 1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by pootle flump View Post
    Yeah, it is possible to do pretty much the same in SQL Server
    Code:
    select * from
    table1 t1 
    JOIN
    (select t2.* 
       from table2 t2
     where t2.clmn2 = t1.clmn1
    and t2.clmn3 = t1.clmn3) tt
    on  1 = 1
    When I tryed I got the error message:
    Msg 156, Level 15, State 1, Line 105
    Incorrect syntax near the keyword 'table'.
    Msg 102, Level 15, State 1, Line 112
    Incorrect syntax near 't1'.

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version of SQL Server are you attempting to use Lenny? Post the results from:
    Code:
    SELECT @@version
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the error messages that Lenny posted, this is from a much larger script. I suspect that we're trying to figure out a syntax problem that is actually outside of the code snippet that has been posted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I think Lenny ran the code he originally posted and had not noticed that the code I posted was different.
    Lenny - copy and paste exactly what I posted and try that. However, I will repeat that although it is a literal translation of the code you posted it is not good SQL by any measure.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    I have this error on any size of the script.
    Version:
    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

    Even on this dummy example I have the same error:

    Code:
    SELECT clm1
    from table 
    (select '123' clm1 where 1 = 1) t2
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'table'.
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 't2'.


    Thanks

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your example doesn't work in either DB2 or in MS-SQL. Try:
    Code:
    SELECT clm1
       FROM master.dbo.sysobjects 
       JOIN (SELECT '123' AS clm1
          WHERE 1 = 1) AS t2
          ON (1 = 1)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by Pat Phelan View Post
    Your example doesn't work in either DB2 or in MS-SQL. Try:
    Code:
    SELECT clm1
       FROM master.dbo.sysobjects 
       JOIN (SELECT '123' AS clm1
          WHERE 1 = 1) AS t2
          ON (1 = 1)
    -PatP
    This in not correct example to me. Because the idea to get something outside and than use inside of the joined to it table.

    Sorry.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lenny77 View Post
    Even on this dummy example I have the same error:

    Code:
    SELECT clm1
    from table 
    (select '123' clm1 where 1 = 1) t2
    remove "table"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about explaining what you want to do in English instead of giving us a non-functioning piece of code and having us guess what you want it to do?

    If nothing else, post the SQL that you'd use in DB/2 and I'll happily translate it to Microsoft SQL. I go both ways several times every day, this isn't any problem for me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by Pat Phelan View Post
    How about explaining what you want to do in English instead of giving us a non-functioning piece of code and having us guess what you want it to do?

    If nothing else, post the SQL that you'd use in DB/2 and I'll happily translate it to Microsoft SQL. I go both ways several times every day, this isn't any problem for me.

    -PatP
    This is good idea !

    Please, translate this code:

    Code:
    select t2.clm2 
    from 
    (select '123' clm1 from sysibm.sysdummy1) t1
    join table
    (select t1.clm1 || '345' clm2 from sysibm.sysdummy1) t2
    on 1 = 1
    Result has to be:

    CLM2
    123345

    Thanks !

Posting Permissions

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