GET-LIST then LIST not by @ID column

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19695 by deeplay

GET-LIST then LIST not by @ID column was created by deeplay

Hello,

Is it possible in jBASE to get values from GET-LIST and then LIST not by @ID.
For example in isolated cases I use to LIST by using WITH:

LIST FBNK.STMT.ENTRY WITH REPAYMENT.TYPE='IN' AND (TRANS.REFERENCE='LD1536567891' OR TRANS.REFERENCE='LD1536567892') ACCOUNT.NUMBER TRANSACTION.CODE VALUE.DATE

But purpose is to extract info about 2000+ LD numbers. Is it possible to use GET-LIST with TRANS.REFERENCE column? If yes - how?

R10 on CentOS

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

jpb
Offline
Moderator
retired

8 years 4 months ago #19701 by jpb

Replied by jpb on topic GET-LIST then LIST not by @ID column

If I get you correctly you want to do something like :

SQLSELECT ACCOUNT_NUMBER, TRANSACTION_CODE, VALUE_DATE FROM 'FBNK.STMT.ENTRY' WHERE REPAYMENT_TYPE='IN' AND TRANS_REFERENCE IN (SELECT F0 FROM 'FBNK.LD.LOANS.AND.DEPOSITS' WHERE CATEGORY = "21000")

The following user(s) said Thank You: deeplay

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 4 months ago #19707 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

Hi JPB

SQL rules :)) While I was thinking of a possible solution with a subroutine that gets the list and returns it @VM-delimited so I could propose to use it in MATCHES EVAL "rtn_name(list_name)"...

Out of curiosity tried your statement... It worked with one small change - it didn't recognize F0, so see what's worked:

.. TRANS_REFERENCE IN (SELECT CONTRACT_NO_ FROM 'FBNK.LD.LOANS.AND.DEPOSITS' WHERE CATEGORY = "21062")

Cheers
VK

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

jpb
Offline
Moderator
retired

8 years 4 months ago #19708 by jpb

Replied by jpb on topic GET-LIST then LIST not by @ID column

F0 is like F1, f2 etc. a entry in VOC to be used instead of @ID (and field 1 etc.) or instead of *A0, *A1 etc. if they are not accepted.

Was too lazy to look up the alternate name of @ID in LD ;-)

The following user(s) said Thank You: VK

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 4 months ago #19709 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

I didn't know SQLSELECT recognizes F1, F2 etc (never used it much)... Yes, F0 absents in my VOC.

Cheers
VK

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago - 8 years 4 months ago #19710 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

Dear jpb, thank you for answer. Your solution looks fine, but how I understand this part ("TRANS_REFERENCE IN (SELECT F0 FROM....") means, that you will take all LD ids from LD.LOANS.AND.DEPOSITS table by some category (21000), but i want to extract info only for specialized list of LDs (about 2000 items).

R10 on CentOS

Last edit: 8 years 4 months ago by deeplay.

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

jpb
Offline
Moderator
retired

8 years 4 months ago #19711 by jpb

Replied by jpb on topic GET-LIST then LIST not by @ID column

How do you get the "specialized list of LDs " ?
If you select them fom LD.LOANS.AND.DEPOSITS with any criteria you can use the same within sub-query.
If you just have a listing with 2000 LDs that have nothing in common we may ask VK to process further on his idea to use MATCHES EVAL "rtn_name(list_name)"...

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19712 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

Yes, I have "a listing with 2000 LDs" in some text file stored in &SAVEDLIST&

R10 on CentOS

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 4 months ago #19713 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

Hi
unfortunately "EVAL" after "MATCHES" or "LIKE" gives jQL parse error.

Is it a one-time task or a regular one?

Cheers
VK

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 4 months ago #19714 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

OK, done it.

1. Write and compile the jBC subroutine (check first that you don't have another subroutine with the same name):

SUBROUTINE LIST.CONV(ret_list, list_name)
*-------------------------------------------------------------------------
   GETLIST list_name TO ret_list ELSE ret_list = ''  ;  RETURN
   CHANGE @FM TO @VM IN ret_list
   RETURN
END

2. Assuming that your list is named LD, run:

LIST FBNK.STMT.ENTRY WITH REPAYMENT.TYPE='IN' AND EVAL "TRANS.REFERENCE MATCHES LIST.CONV('LD')" EQ 1 ACCOUNT.NUMBER TRANSACTION.CODE VALUE.DATE

Cheers
VK

The following user(s) said Thank You: deeplay

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19715 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

wow, looks like something, that i looking for! I'll test and let you know about results, thanks!

R10 on CentOS

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19716 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

VK, where I can store list file (containing LDs list)?

R10 on CentOS

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19718 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

VK and jpb, last solution works! Thank you very much for your help!

R10 on CentOS

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

deeplay
Topic Author
Offline
Junior Member

8 years 3 months ago #19877 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

Dears, I've faced with problem on applying "routine solution" (by VK) for selecting data from FBNK.PD.PAYMENT.DUE$HIS by CONTRACT.NUMBER field. The problem is, that PD table stores CONTRACT.NUMBER in history format: PDLD1234567890;12
But file stored in SAVEDLIST dir contains data like this:
PDLD1234567890
PDLD1234567891

Question: is it possible to run select using VK's solution but to get same results as: ...CONTRACT.NUMBER LIKE 'PDLD1234567890...'

p.s: I've tried to store PDLDs in file (in SAVEDLIST dir) like that:
PDLD1234567890...
PDLD1234567891...
It's returns "No records"

R10 on CentOS

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

deeplay
Topic Author
Offline
Junior Member

8 years 3 months ago #19879 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

Also has tried to use EVAL inside EVAL, but it's impossible:

SELECT FBNK.PD.PAYMENT.DUE$HIS WITH REPAYMENT.DATE>20150501 AND (REPAY.TYPE='IN' OR REPAY.TYPE='PR' OR REPAY.TYPE='CH') AND EVAL "EVAL'CONTRACT.NUMBER[3,12]' MATCHES LIST.CONV('stmnt_ld')" EQ 1 SAVING EVAL "CONTRACT.NUMBER:',':CUSTOMER:',':REPAYMENT.DATE:',':REPAY.TYPE:',':ORIG.STLMNT.ACT:',':REPAY.AMT:',':CURRENCY"
SAVE.LIST stmnt_PD.csv

Returns:
"Error in attribute definition item EVAL'CONTRACT.NUMBER[3,12]' MATCHES LIST.CONV('stmnt_ld')"

R10 on CentOS

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 3 months ago #19880 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

I'm a bit lost...
If I understand you correctly, you need the following:

... AND EVAL "FIELD(CONTRACT.NUMBER,';',1) MATCHES ..." EQ 1

Cheers
VK

The following user(s) said Thank You: deeplay

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

deeplay
Topic Author
Offline
Junior Member

8 years 3 months ago #19882 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

yes, VK, but MATCHES result of your routine LIST.CONV(). But for routine it's neccessary to use EVAL again.

R10 on CentOS

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

deeplay
Topic Author
Offline
Junior Member

8 years 3 months ago - 8 years 3 months ago #19883 by deeplay

Replied by deeplay on topic GET-LIST then LIST not by @ID column

sorry :) you're right, there is no need to include another EVAL, because everything inside of EVAL works fine )))

SELECT FBNK.PD.PAYMENT.DUE$HIS WITH REPAYMENT.DATE>20150221 AND (REPAY.TYPE='IN' OR REPAY.TYPE='PR' OR REPAY.TYPE='CH') AND EVAL "CONTRACT.NUMBER[3,12] MATCHES LIST.CONV('stmnt_ld')" EQ 1 SAVING EVAL "CONTRACT.NUMBER:',':CUSTOMER:',':REPAYMENT.DATE:',':REPAY.TYPE:',':ORIG.STLMNT.ACT:',':REPAY.AMT:',':CURRENCY"

p.s: FIELD(CONTRACT.NUMBER,';',1) — I've just read about this from "This is how it works" book ))) Thanks for sharing your skills and knowledge.

R10 on CentOS

Last edit: 8 years 3 months ago by deeplay.

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

Winfred_Lubega
Offline
Senior Member
Me

7 years 11 months ago #20713 by Winfred_Lubega

Replied by Winfred_Lubega on topic GET-LIST then LIST not by @ID column

HELLO,
I am trying to extract data form the history table TELLER, but it comes with TT1111...;1, and when i use EQ it says no record found. How can i do it?

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

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

7 years 11 months ago #20714 by VK

Replied by VK on topic GET-LIST then LIST not by @ID column

Hi
post here the full text of your query...

Cheers
VK

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

Time to create page: 0.117 seconds