I tried adding the set option at the beginning of my program.
Exec SQL
SET OPTION DATFMT = *ISO;
But when I tried to compile, it still failed.
The only thing that will work is if I prompt the compile command and
change the date format parameter to *ISO. I prefer to do that inside of
the program when I can because I might forget it the next time I have to
compile the program, but apparently that is the only way the SQL
precompiler will recognize it.
Dawn Siegrist
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Wednesday, May 11, 2011 5:23 PM
To: RPG programming on the IBM i / System i
Subject: 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
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
