× Discuss about Archiving, Close of Business, Delivery Setup, Security Management System, Global Processing etc…

RE.CONSOL.SPEC.ENT.KEY @ID key structure

  • SandroK
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 2 days ago #22678 by SandroK
Hello all,

I'm trying to find a way to select records from RE.CONSOL.SPEC.ENT.KEY file by a particular YYYYMM (less than).
Most of the keys has the date at the end:
LD.1.TR.GEL.21074.4610.1101.GE.GE.1100.L.LIVEDB.20190508
LD.1.TR.GEL.21019.1400.1101.GE.GE.1100.S.50010.20191211
PD.1.TR.GEL.21073.7356.6113.GE.GE.6100..OVERDUEIN.20190418
AC.1.TR.USD.1001.2017.2110.GE.GE.2100..CREDIT.20190815

However, there are records with '.xx' attached at the end:
LD.1.TR.GEL.21067.2261.4105.GE.GE.4100.L.51001.20190503.1
PD.1.TR.GEL.21050.9601.1101.GE.GE.1100..OVERDUEPR.20191015.7
PD.1.TR.GEL.21053.9601.8101.GE.GE.8100..51000.20191230.24

Ideally would like to run something like that:
select FBNK.RE.CONSOL.SPEC.ENT.KEY WITH EVAL"INT(GET-YYYYMM-FROM-ID)" LT 201907

Any recommendations?

Thanks a lot
Sandro

Please Log in or Create an account to join the conversation.

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus|G09-G13 TAFC|R05-R23 TAFJ|R19,R23-test
More
4 years 2 days ago #22680 by VK
Hi
EVAL "INT(FIELD(@ID, '.', 13))"

If you are sure that all dates are positioned at 13th place. (it's 18 in my case but I noticed that "CP" entries have different ID structure.

You can also create a routine (name can be GET-YYYYMM-FROM-ID as per your example but you have to pass @ID to it as a parameter) that would analyze the ID structure and return the correct data.

Still, depending on your volumes you might have problems with performance of this query.

Cheers
VK
The following user(s) said Thank You: SandroK

Please Log in or Create an account to join the conversation.

  • SandroK
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 2 days ago #22681 by SandroK
Replied by SandroK on topic RE.CONSOL.SPEC.ENT.KEY @ID key structure
Hi VK. Thanks for the hint! Will try it tomorrow.

Yes, the volume is an issue, about 45ml records in a file. Standard arc process falls off, (jBASE: Unable to allocate 1769444513 bytes, errno = 12, at jlibEINSERT.c:242) and trying to deal with that issue by manually removing the records.

Cheers
Sandro

Please Log in or Create an account to join the conversation.

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus|G09-G13 TAFC|R05-R23 TAFJ|R19,R23-test
More
4 years 1 day ago #22684 by VK
Hi
if your DB is jBASE you can distribute this file.

Cheers
VK

Please Log in or Create an account to join the conversation.

  • SandroK
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 1 day ago #22689 by SandroK
Replied by SandroK on topic RE.CONSOL.SPEC.ENT.KEY @ID key structure
Hi VK,

R14, JBase, TAFC, on CentOs.
How efficient is the INT(FIELD(@ID, '.', 13)) function? Can I use it in the distribution algorithm?
Eg I usually try to use some thing like P$KEY[7,1] in dist algos, I have not noted significant performance issues with that function.

Thanks a lot!
Sandro

Please Log in or Create an account to join the conversation.

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus|G09-G13 TAFC|R05-R23 TAFJ|R19,R23-test
More
4 years 1 day ago #22691 by VK
Hi
both ways are OK in my understanding. I believe INT isn't necessary - then the text sort will be done.

But make sure first that you have the date exactly there...
You can SELECT ... SAVING EVAL "FIELD(@ID, '.', 13)"
.. then SAVE.LIST and SORT.LIST. Then look both at the saved list top and the bottom to see if there are any irregularities.

Cheers
VK

Please Log in or Create an account to join the conversation.

  • SandroK
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 1 day ago #22692 by SandroK
Replied by SandroK on topic RE.CONSOL.SPEC.ENT.KEY @ID key structure
Hi VK,

Thanks again for the hint. Looks good!
Just for the reference did on a test server:
time count FBNK.RE.CONSOL.PROFIT WITH EVAL "INT(@ID[4,5])" EQ 51000
 13261783 Records counted
usr: 187.14   sys: 58.37   elapsed: 77m56.19s

time count FBNK.RE.CONSOL.PROFIT WITH EVAL "INT(FIELD(@ID, '.', 2))" EQ 51000
 13261783 Records counted
usr: 149.15   sys: 57.54   elapsed: 55m10.79s

It could be due to the cache, nevertheless, FIELD showed better results!

Thanks
Sandro

Please Log in or Create an account to join the conversation.

Time to create page: 0.119 seconds