SQL Join on multiple columns?
Can I join from one file to another on more than one column? For example, join from FileA to FileB on OrderNumber.A=OrderNumber.B AND ItemNumber.A=ItemNumber.B? All examples show only one column... I am on an AS400 using RPGLESQL, I need to join from one detail file to another detail file.
Software/Hardware used:
AS400, RPGLESQL,PkMS
Software/Hardware used:
AS400, RPGLESQL,PkMS
ASKED:
April 22, 2010 6:50 PM
UPDATED:
April 25, 2010 8:06 AM
Answer Wiki:
Yes, you can. You have it coded correctly by just using the AND.
Here is a sample of my code:
This is in a source member as I try to use RUNSQLSTM.
That way I can make modifications without having to recompile programs.
====
INSERT INTO CUBRANCH
(CUNO, ORGBRN, ORGNAM, BRNAME, USRPRF)
SELECT '1379', t.ORGBRN, t.ORGNAM, t.BRNAME, 'New Record'
FROM cuBRaNCHt t Exception Join cubranch p
on t.cuno = p.cuno
and t.oRGBRN = p.ORGBRN;
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you. I was trying this in STRSQL and it looks like I was close but I was also using an invalid field name. I assumed the error message was referring to my AND in the Join portion of the statemnt instead of the bad field name.
Can you (or anyone else) recommend a good book to use for SQL? We are moving from the AS400 to Windows 2008R2 so I will need something on SQLSERVER 2008, but I will also need to support the AS400 for some time, so something on AS400 SQL? I don’t even know what RUNSQLSTM is or how to use it, but it looks intriguing!
RUNSQLSTM is a command that executes SQL statements from a source member. It’s handy for storing SQL that you want to save for later use. I sometimes exit STRSQL and save my session in a source member. Then I edit the member to remove all of the excess garbage, leaving just the statements that I want to keep in that member.
You can run a series of statements by separating each statement with a semi-colon. Parms on the command let you specify run-time elements such as error severity — i.e., if you expect an error on one statement, you can have it ignored and the following statements will still run. For example, I might have a CREATE TABLE that I run to create a test table. That might be followed by a series of INSERT statements to create some test data. I usually have a DROP TABLE as my first statement, but I don’t want the series to stop just because the table doesn’t exist and errors out when I try to DROP it.
Sometimes I’ll save statements and edit them to make them neatly readable. I might do that so I’ll have an example of something like JOIN on multiple columns. A month later, I can open that member and see an example that makes sense to me.
Tom
Thank you for the information on RUNSQLSTM.
I can see how it would be very useful for saving examples, right now I take a print key , make notes on it and file it. I like your way a lot better and the examples could be shared by others in the department, too.
I will also give it a try on the statement that I have in the RPGLESQL pgm so that a recompile is not necessary if the SQL statement changes.
RUNSQLSTM is limited in the SQK statements that it can run. The major limitation that confuses most new users is that a SELECT statement is not allowed.
Since a SELECT is intended for defining a set of records for a program (or SQL procedure) to process, there is nothing that RUNSQLSTM can do with a SELECT. I.e., a SELECT by itself will neither display nor print the selected rows. A program must FETCH rows and route them to a display or printer. (STRSQL runs a program that knows what to do with a SELECT, so they are allowed in a STRSQL session.)
If a SELECT is needed for displaying or printing rows or for routing them to an outfile, use a QM query instead. The STRQMQRY command can run SELECT statements from source members because the command-processing program knows what to do.
Tom