Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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