Fetch into DS (SQL0312)

[FaStOnE]

This literally has me

DS Defined as:

      * Display DS
     d SF1D          e ds                  ExtName( ED365d : Sub1 : *Output )
     d SF1F          e ds                  ExtName( ED365d : Ctl1 : *Input  )

SQL Code:

          Declare SUP Scroll Cursor For
                  SELECT I.VndNoAP                 as VndNoAP,
                         I.EDIID                   as EDIID,
              SubString( L.Vendor_VName, 1, 20 )   as VndName,
              SubString( I.EDICont     , 1, 20 )   as EDICont,
                         I.EDIVPhone               as EDIvPhone,
                         I.Compare                 as Compare,
                         I.AuthCode                as AuthCode
                    FROM InvSup I, DBAPVEN L
                   WHERE Trim( Char( I.VndNoAP ) ) = Trim( L.Vendor )
                     AND I.VndNoAP        =  Case When :fF1 <> 0
                                             Then :fF1
                                             Else I.VndNoAP      End
                     AND L.Vendor_VName Like Case When :fF2 <> ' '
                                             Then Trim( :fF2 )
                                             Else L.Vendor_VName End
                     AND I.EDICont      Like Case When :fF3 <> ' '
                                             Then Trim( :fF3 )
                                             Else I.EDICont      End
                     AND I.EDIvPhone    Like Case When :fF4 <> 0
                                             Then Trim( :fF4 )
                                             Else I.EDIvPhone    End
                     AND I.AuthCode       =  Case When :fF6 <> ' '
                                             Then :fF6
                                             Else I.AuthCode     End
                     AND I.EDIID        Like Case When :fF7 <> ' '
                                             Then Trim( :fF7 )
                                             Else I.EDIID        End

                ORDER BY VndNoAP
          For Fetch Only;

Fetch:

          Exec SQL
          Fetch Next From SUP into :SF1D;

Sub1 Code:

     A  61        VNDNOAP        5Y 0O  8  8EDTCDE(3)
     A N61        EDIID         15   O  8  6
     A            VNDNAME       20   O  8 23
     A  62        EDICONT       20   O  8 45
     A  62        EDIVPHONE     10Y 0O  8 67EDTWRD('   /   -    ')
     A N62        COMPARE        1   O  8 53
     A N62        AUTHCODE       3   O  8 65

I'm getting the SQL0312 Error:

"Variable SF1D not defined or not usable."

Any Suggestions???

[B.Hauser]

Re: Fetch into DS (SQL0312)

I assume the EDTCDE and EDTWRD, which cannot be handled by SQL cause the problem.

Birgitta

[tomholden]

Re: Fetch into DS (SQL0312)

just for the Halibut...try changing SF1D to be *INPUT and see what happens...

personally i think opposite from IBM on this one...*INPUT should work..but then you have other problems to work out

[K2r400]

Re: Fetch into DS (SQL0312)

For me your solution is bad, because you want to do a select directly in a DS as the same as an external screen format.
In the first place I think that it's not possible,
second I think that it's very dangerous, in the case or the fields of the screen are moved (movement below the other one), it will be necessary to modify the SQL statement.

If you don't want to declare an internal data-structure (with LIKE for the field) to receive your data, the best way (for me) is to make your "select * into" in the exterenal DS of the file and make an eval-corr (or several eval for each fields) as we saw it together in another post ( http://www.code400.com/forum/showthread.php?t=8497 ).

[B.Hauser]

Re: Fetch into DS (SQL0312)

For me your solution is bad, because you want to do a select directly in a DS as the same as an external screen format.
In the first place I think that it's not possible,
second I think that it's very dangerous, in the case or the fields of the screen are moved (movement below the other one), it will be necessary to modify the SQL statement.

If you don't want to declare an internal data-structure (with LIKE for the field) to receive your data, the best way (for me) is to make your "select * into" in the exterenal DS of the file and make an eval-corr (or several eval for each fields) as we saw it together in another post ( http://www.code400.com/forum/showthread.php?t=8497 ).

It is never a good idea to use SELECT * FROM!
Because ...

• in most cases a lot of unnecessary information is returned.
• The Optimizer cannot use an Index Only Access (IOA).
• If fields are moved or added the SQL-Statements (or at least the data structures must be changed and the programs recompiled.
• If the new feature "implicitely hidden" is used to hide columns within the SELECT * statement you'll get some trouble, because the hidden fields are not returned, but the (external) data structure contains all columns.

Birgitta

[FaStOnE]

Re: Fetch into DS (SQL0312)

My biggest intent here was to take advantage of the EVAL-CORR to get data into a Data Structure (PFDS). Then EVAL-CORR the values to the Display Screen (DSi) - either as an Update or Add. Write that data to the ScreenFmt. Read the Data into the DSo. EVAL-CORR back into PSDS and write/update to the Table.

With that understanding ... my assumptions were:

1- Create DSi with EXTNAME( File : ScrFmt : *Input )
2- Create DSo with EXTNAME( File : ScrFmt : *Output )
3- Create PFDS with EXTNAME( Table )

Fields defined in ScrFmt as Both Input/Output would be in both DS above.

In simplistic pseudo code... something like

-- For Adding a Record --
Clear DSo
Write ScrFmt DSo
Read ScrFmt DSi

Eval-Corr PFDS = DSi
Write TableRec PFDS

-- For Updating a Record --
Select Into PFDS

Eval-Corr DSo = PFDS

Write ScrFmt DSo 
Read ScrFmt DSi

PFDS = DSi
Update TableRec PFDS

[Jerry G]

Re: Fetch into DS (SQL0312)

Quite late, but for anyone else stumbling upon this page, my lesson from experience/research:

The SQL precompiler can't handle EXTNAME keywords with the 2nd or 3rd parameters. From the manual, "Database Embedded SQL programming (7.1)" (http://publib.boulder.ibm.com/infoce...ringirpg.htm): "The precompiler supports EXTNAME, but does not support EXTNAME(filename : fmtname : fieldtype), where fieldtype is *ALL, *INPUT, *OUTPUT, or *KEY."

I'm often there with Rick w.r.t the SQL precompiler:

[TedHolt]

Re: Fetch into DS (SQL0312)

SQL0312 is the most useless error message in the world. I hate it with a passion. What good is a message that tells you that something's wrong, but not why it's wrong?

I have wasted too much time trying to figure out why the compiler won't accept a variable, usually a host structure. Recently I started a OneNote page with resolutions to SQL0312. I'm hoping that running thru the list of possible causes will save me time in the future.

Great thread resurrection!

[danlong005]

Re: Fetch into DS (SQL0312)

Did you change the compiler option rpgppopt to *lvl2 to try to use extname? That may not help, I don't use EXTNAME generally. I do agree with Ted, UTTERLY useless error message! Just makes me want to throw things when I get that message.

[Jerry G]

Re: Fetch into DS (SQL0312)

SQL0312 is the most useless error message in the world. I hate it with a passion. What good is a message that tells you that something's wrong, but not why it's wrong?

Amen! And "SQL5011" (for data structures) is similar. And you know the precompiler must check umpteen different things in the course of validation, and presumably _could_ issue a very specific error message for each of those conditions. Whoever came up with these messages should be ... well ....

[TedHolt]

Resurrecting this thread yet again!

Today I ran into the same problem -- I had put *INPUT as the 2nd parameter of EXTNAME. I am so thankful for this site and for people who revisit these old topics with the solutions to the problems. Thanks, Jerry G!

[Jerry G]

I'm humbled to get a "Thank you" from someone who's done so much to educate the IBM i community. Thank you, too, Ted ... and everyone who posts on this board. What a great group of people!