Embedded SQL with multiple tables

[Mikjall]

Just a question on the proper syntax of a SQL state when embedding it and using multiple tables (three). I understand SQL and for the most part, the embedding of the SQL (only for a single table), but no so much within the 400 when using multiple tables. For example, when writing SQL using SQL Server or MySQL, I usually write it using joins like this:

SELECT A.Column1, B.Column2, C.Column3
FROM Table1 A,
INNER JOIN Table2 B
ON A.Column2 = B.Column2
INNER JOIN Table3 C
ON B.Column5 = C.Column5
WHERE C.Column4 = "abc"
GROUP BY A.Column1
ORDER BY B.Column2

As you can tell, I take advantage of SQL's shortcut when naming tables (using the A, B, and C). How can I write this when embedding SQL and do I need to, and if so, how I tell it what library the files are in (with my case, they tables are located in one library). I wish to do this using RPG IV code.
Thank you

[DeadManWalks]

Re: Embedded SQL with multiple tables

This statement looks just fine as it is.

1) never , never , never hard code the library names. Did I say never. The library list is all you need.

2) If you have done inbedded before for one table, there realy is no change for more than one. I like to use the "Coalesce" function for fields that are involved in the joined table.


DMW

[littlepd]

Re: Embedded SQL with multiple tables

I like to use the "Coalesce" function for fields that are involved in the joined table.


DMW

But since the joins used here are inner joins, you will never get a "NULL" value in any of the fields because you will always find a record. So you could probably do without the COALESCEs in this instance.

Might I also point out that the default type of join for DB2 SQL is INNER. So unless you're doing it for readability purposes, you don't need to include the word INNER in your statement: JOIN is all you need.

[B.Hauser]

Re: Embedded SQL with multiple tables

Hi,

your statement may work with other databases, but not on the System i.
If you use group by, all fields that are specified without an aggregat function in the select statement must be specified in the group by clause.

SELECT A.Column1, B.Column2, C.Column3
   FROM Table1 A,
       INNER JOIN Table2 B
         ON A.Column2 = B.Column2
       INNER JOIN Table3 C
         ON B.Column5 = C.Column5
   WHERE C.Column4 = 'abc'
   GROUP BY A.Column1, B.Column2, C.Column3
   ORDER BY B.Column2 

Birgitta

[Mikjall]

Re: Embedded SQL with multiple tables

Hi everyone,

Thank you for responding!!! I talked with another programmer and found another way of writing it without using the joins (instead using FROM Table A, Table B, etc; WHERE A.column = B.Column). Personally, when I'm writing SQL code for either MySQL or SQL Server, I prefer using the JOINS. I do have another question on how to write a certain SQL code on the Series i, but I'll post it in a new thread. Thanks again for all your help!!!

Mikjall

[arrow483]

Re: Embedded SQL with multiple tables

I talked with another programmer and found another way of writing it without using the joins (instead using FROM Table A, Table B, etc; WHERE A.column = B.Column).

This is another way of writing inner joins. Works the same.