SQL AS400

[johnan]

Hi the best. stucking on one SQL:

SELECT CLICFIL222.OBBIP10H.B1CUS, CLICFIL222.MSCML102.CNAME, Count(CLICFIL222.OBBIP10H.B1CPO) AS Antalfsss,Max(substr(digits(CLICFIL222.OBBIP10H.B1TTM), 1, 2) concat ':' concat substr(digits(CLICFIL222.OBBIP10H.B1TTM), 3, 2)) AS MaxTID FROM CLICFIL222.OBBIP10H RIGHT JOIN CLICFIL222.MSCML102 ON CLICFIL222.OBBIP10H.B1CUS=CLICFIL222.MSCML102.CUSNO where CLICFIL222.OBBIP10H.B1TDT = current_date and CLICFIL222.OBBIP10H.B1CUS < '1000' and CLICFIL222.OBBIP10H.B1OTP not in 'KRE' GROUP BY CLICFIL222.OBBIP10H.B1CUS, CLICFIL222.MSCML102.CNAME ORDER BY Max(CLICFIL222.OBBIP10H.B1TTM) DESC

This give me data from CLICFIL222.OBBIP10H, but i even want CLICFIL222.MSCML102.CNAME if  there is no post in table CLICFIL222.OBBIP10H

I have tryed all the Joins but still not hit. I think i'm missing somthing?

CUST 25 have no hit in CLICFIL222.OBBIP10H but remains in CLICFIL222.MSCML102 and i want to show i like table here:

255	CUST 1	37	10:45
245	CUST 2	7	10:45
244	CUST 3	21	10:45
243	CUST 4	26	10:45
232	CUST 5	47	10:45
225	CUST 6	32	10:45
224	CUST 7	39	10:45
253	CUST 8	35	10:43
251	CUST 9	23	10:43
249	CUST 10	27	10:42
248	CUST 11	49	10:42
246	CUST 12	30	10:42
242	CUST 13	39	10:42
241	CUST 14	28	10:41
240	CUST 15	60	10:41
237	CUST 16	50	10:41
235	CUST 17	15	10:41
233	CUST 18	38	10:41
230	CUST 19	50	10:40
229	CUST 20	18	10:40
227	CUST 21	99	10:40
256	CUST 22	7	10:16
257	CUST 23	1	10:14
247	CUST 24	3	10:13
233	CUST 25	0	00:00

[johnan]

Let's get this easier.. 🙂

SELECT CNAME
FROM CLICFIL222.MSCML102
inner JOIN CLICFIL222.OBBIP10H ON B1CUS =CUSNO
where B1TDT = current_date order by CNAME

In table CLICFIL222.MSCML102 i have many post (CName), so i want to show all the post from that table even if it doesn't get hit from OBBIP10H, i just want the names if theres not hits from OBBIP10H