Need help looping through two tables in SQL and creating new records for a third table

Zach Mann
Zach Mann used Ask the Experts™
on
I have been tasked with creating a list of possible combinations for standard parts.  

I have a list of standard parts that look something like this:

10.200.12??.000
15.234.1324.0??
81.43234.??00

I also have a list of color codes that are all two digits:
11
QQ
AH

I am wondering if it is possible in SQL and ultimately in a CL program on our i series to generate a list of all possible final part combinations and put them in a table?

In this example:
10.200.1211.000
15.234.1324.011
81.43234.1100
10.200.12QQ.000
15.234.1324.0QQ
81.43234.QQ00
10.200.12AH.000
15.234.1324.0AH
81.43234.AH00

I don't have any experience with loops in SQL and am having trouble making sense of the examples I have found.

I can do this in Visual Basic or other languages but the goal is to have it on a schedule and automated on our job scheduler.  

Any guidance is appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Zach,

It's actually pretty easy.  :)

If we start with both the parts list and the color codes as just rows in existing tables, you could generate the list with something like this:

SELECT replace (parts.name, '??', colors.code) FROM parts CROSS JOIN colors

Open in new window


And then just modify the query to be an INSERT instead of a SELECT statement.

If the colors are "hard coded" the queries can be written as CTEs with the codes defined in the CTE.


Good Luck,
Kent
Most Valuable Expert 2015

Commented:
(no points...)

Nice! This is one of the few good uses of a CROSS JOIN.  Returning all possibly combinations is exactly what they're made for.
Zach MannApplications Developer/Analyst

Author

Commented:
Thank you for the prompt response Kent.  I had not considered using a CROSS JOIN and it appears that it will be much easier than anticipated because of it.  I am still having trouble getting the SQL to work though.

The message I received is:

RUNSQL SQL('INSERT INTO ZACHM.STAND00001 (ITNBR) VALUES(REPLACE (B.BASENO, ''??'', C.COLOR) FROM ZACHM.BASELIST B CROSS JOIN ZACHM.TESTCOLORS C)') COMMIT(*NONE) NAMING(*SQL)
                                                 
Token REPLACE was not valid. Valid tokens: (.                            
RUNSQLSTM or RUNSQL command failed.                                      

Any ideas on how to fix this?

Thanks again for the help.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

DB2 functions are very demanding about parameter types.  IBM implements most functions by overloading the function name so that they can accept a broad range of types.

Check the parameters being passed to REPLACE and makes sure that all of them are a character type (CHAR, VARCHAR).

iSeries SQL is different from LUW and ZOS SQL.  But it looks like the query should SELECT the items to insert not generate them with the VALUES clause.
VP Technology / Senior Consultant
Commented:
INSERT INTO ZACHM.STAND00001 (ITNBR)
SELECT replace(b.baseno, '??', c.color) FROM zachm.baselist b CROSS JOIN zachm.testcolors c
Gary PattersonVP Technology / Senior Consultant

Commented:
REPLACE syntax looks ok - VALUES clause was just invalid, and that was the first token in the bad values clause.
Gary PattersonVP Technology / Senior Consultant

Commented:
If it still doesn't work, please provide data types and lengths for all of the columns mentioned.
Zach MannApplications Developer/Analyst

Author

Commented:
Thank you Gary!  I still have a problem with some repeated numbers being in there because not all parts contain '??' but that will be easy for me to deal with.
Gary PattersonVP Technology / Senior Consultant

Commented:
Happy to help Zach.  Transitioning from traditional programming languages to SQL took me a while, but once you get the hang of it, SQL can really simplify the development process - as you can see from this example.  

Here is a simple modification to limits results to only rows that have the '??' literal in the BASENO column:

INSERT INTO ZACHM.STAND00001 (ITNBR)
SELECT replace(b.baseno, '??', c.color) FROM zachm.baselist b CROSS JOIN zachm.testcolors c
WHERE b.baseno like '%??%'

The LIKE predicate allows simple pattern matching.  "%" represents zero or more characters of any value (like an "*" is used in Windows) and an underscore "_" represents a single character (confusingly, like the '?' wildcard in Windows).  In IBM i DB2 SQL, "?" is not a special character in a LIKE comparison, so '%??%' represents any string containing '??'.  The following would all match:

??
??123
xyz??
xyz??123
Zach MannApplications Developer/Analyst

Author

Commented:
Would this still bring in the values without '??' in them?

I was thinking of trying to add DISTINCT after SELECT but haven't had a chance to yet,  I need all of the static values in there in addition to the created ones.
It will create a row for every combination.  If '??' doesn't exist in a row in baselist, the item will appear exactly as it appears in the database.
Gary PattersonVP Technology / Senior Consultant

Commented:
Initial query (no WHERE) will produce duplicates for rows without ?? in baseno.  CROSS JOIN joins each row in the baselist to each row in testcolors.  REPLACE will do nothing to the rows with no ??, so you'll get duplicates (one for each row in testcolors) for each row in baselist that doesn't contain '??'.

Adding the WHERE clause would filter out rows from the result set for items without "??" in the baseno, so items in baselist without ?? would all be omitted from final result set.  

Adding DISTINCT to the initial query (no WHERE) should solve the problem:  it will produce a list with no duplicates.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial