Below is the query
************************** Beginning of Data *************
SELECT Distinct
-- Columns
(Case When D.POACCT=1 Then 'A'
Else 'B' End) as LV,
P.MF as MF,
Substr(D.POITEM,4,17) as PN,
I.INAME as PNN,
I.ICLAS1 as I1,
I.ICCTR as CC,
P.GP as GP,
D.POWARE as WH,
Substr(Digits(D.PODATE),4,2) || '/' ||
Substr(Digits(D.PODATE),6,2) || '/' ||
Substr(Digits(D.PODATE),2,2) as ED,
Digits(D.PODATE) as EDSRT,
C.POINIT as OP,
Digits(P.POREF#) as ON,
Digits(D.POLINE) as LN,
D.POUM as UM,
----------------------------------------------------------------------------
(Case When (Locate('S* ED',T.TX)<>1 and Locate('S8 ED', T.TX)<>1
and Locate('CURRENT ETA',T.TX)<>1)
and NOT(Strip(&ED)='*NA') and Locate('x',T.TX)<>50
Then NULLIF (0,0) Else
(Case When D.POACCT=1 Then D.POQTYO Else 0 End) End) as QS,
----------------------------------------------------------------------------
(Case When (Locate('S* ED',T.TX)<>1 and Locate('S8 ED', T.TX)<>1
and Locate('CURRENT ETA',T.TX)<>1)
and NOT(Strip(&ED)='*NA') and Locate('x',T.TX)<>50
Then NULLIF (0,0) Else
(Case When D.POACCT=1 Then 0 Else D.POQTYO End) End) as QA,
---------------- Need to define "AVAIL" quantity
---------------------------
(Case When (Locate('S* ED',T.TX)<>1 and Locate('S8 ED', T.TX)<>1
and Locate('CURRENT ETA',T.TX)<>1)
and NOT(Strip(&ED)='*NA') and Locate('x',T.TX)<>50
Then NULLIF (0,0) Else
(Case When D.POACCT=1 Then D.POQTYO Else 0 End)
-(Case When D.POACCT=1 Then 0 Else D.POQTYO End) End) as QF,
----------------------------------------------------------------------------
(Case When D.POACCT=1 Then 'ETA = ' ||
Char(Date( '20' || Substr(Digits(D.POSHDT),5,2) || '-' ||
Substr(Digits(D.POSHDT),1,2) || '-' ||
Substr(Digits(D.POSHDT),3,2)) + 2 Days)
Else ' ' || Substr(B.BNAME,1,23) || ' ' ||
B.BTRRT1 || ' ' ||
B.BTRRT3 End) as
ET,
----------------------------------------------------------------------------
Substr(Digits(D.POSHDT),1,2) || '/' ||
Substr(Digits(D.POSHDT),3,2) || '/' ||
Substr(Digits(D.POSHDT),5,2) as
SD,
----------------------------------------------------------------------------
(Case When D.POACCT=1 Then 'P' || D.POSTDE
Else 'C' || D.PORECY End) as
ST,
----------------------------------------------------------------------------
Substr((Case When (Locate('S* ED', T.TX)<>1
and Locate('S8 ED', T.TX)<>1
and Locate('CURRENT ETA',T.TX)<>1)
Then ' ' Else '' End) ||
Value(T.TX,' '),1,30) as TX,
----------------------------------------------------------------------------
(Case When Substr(Value(T.TX,Space( 5)),1,5)='S* ED' Then '1'
When Substr(Value(T.TX,Space( 5)),1,5)='S8 ED' Then '1'
When Substr(Value(T.TX,Space(11)),1,5)='CURRENT ETA' Then '1'
When Substr(Value(T.TX,Space( 5)),1,2)='DC' Then '5'
Else '9'
End)
as
TXTP,
----------------------------------------------------------------------------
Digits(Value(T.POLINE,9999)) as
TXLN,
1 as XX
-- Tables
FROM "QTEMP"/"OOBSPN" P Left Outer Join
"QS36F"/"CLASSES" J on ( 'I1' =J.CLCAT
and P.I1 =J.CLCODE),
&OVF/"OOBSMTR" R,
"QS36F"/"MFGR" M,
"QTEMP"/"OVFPOC" C Left Outer Join
"QTEMP"/"OOBSTX" T on (C.POREF#=T.POREF#),
"QTEMP"/"OVFPOD" D,
"QS36F"/"BILLTO" B,
"QS36F"/"ITEM" I
-- Row Selection
WHERE ((Strip(&GP)='ALL') or (Locate(Strip(&GP ),P.GP)=1))
And ((Strip(&MF)='ALL') or (Locate(Strip(&MF ),P.MF)=1)
or (&MF2<>' ' and Locate(Strip(&MF2),P.MF)=1)
or (&MF3<>' ' and Locate(Strip(&MF3),P.MF)=1)
or (&MF4<>' ' and Locate(Strip(&MF4),P.MF)=1)
or (&MF5<>' ' and Locate(Strip(&MF5),P.MF)=1)
or (&MF6<>' ' and Locate(Strip(&MF6),P.MF)=1))
And ((Strip(&I1)='ALL') or (Locate(Strip(&I1 ),P.I1)=1)
or (&I12<>' ' and Locate(Strip(&I12),P.I1)=1)
or (&I13<>' ' and Locate(Strip(&I13),P.I1)=1)
or (&I14<>' ' and Locate(Strip(&I14),P.I1)=1)
or (&I15<>' ' and Locate(Strip(&I15),P.I1)=1)
or (&I16<>' ' and Locate(Strip(&I16),P.I1)=1))
And (( Strip(&RP) ='ALL')
or (Locate(I.IPATH,Strip(&RP)) > 0 ))
And ((&SAM='Y')
or (&SAM='N' and I.ICCTR<>'SAM')
or (&SAM='O' and I.ICCTR ='SAM'))
-- WMB -- or (&SAM='N' and I.IMFGR<>'SAM')
-- WMB -- or (&SAM='O' and I.IMFGR ='SAM'))
-- Join Conditions
And (P.GP =R.GP)
And (P.MF =M.MFGR#)
And (P.POREF#=C.POREF#)
And (P.POREF#=D.POREF# and P.POLINE=D.POLINE)
And (C.POCO# =Digits(B.BCO#) and C.POACCT=B.BACCT#)
or (&SAM='N' and I.ICCTR<>'SAM')
or (&SAM='O' and I.ICCTR ='SAM'))
-- WMB -- or (&SAM='N' and I.IMFGR<>'SAM')
-- WMB -- or (&SAM='O' and I.IMFGR ='SAM'))
-- Join Conditions
And (P.GP =R.GP)
And (P.MF =M.MFGR#)
And (P.POREF#=C.POREF#)
And (P.POREF#=D.POREF# and P.POLINE=D.POLINE)
And (C.POCO# =Digits(B.BCO#) and C.POACCT=B.BACCT#)
And (Substr(D.POITEM, 1,3)=I.IMFGR
and Substr(D.POITEM, 4,4)=I.ICOLOR
and Substr(D.POITEM, 8,9)=I.IPATT
and Substr(D.POITEM,17,2)=I.ISHADE
and Substr(D.POITEM,19,2)=I.ILOT#)
-- Sort Columns
ORDER BY MF,PN,I1,CC,GP,WH,OP,LN,TXLN
***************************** End of Data
************************************
Doreen Boyle - I.T. Systems Specialist
Ohio Valley Flooring
Office: 513 527-9593
Cell: 513 356-6802
doreen.boyle@xxxxxxx
******************************************
From: Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Date: 05/12/2020 04:20 PM
Subject: RE: [EXTERNAL] Query Error
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
Hi Doreen
You don't supply the query - so its not easy to determine the cause
Any way - this is strictly a guess - however you are calculating the
dates, look at downloading idate and use that instead
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of doreen.boyle@xxxxxxx
Sent: Tuesday, May 12, 2020 1:48 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Query Error
There is a CL job that runs on the Power 8 V7R3. It calls a query that
produces a report. The job blows up when it tries to run the query with
the errors below. Value in date, time or timestamp not valid. I'm not
sure how to resolve. The CL and query are kind of lengthy so I didn't
post. I can if required.
Message ID . . . . . . : CPA0701 Severity . . . . . . . : 99
Message type . . . . . : Inquiry
Date sent . . . . . . : 05/12/20 Time sent . . . . . . : 02:11:52
Message . . . . : QWM2701 received by OOBS at 380. (C D I R) This
calls a query
Cause . . . . . : Control language (CL) program OOBS in library QGPL
detected an error at statement number 380. Message text for QWM2701 is:
STRQMQRY command failed.
To procedure . . . . . . . : SEND_MESSAGE
Statement . . . . . . . . . : 4311
Message . . . . : Select/omit error on member OOBSPN.
Cause . . . . . : A select/omit error occurred on member OOBSPN file
OOBSPN
in library QTEMP because the fields in record number 1537, record format
*FIRST, member number 1 have one of the following problems: -- A field
contains decimal data that is not valid. -- A field contains floating
point
data that is not valid. -- A field contains DBCS data that is not valid.
--
A field contains date, time, or timestamp data that is not valid.
Statement . . . . . . . . . : 28
Message . . . . : RUN QUERY command failed with SQLCODE -181.
Cause . . . . . : The SQLCODE is -181. The SQLSTATE is 22007. The DB2
for
IBM i SQL message text for this error is: Value in date, time, or
timestamp
string not valid. Recovery . . . : Check the job log for more
information, correct the error and try the request again.
Doreen Boyle - I.T. Systems Specialist
Ohio Valley Flooring
Office: 513 527-9593
Cell: 513 356-6802
doreen.boyle@xxxxxxx
******************************************
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
midrange.com
