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

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:


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

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:

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.
Zach MannApplications Developer/AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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 (, '??', 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,
(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/AnalystAuthor 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:

Token REPLACE was not valid. Valid tokens: (.                            
RUNSQLSTM or RUNSQL command failed.                                      

Any ideas on how to fix this?

Thanks again for the help.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Kent OlsenDBACommented:
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.
Gary PattersonVP Technology / Senior Consultant Commented:
SELECT replace(b.baseno, '??', c.color) FROM zachm.baselist b CROSS JOIN zachm.testcolors c

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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/AnalystAuthor 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:

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:

Zach MannApplications Developer/AnalystAuthor 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.
Kent OlsenDBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.