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 |
