Understanding EVAL-CORR and actually using it

[FaStOnE]

OK. This isn't a tutorial nor a complaint. It's more of a "this is what I'm figuring out" and "is this really correct"...

(Also remember, I'm having to do this in Structured RPG, not /Free...

For simplistic definition, consider the following:

InvTable
ShipID
Whse
Invoice
InvDate

You don't use F-Specs and define the fields to the program as:

     d Inv           e ds                  ExtName( InvTable  ) Qualified Inz

You have a DDS Screen with the following:

     A          R SCR1 
     A            SHIPID             B 13 50
     A            INVOICE            B  5 18
     A            WHSE               B 14 62
     A            INVDATE            B  6 18

So, you create an entries to take care of the Display screen:

     d Scr1o           ds                  LikeRec( Scr1 : *Output )    Inz
     d Scr1i           ds                  LikeRec( Scr1 : *Input  )    Inz

Somewhere in your code, you want to get the information into your screen vars and display/update them. Logic says, just "Select" them into your Screen Data Structure, Scr1o.. as in:

Select ShipID, Invoice, Whse, InvDate 
   into :Scr1o 
 From InvTable

But, the SQL Pre-compiler doesn't like this and tells you that "SCR1O is not defined or not useable"

So, from what I've found, you can create another DS as:

     d InvDS           ds                  Qualified
     d  ShipID                             Like( Inv.ShipID ) Inz
     d  Invoice                            Like( Inv.Invoice ) Inz
     d  Whse                               Like( Inv.Whse ) Inz
     d  InvDate                            Like( Inv.InvDate ) Inz

Change your Select Statement to:

Select ShipID, Invoice, Whse, InvDate 
   into :InvDS 
 From InvTable

BTW, you "COULD" do a Select * into :Inv, but Biggs told me not to...

Then you can do an EVAL-CORR as in:

     c                   Eval-Corr Scr1o = InvDS

and proceed with your coding as in:

...
     c                   Write     Scr1          Scr1o
     c                   Read      Scr1          Scr1i
...

OK, all this works and the DataFlow is actually pretty nice. There are a reduced number of "Eval DspF1 = F1" statements and you can format data to your desired sizes etc just with the EVAL-CORR statement alone.

My curiosity here is why the "Select..Into :Scr1o" isn't allowed past the SQL Pre-Compiler? The Data Structure is defined and is available .. just not a direct derivative of an External Definition.

Discussion, corrections or clarifications are welcome... this is just what I'm discovering as I move through the circle of SDLC as we know it.

[MichaelCatalani]

Re: Understanding EVAL-CORR and actually using it

So, from what I've found, you can create another DS as:

     d InvDS           ds                  Qualified
     d  ShipID                             Like( Inv.ShipID ) Inz
     d  Invoice                            Like( Inv.Invoice ) Inz
     d  Whse                               Like( Inv.Whse ) Inz
     d  InvDate                            Like( Inv.InvDate ) Inz

Change your Select Statement to:

Select ShipID, Invoice, Whse, InvDate 
   into :InvDS 
 From InvTable

BTW, you "COULD" do a Select * into :Inv, but Biggs told me not to...

Instead of redefining the fields in the InvDS, you could create it with LikeDS from Inv so that you dont have to specify the individual fields.

I dont think I have ever tried to go SQL directly into a display file DS. One issue with that MAY be that the display file DS is using different numeric types frm the file DS, and SQL doesnt like that. I'm not sure that would cause a problem or not.

[FaStOnE]

Re: Understanding EVAL-CORR and actually using it

I could be wrong, but doing the LikeDS for INVDS would error out on the Select statement, unless I was doing a "SELECT *". Then, I could just use the INV Data Structure and not even create the INVDS. I guess you're correct in the display file attribute changes. Maybe that's the caveat that is overtaken by the SQL Pre-Compiler to not allow that to happen.

Good feedback there...

[K2r400]

Re: Understanding EVAL-CORR and actually using it

You can use this too :

 d Scr1x           ds                  LikeRec( Scr1 : *all    )    Inz

...

c                   Write     Scr1          Scr1x
c                   Read      Scr1          Scr1x

[MichaelCatalani]

Re: Understanding EVAL-CORR and actually using it

I could be wrong, but doing the LikeDS for INVDS would error out on the Select statement, unless I was doing a "SELECT *". Then, I could just use the INV Data Structure and not even create the INVDS. I guess you're correct in the display file attribute changes. Maybe that's the caveat that is overtaken by the SQL Pre-Compiler to not allow that to happen.

Good feedback there...

Hmmm, maybe I was misunderstanding your original problem. You should be able to select individual fields into the external data structure without a problem. You should be able to use either select * or Select ShipID, Invoice, Whse, InvDate. In fact, if you specify an external data structure, and dont select all of the fields, the non-selected fields in the DS will be blank / zeros.

The reason I would use Select * is so that if you ever modify your display file to add additional fields from the file, then all you have to do is to recompile the program and the fields will be picked up automatically. If you select the individual fields in your SQL statement, then adding fields on your display file would require you to also modify Select statement in your program.

[FaStOnE]

Re: Understanding EVAL-CORR and actually using it

OK.. Agreed I should be able to select fields into an External DS without problem --
Per Biggs, it was stated that I shouldn't "necessarily" use the "Select *" that performance was better using the defined field names. I totally agree concerning the addition of fields to the screen by just recompiling. Just had that happen actually. But, maybe it was an alternate personality that performed the work, but I thought that the Select Into Data Structure had to match the Selected Column definition. Will have to further test/validate this theory.

-BUT- (and yes, there's usually a big one somewhere)
The original issue here is doing the Select (either "*" or By field definition) directly into a LIKEREC of the Screen Record. In definition from above, the SCR1 screen Data structure Scr1o. That's where the, for a lack of better terms, - extra step - of creating the LIKEDS of the Table Data Structure (either complete or specific field defined) for Selecting into and doing the EVAL-CORR comes into place.

I'm sure this is as clear as mudd... but, it might help someone utilizing this method in the future. I totally agree with the use of the EVAL-CORR and the power it brings to coding. I was just bum-fuzzled about the lack of being able to utilize the LIKEREC Data Structure for the SQL Statements.

[MichaelCatalani]

Re: Understanding EVAL-CORR and actually using it

OK.. Agreed I should be able to select fields into an External DS without problem --
Per Biggs, it was stated that I shouldn't "necessarily" use the "Select *" that performance was better using the defined field names. I totally agree concerning the addition of fields to the screen by just recompiling. Just had that happen actually. But, maybe it was an alternate personality that performed the work, but I thought that the Select Into Data Structure had to match the Selected Column definition. Will have to further test/validate this theory.

-BUT- (and yes, there's usually a big one somewhere)
The original issue here is doing the Select (either "*" or By field definition) directly into a LIKEREC of the Screen Record. In definition from above, the SCR1 screen Data structure Scr1o. That's where the, for a lack of better terms, - extra step - of creating the LIKEDS of the Table Data Structure (either complete or specific field defined) for Selecting into and doing the EVAL-CORR comes into place.

I'm sure this is as clear as mudd... but, it might help someone utilizing this method in the future. I totally agree with the use of the EVAL-CORR and the power it brings to coding. I was just bum-fuzzled about the lack of being able to utilize the LIKEREC Data Structure for the SQL Statements.

A couple of points:

1) I tested a little bit further with going directly into a display file DS, and couldnt get it to work. ( Even by only using character fields on the display, which should have eliminated any problem with numeric field definitions that I thought may have been an issue.) I believe it SHOULD work, but for some reason the SQL pre-compiler is not picking up data structures based upon display file record formats. The good news is that all thats needed is a single eval-corr statement to get around the problem.

2) As far as performance of using Select *, this is a case of where I would tend to use this method. The benefit of readability and future display file changes being automatically handled in the program in what basically is a single record retrieve far outweigh any performance hit.