RE: SQL5011 - Why am I getting this?

From the Embedded SQL programming manual:

If the date and time format and separator of date and time subfields within the host structure are not the same as the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET OPTION statement), then the host structure array is not usable.

You are using two date fields in the data structure. Could this be the problem?

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Siegrist, Dawn (Penske)
Sent: Wednesday, May 11, 2011 5:00 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL5011 - Why am I getting this?

I am trying to run an SQL statement to load 4 records into a multiple
occurrence data structure. According to what I see in the manuals, I
have everything set up correctly, but it is giving me an SQL5011 - Host
structure array HVWEEKDS not defined or not usable. I can't seem to
figure out how to fix this. Does anyone have any ideas?



Here is my code.



//----------------------------------

// Define Entry ProtoType

//----------------------------------

D EntryList PR ExtPgm('PMSBCUR2R')

D 4

D 2

D #PmWeekRcds Likeds(PmWeekDS) Dim(4)



//----------------------------------

// Main Procedure Interface

//----------------------------------

D EntryList PI

D PiDist 4

D PiLoc 2

D PmWeekRcds Likeds(PmWeekDS) Dim(4)



//---------------------------------------------------------

// Stand Alone Fields

//---------------------------------------------------------



D $$Corp S 4

D $$Flag S 1

D $$Dnam S 24

D $$Admn S 4

D $$Regn S 4

D $$Area S 4

D $$Cdis S 4

D NumRows S 3P 0

D RowCount S 3P 0



//---------------------------------------------------------

// Data Structures

//---------------------------------------------------------

D PmWeekDs DS Inz

D pwWeekFrom 10

D pwOverDays 7P 0

D pwOverMtrs 7P 0

D pwIncpOver 7P 0

D pwTotFleet 7P 0

D pwTotDue 7P 0

D pwDefectPct 3P 0

D pwCurrPct 3P 0

D pw4weekPct 3P 0



D hvWeekDs DS Occurs(4) Inz

D hvWeekFrom D

D hvWeekTo D

D hvOverDays 7P 0

D hvOverMtrs 7P 0

D hvIncpOver 7P 0

D hvTotFleet 7P 0

D hvTotDue 7P 0

D hvDefectPct 3P 0

D hvCurrPct 3P 0



//---------------------------------------------------------

// Prototypes

//---------------------------------------------------------



D SPV1LOCN PR ExtPgm('SPV1LOCN')

D $$Corp 4

D PiDist 4

D PiLoc 2

D $$Flag 1

D $$Dnam 24

D $$Admn 4

D $$Regn 4

D $$Area 4

D $$Cdis 4



//---------------------------------------------------------

// M A I N P R O G R A M

//---------------------------------------------------------



/FREE



// Get district information to use in query



$$Corp = '*ANY';

SPV1LOCN ($$Corp :PiDist :PiLoc :$$Flag :$$Dnam :$$Admn

:$$Regn :$$Area :$$Cdis);



// Get last 4 weeks of records for location from weekly

// PM defect summary file



Exec SQL

Declare SummCsr Cursor For

Select START_DATE,

END_DATE,

OVR_DAYS_CNT,

OVR_MTRS_CNT,

INCOMPLETE_CNT,

TOT_FLEET,

TOT_DUE,

TOT_DEF_PCT,

PCT_FLEET

from PMSSUMMWF

where CORP = :$$Corp and

AREA = :$$Area and

CTRL_DIST = :$$Cdis and

DIST = :PiDist and

LOCN = :PiLoc

order by END_DATE desc ;



Exec SQL

Open SummCsr;



Exec SQL

Fetch SummCsr For 4 Rows Into :hvWeekDs;



NumRows = 0;

For RowCount = 4 downto 1;

%Occur(hvWeekDs) = RowCount;

If ( hvWeekFrom <> *Loval );

NumRows += 1;



//Load data to data structure to send to caller

pwWeekFrom = %Char(hvWeekFrom : *USA);

pwOverDays = hvOverDays;

pwOverMtrs = hvOverMtrs;

pwIncpOver = hvIncpOver;

pwTotFleet = hvTotFleet;

pwTotDue = hvTotDue;

pwDefectPct = 100 - hvDefectPct;

pwCurrPct = 100 - hvCurrPct;



// Get 4-week rolling currency percentage

Exec SQL

Select PCT_FLEET

Into :pw4weekPct

From PMSSUMM4WF

Where CORP = :$$Corp and

AREA = :$$Area and

CTRL_DIST = :$$Cdis and

DIST = :PiDist and

LOCN = :PiLoc and

END_DATE = :hvWeekTo;



pw4weekPct = 100 - pw4weekPct;

PmWeekRcds(RowCount) = PmWeekDs;

EndIf;

EndFor;



// Send results back - used if called from stored procedure

Exec SQL

SET RESULT SETS ARRAY :PmWeekRcds FOR :NumRows ROWS;



Return;



Thank you.



Dawn Siegrist

Penske

Systems Analyst



T 610-775-6243

F 610-603-8402

E Dawn.Siegrist@xxxxxxxxxx <mailto:Dawn.Siegrist@xxxxxxxxxx>

GoPenske.com



PO Box 563

Reading PA 19603-0563 USA



Check out our blog for news and event info:

Blog.GoPenske.com