ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using STRSQL to populate a DDS file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using STRSQL to populate a DDS file

    Hi, I have a file that I want to populate using a SQLRPGLE program. To test it, I am using STRSQL and the select statement that will be in the program. The selection criteria pulls a total of 16 fields from a couple of files, but the DDS file has 17 fields. I want the last field to be blank when I run the SQL. (It will be populated later) However, STRSQL fails saying "Query output definition does not match file". How can I use SQL Select to populate part of a file? Is there a way to force a blank value in a Select statement? The last field is defined as ALWNULL in the DDS.

    TIA, Kevin

  • #2
    select '' to just get a blank, add the '' to where that extra field is on the insert field list. If you don't care if the extra field is null then don't specify on the Insert portion of the statement where you list the fields being inserted.

    Comment


    • #3
      Thanks for the response Scott. However, I still get the response that the query definition doesn't match the file layout. I have tried leaving the field off the list, putting 2 single quotes together, 2 double quotes together, a pair of single quotes with a space, double quotes with a space, etc. Here is my (current) SQL statement:
      SELECT cast(substr(ctdref,4,7) as decimal(10,0)) ponumb, cthtlc
      locn, CTHCID, CTDSKU, CTDUPC, CTDBQT cs_pk, CTDSQT cs_qty, ctdsqt
      units, CTDSTS cs_sts, CTHCDT, CTHEDT, CTHCAR, CTHBOL, nhrucn,
      nhrcvr, nhvnum, ''
      FROM mm4r6dvl/RCVCTH a, mm4r6dvl/rcvctd b,
      mm4r6dvl/asnhdr c WHERE cthcid=ctdcid and CTHTLC in (select whsloc
      from @ediwhs) and cthbol=nhbol and cast(substr(ctdref,4,7) as
      decimal(10,0))=nhpono
      order by ponumb,ctdsku,cthcid

      The empty field is the last field in the file - a total of 17 fields including the empty one.

      Comment


      • #4
        Hold the phone! It may not work using STRSQL but it does appear to work using SQLRPGLE! Thanks again Scott M, I appreciate the help.

        Comment

        Working...
        X