zOs/SQL/YDIT002R
$#@
call sqlConnect dbof
$@inp
$do qq=1 to 5 $@[
$=f=- 1 + (qq // 2)
sql = space( $-@[
if $f=1 then $@=[ origSubSel
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
DI00206, DI00207
from oa1p.VDI002A1A A
WHERE DI00201 <= ? AND DI00201 =
( SELECT MAX ( DI00201 )
FROM oa1p.VDI002A1A
WHERE DI00201 <= ?
AND DI00202 = A.DI00202 AND DI00203 = A.DI00203
AND DI00204 >= ? AND DI00205 = A.DI00205
)
AND DI00202 = ? AND DI00203 = ' '
AND DI00204 >= ? AND DI00205 = ?
$]
else if $f=2 then $@=[ fetFi
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
DI00206, DI00207
from oa1p.VDI002A1A A
WHERE DI00201 <= ?
AND DI00202 = ? AND DI00203 = ' '
AND DI00204 >= ? AND DI00205 = ?
order by di00201 desc
fetch first 1 row only
$] $], 1)
$** say sql
fun = word(sql, 1)
call sqlQueryPrep 3, subWord(sql, 2), ':m.FE0, :m.fe1, :m.fe2, :m.fe3',
', :m.FE4, :m.fe5, :m.fe6, :m.fe7'
m.timing_say = 1
call timing ,, 'prepared'
$;
cFnd = 0
cNoF = 0
v1 = '20140707'
v4 = '20140707'
$do jx=1 to m.jj.0 $@[
if $f=1 then
call sqlQueryArgs 3, v1, v1, v4, m.jj.jx.d2, v4, m.jj.jx.d5
else if $f=2 then
call sqlQueryArgs 3, v1, m.jj.jx.d2, v4, m.jj.jx.d5
m.fe1=''
if sqlFetch(3, ff) then do
$** say ${i&D2} ${i&D5} '==>' m.fe0 m.fe1 '2='m.fe2 m.fe3 m.fe4,
$** '5='m.fe5 m.fe6
cFnd = cFnd +1
end
else do
$** say ${i&D2} ${i&D5} '==> notfound'
cNoF = cNoF +1
end
call sqlClose 3
$]
call timing $qq fun, cFnd+cNof, cFnd 'found and' cNoF 'notFound'
$]
$proc inp $@[
jx=0
$for i $@[
jx = jx+1
m.jj.jx.d2 = right(${i&D2}, 2, 0)
m.jj.jx.d5 = ${i&D5}
$]
m.jj.0 = jx
$<:[table
D2 D5
2 USD
1 0236
1 2991
1 2220
1 1664
1 1234
1 0945
1 0947
1 1235
1 2221
1 1665
1 2992
1 0237
2 UYI
2 ADP
2 AED
2 UYN
1 0238
1 2993
1 1666
1 2222
1 1236
1 0949
1 0950
1 1237
1 2223
1 1667
1 2994
1 0239
2 UYU
2 AFA
2 AFN
2 UZS
1 0241
1 2995
1 1668
1 2224
1 1238
1 0952
1 0953
1 1239
1 2225
1 1669
1 2996
1 0242
2 VEB
2 ALK
2 ALL
2 VEF
1 0248
1 2997
1 1670
1 2226
1 1240
1 0955
1 0956
1 1241
1 2237
1 1671
1 2998
1 0249
2 VNC
2 AMD
2 ANG
2 VND
1 0251
1 3000
1 1672
1 2238
1 1242
1 0957
1 0958
1 1243
1 2240
1 1673
1 3001
1 0260
2 VUV
2 AOA
2 AOK
2 WST
1 0261
1 3002
1 1674
1 2241
1 1244
1 0959
1 0960
1 1245
1 2242
1 1675
1 3003
1 0265
2 XAF
2 AON
2 AOR
2 XAF
1 0267
1 3004
1 1676
1 2243
1 1246
1 0961
1 0962
1 1247
1 2244
1 1677
1 3005
1 0275
2 XAF
2 ARP
2 ARS
2 XAG
1 0280
1 3006
1 1678
1 2245
1 1248
1 0963
1 0964
1 1249
1 2246
1 1679
1 3008
1 0285
2 XAU
2 ATS
2 AUD
2 XBA
1 0290
1 3015
1 1680
1 2257
1 1250
1 0965
1 0966
1 1251
1 2258
1 1681
1 3016
1 0292
2 XCD
2 AWG
2 AZM
2 XDR
1 0295
1 3017
1 1682
1 2260
1 1252
1 0967
1 0968
1 1253
1 2261
1 1683
1 3090
1 0305
2 XFO
2 AZN
2 BAD
2 XOF
1 0306
1 3100
1 1684
1 2262
1 1254
1 0969
1 0970
1 1260
1 2263
1 1685
1 3101
1 0307
2 XOF
2 BAM
2 BBD
2 XOF
1 0310
1 3105
1 1686
1 2264
1 1261
1 0971
1 0972
1 1262
1 2270
1 1687
1 3106
1 0311
2 XOF
2 BDT
2 BEC
2 XOF
1 0312
1 3110
1 1688
1 2271
1 1263
1 0973
1 0974
1 1264
1 2272
1 1689
1 3111
1 0315
2 XOF
2 BEF
2 BGJ
2 XOF
1 0320
1 3115
1 1690
1 2273
1 1270
1 0975
1 0976
1 1290
1 2274
1 1691
1 3120
1 0325
2 XPD
2 BGK
2 BGL
2 XPF
1 0331
1 3121
1 1692
1 2280
1 1291
1 0977
1 0978
1 1292
1 2281
1 1693
1 3125
1 0332
2 XPT
2 BGN
2 BHD
2 XZN
1 0335
1 3130
1 1694
1 2282
1 1301
1 0979
1 0980
1 1302
1 2283
1 1695
1 3210
1 0336
2 YDD
2 BIF
2 BMD
2 YER
1 0337
1 3211
1 1696
1 2284
1 1309
1 0981
1 0982
1 1310
1 2285
1 1697
1 3215
1 0338
2 YUD
2 BND
2 BOB
2 YUM
1 0340
1 3220
1 2286
1 1698
1 1311
1 0983
1 0984
1 1312
1 1699
1 2297
1 3221
1 0345
2 YUN
2 BOP
2 BOV
2 ZAR
1 0350
1 3300
1 2298
1 1700
1 1313
1 0985
1 0986
1 1315
1 1701
1 2300
1 3301
1 0355
2 ZMK
2 BRB
2 BRL
2 ZMW
1 0357
1 3302
1 2301
1 1702
1 1320
1 0987
1 0988
1 1321
1 1703
1 2302
1 3310
1 0358
2 ZRN
2 BRN
2 BSD
2 ZRZ
1 0360
1 3320
1 2303
1 1704
1 1322
1 0989
1 0990
1 1323
1 1705
1 2304
1 3330
1 0365
2 ZWC
2 BTN
2 BWP
2 ZWD
1 0370
1 3331
1 2305
1 1706
1 1330
1 0991
1 0992
1 1331
1 1707
1 2306
1 3400
1 0375
2 ZWL
2 BYB
2 BYR
2 ZWN
1 0380
1 3409
1 2313
1 1708
1 1332
1 0993
1 0994
1 1333
1 1709
1 2317
1 3410
1 0385
1 0001
2 BZD
2 CAD
1 0002
1 0390
1 3412
1 2318
1 1710
1 1398
1 0995
1 0996
1 1399
1 1711
1 2320
1 3510
1 0395
1 0003
2 CDF
2 CHF
1 0004
1 0400
1 3511
1 2321
1 1712
1 1400
1 0997
1 0998
1 1401
1 1713
1 2322
1 3550
1 0402
1 0005
2 CLF
2 CLP
1 0006
1 0405
1 3711
1 2323
1 1714
1 1402
1 0999
1 1001
1 1403
1 1715
1 2324
1 3860
1 0415
1 0007
2 CNH
2 CNX
1 0008
1 0420
1 3980
1 2329
1 1716
1 1410
1 1002
1 1003
1 1411
1 1717
1 2330
1 3981
1 0425
1 0009
2 CNY
2 COP
1 0010
1 0427
1 3990
1 2331
1 1718
1 1412
1 1010
1 1011
1 1413
1 1719
1 2332
1 4001
1 0430
1 0011
2 COU
2 CRC
1 0012
1 0435
1 4002
1 2333
1 1720
1 1450
1 1012
1 1013
1 1451
1 1721
1 2334
1 4010
1 0438
1 0013
2 CSJ
2 CUC
1 0014
1 0439
1 4011
1 2335
1 1722
1 1452
1 1014
1 1018
1 1500
1 1723
1 2336
1 4020
1 0440
1 0015
2 CUP
2 CVE
1 0016
1 0441
1 4021
1 2343
1 1724
1 1501
1 1019
1 1020
1 1502
1 1725
1 2347
1 4099
1 0442
1 0017
2 CYP
2 CZK
1 0018
1 0443
1 4100
1 2348
1 1726
1 1503
1 1021
1 1025
1 1504
1 1727
1 2350
1 4101
1 0445
1 0019
2 DDM
2 DEM
1 0020
1 0447
1 4106
1 2360
1 1728
1 1505
1 1026
1 1027
1 1506
1 1729
1 2361
1 4110
1 0448
1 0021
2 DJF
2 DKK
1 0022
1 0455
1 4111
1 2362
1 1730
1 1507
1 1028
1 1029
1 1508
1 1731
1 2363
1 0456
1 0023
2 DOP
1 4112
1 4113
2 DZD
1 0024
1 0457
1 2364
1 1732
1 1509
1 1030
1 1031
1 1510
1 1733
1 2365
1 0458
1 0025
2 ECS
1 4115
1 4120
2 EEK
1 0026
1 0459
1 2383
1 1734
1 1511
1 1032
1 1035
1 1512
1 1735
1 2384
1 0460
1 0027
2 EGP
1 4121
1 4125
2 ERN
1 0028
1 0502
1 2390
1 1736
1 1513
1 1036
1 1040
1 1514
1 1737
1 2391
1 0504
1 0029
2 ESA
1 4130
1 4209
2 ESB
1 0030
1 0505
1 2392
1 1738
1 1515
1 1041
1 1042
1 1516
1 1739
1 2393
1 0506
1 0031
2 ESP
1 4211
1 4212
2 ETB
1 0032
1 0507
1 2397
1 1740
1 1517
1 1043
1 1044
1 1518
1 1741
1 2398
1 0508
1 0033
2 EUR
1 4215
1 4225
2 FIM
1 0034
1 0509
1 2399
1 1742
1 1519
1 1045
1 1046
1 1520
1 1743
1 2405
1 0510
1 0035
2 FJD
1 4230
1 4231
2 FKP
1 0036
1 0511
1 2410
1 1744
1 1521
1 1049
1 1051
1 1522
1 1745
1 2422
1 0513
1 0037
2 FRF
1 4232
1 4233
2 GBP
1 0038
1 0517
1 2423
1 1746
1 1523
1 1052
1 1053
1 1524
1 1747
1 2497
1 0518
1 0039
2 GEL
1 4235
1 4236
2 GGP
1 0040
1 0525
1 2498
1 1748
1 1525
1 1054
1 1055
1 1526
1 1749
1 2499
1 0529
1 0041
2 GHC
1 4237
1 4238
2 GHS
1 0042
1 0533
1 2500
1 1750
1 1527
1 1061
1 1062
1 1528
1 1751
1 2501
1 0537
1 0043
2 GIP
1 4400
1 4510
2 GMD
1 0044
1 0541
1 2502
1 1752
1 1529
1 1063
1 1064
1 1530
1 1753
1 2503
1 0549
1 0045
2 GNF
1 4511
1 4550
2 GQE
1 0046
1 0553
1 2504
1 1754
1 1531
1 1065
1 1069
1 1532
1 1755
1 2505
1 0557
1 0047
2 GRD
1 4551
1 4590
2 GTQ
1 0048
1 0558
1 2509
1 1756
1 1533
1 1070
1 1071
1 1534
1 1757
1 2510
1 0561
1 0049
2 GYD
1 4702
1 4703
2 HKD
1 0050
1 0563
1 2511
1 1758
1 1535
1 1072
1 1073
1 1536
1 1759
1 2512
1 0565
1 0051
2 HNL
1 4721
1 4730
2 HRD
1 0052
1 0567
1 2513
1 1760
1 1537
1 1074
1 1075
1 1538
1 1761
1 2518
1 0569
1 0053
2 HRK
1 4740
1 4750
2 HTG
1 0055
1 0573
1 2521
1 1762
1 1539
1 1076
1 1077
1 1540
1 1763
1 2522
1 0585
1 0056
2 HUF
1 4751
1 4755
2 IDR
1 0057
1 0589
1 2523
1 1764
1 1541
1 1078
1 1079
1 1542
1 1765
1 2529
1 0593
1 0058
2 IEP
1 4756
1 4790
2 ILP
1 0060
1 0595
1 2530
1 1766
1 1543
1 1080
1 1081
1 1544
1 1767
1 2531
1 0597
1 0061
2 ILR
1 4800
1 4900
2 ILS
1 0062
1 0601
1 2532
1 1768
1 1545
1 1082
1 1083
1 1546
1 1769
1 2533
1 0605
1 0064
2 INR
1 4901
1 4902
2 IQD
1 0065
1 0606
1 2541
1 1770
1 1547
1 1084
1 1085
1 1548
1 1771
1 2545
1 0609
1 0066
2 IRR
1 4903
1 4904
2 ISJ
1 0069
1 0613
1 2546
1 1772
1 1549
1 1086
1 1087
1 1550
1 1773
1 2555
1 0617
1 0070
2 ISK
1 4905
1 4906
2 ITL
1 0073
1 0618
1 2559
1 1774
1 1551
1 1088
1 1090
1 1552
1 1775
1 2560
1 0619
1 0074
2 JMD
1 4950
1 4951
2 JOD
1 0075
1 0625
1 2561
1 1776
1 1553
1 1091
1 1092
1 1554
1 1777
1 2562
1 0641
1 0082
2 JPY
1 4952
1 4953
2 KES
1 0083
1 0642
1 2563
1 1778
1 1555
1 1093
1 1094
1 1556
1 1779
1 2564
1 0645
1 0084
2 KGS
1 4954
1 4955
2 KHR
1 0087
1 0646
1 2569
1 1780
1 1557
1 1095
1 1096
1 1558
1 1781
1 2570
1 0647
1 0088
2 KMF
1 5910
1 5911
2 KPW
1 0089
1 0649
1 2571
1 1782
1 1559
1 1097
1 1098
1 1560
1 1783
1 2572
1 0653
1 0090
2 KRW
1 5912
1 5914
2 KWD
1 0091
1 0657
1 2573
1 1784
1 1561
1 1100
1 1101
1 1562
1 1785
1 2580
1 0661
1 0093
2 KYD
1 5918
1 5919
2 KZT
1 0094
1 0671
1 2581
1 1786
1 1563
1 1102
1 1103
1 1564
1 1787
1 2582
1 0672
1 0095
2 LAJ
1 5921
1 5922
2 LAK
1 0096
1 0673
1 2583
1 1788
1 1565
1 1104
1 1105
$]
$]
$#out 20140707 15:24:42
1 fetFi 1000 ela=0 cpu=0.87 su=38751 1000 found and 0 notFound
2 origSubS 1000 ela=0 cpu=0.87 su=38808 1000 found and 0 notFound
3 fetFi 1000 ela=0 cpu=0.88 su=39481 1000 found and 0 notFound
4 origSubS 1000 ela=0 cpu=0.87 su=39038 1000 found and 0 notFound
5 fetFi 1000 ela=0 cpu=0.87 su=38488 1000 found and 0 notFound
$#out 20140707 15:03:29
fetFi 1000 ela=0 cpu=1.43 su=63896 1000 found and 0 notFound
origSubSel 1000 ela=0 cpu=1.38 su=61634 1000 found and 0 notFound
fetFi 1000 ela=0 cpu=1.40 su=62377 1000 found and 0 notFound