ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to create view using multiple inner join in DB2/400?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to create view using multiple inner join in DB2/400?

    I would like to create VIEW for DB2400 which has multiple inner join. I've managed to create view with single inner join as follows:

    CREATE VIEW MYCOLLECTION/VIEW1 AS SELECT a.ShipVia , b.ShipperID FROM
    MYCOLLECTION/Orders A INNER JOIN MYCOLLECTION/Shippers b ON A.ShipVia = B.ShipperID

    But with multiple inner join gives SQL0104 syntax error as follows:

    Message . . . . : Token was not valid. Valid tokens: ON FULL LEFT CROSS INNER RIGHT USING.

    I've tried to create multiple inner join view using iSeries Navigator as well as using 5250 emulator (STRSQL). But it seems that DB2400 doesn't support creating multiple inner join view. Is it true?

    If not please let me know how to create so.

    Thanks in advance.
    Last edited by ramtirthkar; September 8, 2009, 03:48 AM. Reason: add more information

  • #2
    Re: How to create view using multiple inner join in DB2/400?

    Can you show us your multiple join query?

    Comment


    • #3
      Re: How to create view using multiple inner join in DB2/400?

      Thanks Vernond for your reply:
      Here is my query:

      CREATE VIEW MYCOLLECTION/VIEW2 AS SELECT a.ShipVia , b.ShipperID FROM MYCOLLECTION/Orders A INNER JOIN MYCOLLECTION/ORDERS INNER JOIN MYCOLLECTION/customers ON MYCOLLECTION/Customers.CustomerID = MYCOLLECTION/Orders.Customers

      Comment


      • #4
        Re: How to create view using multiple inner join in DB2/400?

        you join your file ORDERS with ORDERS, but without specifying keys and after you join with Customers.

        For joining several files you need the following syntax:

        PHP Code:
        Select ....
        From File1 Inner join File2 on File1.Key1 File2.Key1 and File1.Key2 File2.Key2
               Inner Join File 3 on File2
        .Key3 File3.Key3 
               Inner Join File n on Filen
        .KeyN File1.KeyN 
        Where 
        ... 
        Birgitta

        Comment

        Working...
        X