Embedded SQL in RPGLE hdr, dtl fetch for subfile build

1,380 pts.
Tags:
AS/400 Subfiles
Embedded SQL
Header
SQL Server
SQLRPGLE
I am trying to join a header and a detail file and select all fields into data structures defined as external(filename).



D dswtchdr      E DS                  EXTNAME(wtchdr) D dswtcdet      E DS                  EXTNAME(wtcdet)

C     $CreateCsr    Begsr C/EXEC SQL C+ prepare S1 from :sqlstmt C/end-exec  * C/EXEC SQL C+ declare C1 scroll cursor for S1 C/end-exec C                   EndSr  *================================================================ C     $OpenCsr      Begsr C/exec sql C+ open C1 C/end-exec C                   EndSr                              sqlbase = ‘SELECT * ‘                                              +               ‘from wtchdr inner join wtchdet ‘                      +               ‘ on wtchdr.whcnum = wtchdet.wdcnum ‘                  +               %trim(whereclause)                                      +                    (orderbyclause)                                    ; sqlstmt =%trim(sqlbase)                                               ;

C/exec sql C+ fetch next from C1 into :dswtchdr C/end-exec                                         

 On the fetch next, the header data structure receives all data from the row selected. How do I include the detail?

Thx, Nick

The only way that I can think of quickly that might make it work is to CREATE VIEW over a JOIN of the two files. Then reference the VIEW name in EXTNAME() for a single E DS. You don’t actually need to use the VIEW for anything other than that reference. That  is, you don’t need to SELECT from it if you don’t want to. The pre-compiler will only detect one DS for one SELECT. There is only a single set of columns in the returned result set.

Tom



Software/Hardware used:
AS/400 SQLRPGLE V5R4
ASKED: May 10, 2011  4:46 PM
UPDATED: May 12, 2011  12:34 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I have not been able to find any good examples of what I am trying to do with the create view. I did combine all of the fields from the header and the detail into their own new file to reference as an external data structure. This shows all of the fields.

I may have to revert to logical files, settll/reade and chains to get this done. I hate to. Sql does the selecting so nicely when you figure out how to make it work with each scenario.

Any pointers out there on the Create View? In the process, where does it go? On the Select statement or when setting up the cursor or somewhere else?

I don’t know much about this but enough to not to try a Update or Delete with SQL yet. LOL.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    The CREATE VIEW shouldn't need to be much more than something like this:
    CREATE VIEW mylib/TSTLFJOIN AS SELECT * FROM mylib/wtchdr, mylib/wtchdet
    The only purpose would be to register a file description with the database. Technically, it probably only needs to exist at the time the program is being compiled; but you might want to test for certainty. After you run CREATE VIEW, run DSPFFD mylib/TSTLFJOIN to see the resulting list of columns. Be aware that if you actually try to execute a SELECT statement or a RUNQRY over that VIEW, the database will attempt to pass the data to you. You probably don't really want that to happen. Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    That is kinda what I had.
    sqlbase = 'Create View Claims '       +                               
              'AS SELECT * '                                         +    
                'from wtchdr inner join wtchdet '                      +  
                ' on wtchdr.whcnum = wtchdet.wdcnum '                  +  
                %trim(whereclause)                                      + 
                     (orderbyclause)                                    ; 
    I did a strsql from the command line and did the create view Claims statement like you said. DSPFFD did show all of the fields. To satify my own curiosity(which kills the cat), I did a runqry *n claims rcdslt(*yes). I selected one claim number that I had entered as test data earlier with 3 lines. I received 5 lines. When I looked deeper, it was showing the one header record 5 times for each detail line whether the claim number equaled my selection or not. So, with that, I am thinking that the join .. .on claim number might work. How do I dispose of a View? More background: this is for a search window with 8 fields to search on and build a subfile with the results with a fetch. Part of the fields are in the header, part are in the detail. Part are alpha, part are numeric. Part of the fields on the window will allow inquiry using other programs and functions to lookup a starting point and be returned to the search window. (That is what I have been tweaking out today and it looks good.) Tom, you are the man and thanks a bunch for your help. I know this is so close to working and being wondermus!
    1,380 pointsBadges:
    report
  • TomLiotta
    How do I dispose of a View? For my example, it would simply be "DROP VIEW mylib/TSTLFJOIN". That is kinda what I had. sqlbase = ‘Create View Claims ‘ + I'm not clear on what that is for. Since it's clearly HLL program code, I'm not sure how you will use it. You can't create a view in the same program that needs to compile over the view. I would simply put the CREATE VIEW statement in a QSQLSRC member along with other source. You can execute it with RUNSQLSTM. You might also put the DROP VIEW statement in the same member at the beginning. Put a line with a semi-colon after both statements since multiple statements require terminators. Add comments to explain the purpose. Execute it if the base formats change and you need to compile your program again. A lot can be done just by running different types of "data definition" statements. I am thinking that the join .. .on claim number might work. Initially, the only purpose of this view was to create a record format for the compiler to use. The view itself wasn't intended to accomplish anything but provide a list of columns that would match what your embedded SELECT statement would generate when your program ran. However, it's certainly possible that you could create a real VIEW that does some of the work. It's often better to have code down in the database definitions if you can move it out of program code and it does the work that you need. If it works, then your embedded SELECT statement might be simplified. By having an external VIEW definition, you can influence the rows selected in your program if you recreate the VIEW with different attributes. That is, you can do that as long as you don't change to set of columns in the result set. If those change, the program would need to be recompiled to generate the new DS. Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    That is kinda what I had. sqlbase = ‘Create View Claims ‘ + I’m not clear on what that is for. You can’t create a view in the same program that needs to compile over the view. We, the unknowing....I did not know that. Interesting note: the view shows up on the system as a logical file. So, I will set up a QSQLSRC member to DROP VIEW, CREATE VIEW and execute that if the base formats change. How will I know if the base formats change, programatically? Does the program need to look at that or just add notes in the source on what to do if it suddenly stops working after a data base file change? It doesn't make sense to do it at every execution of the program unless needed. True, the original thing was to put the fields from both files in one format to reference on an external DS. I just picked the 'Create View viewname Select *' out of the manual.
    1,380 pointsBadges:
    report
  • NickHutcheson1
    So, now I have a clp to process runsqlstmt's that will drop view claims, and create view claims AS SELECT * FROM WTCHDR INNER JOIN WTCDET ON WTCHDR.WHCNUM = WTCDET.WDCNUM Looks great !!! I can strsql, SELECT * from claims Order by whcnum and it is correct. Thanks a bunch!!! There is something else going on. That Select is the same thing the program has and it is not picking up any rows in the fetch. SQLCOD = -000000183.
    D claims        E DS                  EXTNAME(claims) inz
    C/exec sql                        
    C+ fetch next from C1 into :claims
    C/end-exec     
    Idears?
    1,380 pointsBadges:
    report
  • NickHutcheson1
    Again, empty date fields. CPF5035 - Data mapping error.
    1,380 pointsBadges:
    report
  • NickHutcheson1
    There should be another way around this. I have changed to files date fields to all contain '1940-01-01' . The SQL works. The rest of this will also go to a new post since we are no longer talking about the fetch E DS to combine a header and detail file. Why will the system not figure out that a date data type format *ISO containing a default value of '0001-01-01' is valid, must be a question for the data base Gods. I know I am not the only one who has ran into this. Is there a option on the create sql program command that I should use? Something on the H spec? A system value?
    1,380 pointsBadges:
    report
  • TomLiotta
    We, the unknowing….I did not know that. In more detail: You can create the view in that program. You just can't expect the view to be available for the compiler since the program won't be run to create the view until after the compile is already finished. Each time the file format changes, the view would be recreated by the running program, but then the program would need to be recompiled to let the compiler update the data structure definition. How will I know if the base formats change, programatically? Three general ways -- either the List Record Formats (QUSLRCD) API or DSPFD TYPE(*RCDFMT) to an outfile can supply the current format level ID which you could compare against a saved copy, or I suppose you could could code a simple CL that declares the file and attempts to receive a record from it to generate a level check. If the format ID from the API or from DSPFD doesn't match, or if the CL throws a level check, you'd want to recompile your main program. You'd also recompile the test CL if you chose to go that route. With the existing example, you have FileA joined with FileB. You can test results by changing it to be the opposite -- FileB joined with FileA, i.e., reverse the positions of the file names. The format level ID should change (unless the two files have the same data types and lengths for each column down the through the records). I would expect format IDs to remain pretty stable. Why will the system not figure out that a date data type format *ISO containing a default value of ‘0001-01-01′ is valid, must be a question for the data base Gods. I know I am not the only one who has ran into this. Is there a option on the create sql program command that I should use? Something on the H spec? A system value? Are the date fields defined as 'null-capable? (Actually, are any of the columns null-capable?) If so, then you'll need to include null-indicator support in your programs. If you code procedures in SQL rather than in RPG (or COBOL or whatever), things are different. "Null" columns are database things. They aren't high-level language (HLL) things. You need to code support for null columns into the program. This is commonly done by creating a set of variables that act as indicators of null values. One null-indicator variable would be associated with each column that has a null-capable attribute. You can usually use DSPFFD to see if any fields allow null values. Easiest is to create an array of 5i 0 variables. The way you have your SELECT coded, this array should have a DIM() value that matches the number of columns in your main DS; but technically you only need as many elements as the number of null-capable columns in the SELECT. With that array defined, your FETCH then becomes:
    C+ fetch next from C1 into :claims:claims_ind
    You have one DS named claims to receive values from the columns. A second DS is named claims_ind to receive indications of null values. (You can name it whatever you choose.) If the third element of claims_ind() is negative, then you can't use the value from the third column of your main DS. If your date is, say, in the sixth column, then you would check claims_ind(6) to see if the date is null. Note that you are using the simplified version of a SELECT statement. You are using "*" to request all columns be returned into your program. If you only requested the columns that you actually wanted to use, things might be simplified overall. The SQL statements themselves would be longer because you would type each column name into the statements. But there would no longer be concerns over someone adding new columns to either table. Your DS would have a fixed set of fields. You could set null-indicators only for individual fields that are null-capable. In any case, first thing to determine is if null values are even involved. If you run a straight SELECT in STRSQL over one of the rows with questionable values, what do you see for the date column? Does it actually show '0001-01-01'? If it does, then you might only need to include a SET OPTIONS SQL statement in your program. Perhaps like this:
    C/EXEC SQL                
    C+ Set Option             
    C+     Datfmt    = *iso,  
    C/END-EXEC
    There might be other options you'll want to add, but you might need to let the SQL pre-compiler know that it should account for ISO date formats when it tries to put values into your program's date variables. Hard to tell without more details. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Correction:
    C+     Datfmt    = *iso
    That shouldn't have had a comma after "*iso". Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following