Split: Add data via SQL
It is possible to add data into a new spooled file from a database.
This video shows how it is done:
The data is found via an SQL where statement in the split definition. You just need to state *SQL instead of a program like below:
Change Split Definition SPL310D
Split definition name . : DEMO_SQL Description . . . . . . : Add data via SQL Split detail description : all User exit program Program name . . . . . . *SQL *NONE, *SQL, name
Missing record option . 1 1=Send message,2=Use blank Duplicate record option. 1 1=Send message,2=Use any,3=Use blank Exit parameter data 1. Linenbr. 13 Position 57 - 60 6. Linenbr. Position - 2. Linenbr. Position - 7. Linenbr. Position - 3. Linenbr. Position - 8. Linenbr. Position - 4. Linenbr. Position - 9. Linenbr. Position - 5. Linenbr. Position - 10. Linenbr. Position - Return data is to be printed in the following positions 1. Linenbr. 1 Startposition 1 6. Linenbr. Startposition 2. Linenbr. 2 Startposition 1 7. Linenbr. Startposition 3. Linenbr. 3 Startposition 1 8. Linenbr. Startposition 4. Linenbr. Startposition 9. Linenbr. Startposition 5. Linenbr. Startposition 10. Linenbr. Startposition F3=Exit F12=Cancel
|
Via the exit parameters you define what information you want to retrieve from the spooled file. They are later referenced as *V01-*V10 in the SQL where clauses.
For the SQL look up there are two settings:
Missing record option
This defines what should happen, if the look up should happen not to find any matching record with the SQL statements stated.
1=Send message: The job will stop with an error message: APF4024 - Unable to get SQL data for split function. See joblog. The previous message, APF4017 in the joblog explains the exact reason.
2=Use blank: Blanks are inserted, if no matching record is found.
Duplicate record option
This defines what should happen, if the look up finds multiple records, that match the selection.
1=Send message: The job will stop with the error message: APF4024 - Unable to get SQL data for split function. See joblog. The previous message, APF4018 in the joblog explains the exact reason.
2=Use any: Data from one of the matched records is returned.
3=Use blank: Return blanks if multiple records match the selection.
The return data is (like for a user program) the lines and positions in the spooled file in which the returned data will be inserted. In the example above the data will be inserted in line 1, 2 and 3.
When you press Enter this screen will be displayed:
Change Split Definition SPL350D DEMO_SQL SQL return parameters: Pm Field File Library Where 1 TEXT1 TESTFILE APFUDV "NUMBER" = '*V01' 2 TEXT2 TESTFILE APFUDV "NUMBER" = '*V01' 3 TEXT3 TESTFILE APFUDV "NUMBER" = '*V01'
Return Output queue, library, form type and user data: OQ LB FT TEXT1 TESTFILE APFUDV "NUMBER" = '*V01' UD
Note: Exit parameters *V01-*V10 can be used in the where clause.
F3=Exit F10=Prompt F12=Cancel
|
The SQL can add up to 10 fields to the new spooled file. In the example above we take the fields, TEXT1-TEXT3 from the Database - for the record where the field, NUMBER equals the text found in the specific spooled file page.
If you press F10=Prompt, you will get more space for the SQL where clause, and also additional help for the SQL statement:
Split definition . . . . : KSE_PAGE Sequence number . . . . : 0001 Description . . . . . . : all
Return parameter . . . . : 3
Field . . . . . . . . . . NUMBER
File . . . . . . . . . . . TESTFILE Library . . . . . . . . APFUDV
Where . . . . . . . . . . "NUMBER" = 9999
Note: Exit parameters *V01-*V10 can be used in the where clause.
F3=Exit F4=Prompt F12=Cancel
|
With F4 you can prompt for the name of the field. Here you will see a list of the fields in the selected file, and from this list you can select the field you want to insert in the spooled file:
You can also prompt the where part with F4. After you have selected a field, a dummy comparison is inserted to help you to include delimiters when needed:
The selected field is inserted inside “” to ensure, that the field is not mixed with any predefined fields. A field called USER must e.g. be included in double quotes to ensure, that this is not mistaken for the USER profile running the current job.
A dummy compare value is inserted:
For alphanumeric fields a compare with XXXX is inserted:
"TEXT1" = 'XXXX'
For numeric fields a compare with 9999 is inserted:
"NUMBER" = 9999
In this way InterForm400 reminds you when quotes are needed. You just e.g. replace the XXXX or 9999 with the value, that you want to compare with e.g. *V01.
Set Spooled file attributes via SQL
You can add additional text (found via SQL) as described above, but you can also set various spooled file attributes to a value found via an SQL select statement. This is setup in the bottom of the screen:
Change Split Definition SPL350D DEMO_SQL SQL return parameters: Pm Field File Library Where 1 TEXT1 TESTFILE APFUDV "NUMBER" = '*V01' 2 TEXT2 TESTFILE APFUDV "NUMBER" = '*V01' 3 TEXT3 TESTFILE APFUDV "NUMBER" = '*V01'
Output queue, library, form type and user data: OQ LB FT TEXT1 TESTFILE APFUDV "NUMBER" = '*V01' UD
Note: Exit parameters *V01-*V10 can be used in the where clause.
F3=Exit F10=Prompt F12=Cancel
|
The spooled file attributes are:
OQ |
The Output Queue in which the new, split spooled file is to be created. |
LB |
The LiBrary containing the output queue in which the new split spooled file is to be created. |
FT |
The Form Type of the new, split spooled file. |
UD |
The User Data (USRDTA) field of the new, split spooled file. |
In the example above the form type (FT) of the new spooled file will be the data found in the field, TEXT1 for which the field, NUMBER equals the text found in the spooled file. If different values are returned for any spooled file attribute (when comparing the values for one page with the attributes returned for the other pages), a new spooled file will be created for the pages that has the same settings for the spooled file attributes. So this can be used for level break splitting - without the need of a user program.
Spooled file attributes set via the screen above overrules any fixed value set on the final screen of the split definition (SPL310D).
If you run the SQL split definition above for the InterForm400 demo spooled file with the file, APFUDV/TESTFILE having this content:
NUMBER |
TEXT1 |
TEXT2 |
TEXT3 |
|
1001 |
Flower Power |
Sunny side of the street |
1001 Luftballons |
|
1003 |
Woody woodpecker |
If you go down in the woods |
today |
|
1004 |
Dead herring |
I smell something |
fishy |
Then 3 spooled files will be created with the form types:
Dead herri
Flower Pow
Woody wood
(The 10 first characters of TEXT1. Two pages contains 1001 in line 13, position 57 so they will be placed in the same, resulting spooled file).
The contents of the spooled file will look like this:
Display Spooled File File . . . . . : QPRINT Control . . . . . Find . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+ Dead herring I smell something fishy 1 Herring Marine Research Seaweed Street 14 9000 Battleaxe DK-Denmark --------------------- Att: Martin Merman 1004/PMK.20-02-2013 |
