Plus signs in fields when using a date from a file compared to the month and year function

20 pts.
Tags:
IBM iSeries
iSeries SQL
SQL statements
I have included the complete SQL statement. I keep getting +++ in ontime and percent. select c.totallines, c.ontime, decimal(((c.ontime/c.totallines)*100),15,2) as percent from (select decimal(sum(b.totallines),15,2) as totallines, decimal(sum(b.ontime),15,2) as ontime from (select a.ord as ord, a.line as line, a.custreqdate as custreqdate, a.qtyord as qtyord, a.qtyship as qtyship, a.shipdate as shipdate, a.qtyinv as qtyinv, a.invdate as invdate, a.totallines as totallines, (case when shipdate <= custreqdate and qtyship >= qtyord and shipdate <> 0 and month(date(digits(custreqdate) concat '000000')) = month(curdate()) and year(date(digits(custreqdate) concat '000000')) = year(curdate()) then 1 else (case when invdate <= custreqdate and qtyinv >= qtyord and invdate <> 0 and month(date(digits(custreqdate) concat '000000')) = month(curdate()) and year(date(digits(custreqdate) concat '000000')) = year(curdate()) then 1 else 0 end) end) as ontime from (select integer(avg(jord)) as ord, integer(avg(jline)) as line, integer(avg(jsdte)) as custreqdate, integer(avg(lqord)) as qtyord, ifnull(sum(llsqty),0) as qtyship, ifnull(max(llsdte),0) as shipdate, ifnull(sum(ilqty),0) as qtyinv, ifnull(max(siinvd),0) as invdate, 1 as totallines from erplxusrf/dcol inner join erplxf/ecl on jord=lord and jline=lline and jprod=lprod and jfac=licfac left outer join erplxf/lll on jord=llordn and jline=llolin and jprod=llprod and jfac=llfacl left outer join erplxf/sil on jord=ilord and jline=ilseq and jprod=ilprod left outer join erplxf/sih on ilord=siord and ilcust=sicust and ilinvn=siinvn where jfac='010' and jseq=1 and jsdte between 20100501 and 20100527 and lqord<>0 group by jord,jline,jprod) a) b) c result is... Position to line . . . . . ....+....1....+....2....+....3....+....4....+....5....+....6....+.. TOTALLINES ONTIME PERCENT 3,382.00 +++++++++++++++++++++ +++++++++++++++++++++ ******** End of data ********

Software/Hardware used:
iSeries v5.4.5

Answer Wiki

Thanks. We'll let you know when a new response is added.

I answered my own question…had to change the numeric fields to date fields in the imbeded select statement using date((substr(char(max(jsdte)),1,4) concat ‘-‘ concat substr(char(max(jsdte)),5,2) concat ‘-‘ concat substr(char(max(jsdte)),7,2))) as custreqdate had to do the same with shipdate and invdate. Then had to define the shipdate and invdate as numeric with a different name to compare to 0 in the case statement.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following