× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Doreen
The problem is (I think) this piece of code
(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,
If this is in fact an invalid date, then that code just stops in error
you can download iDate from
www.think400.dk/downloads.htm

This will "ignore" such errors and continue without a problem


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: Wednesday, May 13, 2020 7:35 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Query Error

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.