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:

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.
Zach MannApplications Developer/AnalystAsked:
Who is Participating?
 
Gary PattersonVP Technology / Senior Consultant Commented:
INSERT INTO ZACHM.STAND00001 (ITNBR)
SELECT replace(b.baseno, '??', c.color) FROM zachm.baselist b CROSS JOIN zachm.testcolors c
1
 
Kent OlsenData Warehouse Architect / DBACommented:
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
2
 
_agx_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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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:

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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
1
 
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.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
If it still doesn't work, please provide data types and lengths for all of the columns mentioned.
0
 
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.
0
 
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
1
 
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
1
 
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.