2

I have a table, DEBTOR, with a structure like this:

DEBTOR

and a second table, DEBTOR.INFO structured like this:

DEBTOR.INFO

I have a select list made of record IDs from the DEBTOR.INFO table. How can I

select * from DEBTOR WHERE 53 IN (name of select list)?

Is this even possible?

I realize this query looks more like SQL than RetrieVe but I wrote it that way for an easier understanding of what I'm trying to accomplish.

Currently, I accomplish this query by writing

SELECT DEBTOR WITH 53 EQ [paste list of DEBTOR.INFO record IDs]

but obviously this is unwieldy for large lists.

user3140622
  • 37
  • 6

4 Answers4

0

It looks to me that you cant do that. Even if you use and i-descriptor, It only works in one direction. TRANS("DEBTOR.INFO",53,0,"X") works from the DEBTOR file but not the other way. So TRANS("DEBTOR",@ID,53,"X") from DEBTOR.INFO will return nothing.

See this article on U2's site for a possible solution.

Lamb Of Zod
  • 20
  • 3
  • Thanks for your answer. I realized I made a mistake typing my second query so that will probably alter the correct answer. Also worth mentioning, I would need to team with the programmers in order to add an i-descriptor to a dictionary. 53 is the "foreign key" (PACKET) in the DEBTOR table that matches the record id's in my select list made from the DEBTOR.INFO table. – user3140622 Sep 04 '14 at 15:10
  • I modified my answer to only show the select with eval approach. That might be the easiest way if you want to avoid teaming with programmers. – Lamb Of Zod Sep 06 '14 at 16:59
  • I tried this. Had to flip the locations of the single and double quotes. After that the query returned nothing; it appears that it was trying and failing to find the 8 digit IDs from the debtor.info select list in the index of 9 digit IDs from the debtor table. I added a diagram of my table structure in case there was some confusion. – user3140622 Sep 08 '14 at 12:54
  • I am working with our legacy apps team to attempt implementing the LSELECT program. Thanks for bringing that potential solution to my attention. – user3140622 Jan 05 '15 at 19:50
0

Would something like this work (two steps):

SELECT DEBTOR.INFO SAVING PACKET LIST DEBTOR ....

This creates a select list of the data in the PACKET field in the DEBTOR.INFO file and makes it active. (If you have duplicate values that way you can add the keyword UNIQUE after SAVING).

Then the subsequent LIST command uses that active select list which contains values found in the @ID field of the file DEBTOR.

jbmonco
  • 130
  • 4
  • 1
    Or maybe I'm reading this wrong and you could actually use SQL for UniData or UniVerse to do what you want. In UniData you need to CONVERT.SQL the file(s). Then something like SQL SELECT * FROM DEBTOR WHERE PACKET IN (SELECT ID FROM DEBTOR_INFO WHERE yourcriteria)); – jbmonco Sep 04 '14 at 17:37
  • Unfortunately my select list isn't created from a query, it's created by uploading and converting a .csv file of some arbitrary packet IDs. That's why I'm wondering if there's some way to use the name of a list in a SELECT statement. – user3140622 Sep 05 '14 at 15:00
  • There isn't but you could save that .csv file into the SAVEDLISTS directory under a name MYLIST000 and then use GET.LIST MYLIST to activate it ahead of your next query statement. (note I don't use the 000 in the GET.LIST command. – jbmonco Sep 16 '14 at 20:29
0

Not sure if you are still looking at this, but there is a simple option that will not require a lot of programming.

I did it with a program, a subroutine and a dictionary item.

First I set a named common variable to contain the list of DEBTOR.INFO ids:

SETLIST
*
* Use named common to hold list of keys
COMMON /MYKEYS/ KEYLIST
*
* Note for this example I am reading the list from SAVEDLISTS
OPEN "SAVEDLISTS" TO FILE ELSE STOP "CAN NOT OPEN SAVEDLISTS"
READ KEYLIST FROM FILE, "MIKE000" ELSE STOP "NO MIKE000 ITEM"

Now, I can create a subroutine that checks for a value in that list

CHECKLIST
SUBROUTINE CHECKLIST( RVAL, IVAL)
COMMON /MYKEYS/ KEYLIST
LOCATE IVAL IN KEYLIST <1> SETTING POS THEN
  RVAL = 1
END ELSE RVAL = 0
RETURN

Lastly, I use a dictionary item to call the subroutine with the field I am looking for:

INLIST:
I
SUBR("CHECKLIST", FK)

IN LIST
10R
S

Now all I have to do is put the correct criteria on my list statement:

LIST DEBTOR WITH INLIST = 1 ACCOUNT STATUS FK
Mike
  • 194
  • 11
0

Id use the very powerfull EVAL with an XLATE ;

SELECT DEBTOR WITH EVAL \XLATE('DEBTOR.INFO',@RECORD<53>,'-1','X')\ NE ""
Roga
  • 1
  • 1