Rocket U2 | UniVerse & UniData

Shawn Waldie posted 03-26-2024 16:39

Try this:

GET.LIST EC2WD
LIST PERSON SELECT.ONLY EVAL "IF PERSON.ALT.IDS NE '' THEN 'Y' ELSE 'N'" TO DELIM "*" _HOLD_/24.EC2WD

And I'll also add that since PERSON.ALT.IDS is multi-valued, your sort may not give you the results you're expecting.

Adrian West posted 03-27-2024 13:18

@Shawn Waldie Thank you! This helped a lot, it delivered a list of Y/N flags, but no IDs. I need both.

Say I used this with a single value Field like PERSON.SSN with the ID.SUP to not list SSNs but wanted Colleague IDs instead. Is it possible to list both the Colleague ID and the Y/N flag??

Shawn Waldie posted 03-27-2024 13:25

Yes, that would be helpful.

The 'TO DELIM' clause requires one to explicitly specify inclusion of the record ID, though adjustment of one of the UDT options will probably change that behavior.

Try this:

LIST PERSON SELECT.ONLY @ID EVAL "IF PERSON.ALT.IDS NE '' THEN 'Y' ELSE 'N'" TO DELIM "*" _HOLD_/24.EC2WD

Adrian West posted 03-27-2024 15:15

@Shawn Waldie Pretty much the same result. 

I tried to tweak it with SSN for good measure as previously mentioned, same result.

GET.LIST EC2WD
SELECT PERSON WITH SSN NE '' SELECT.ONLY
LIST PERSON SELECT.ONLY @ID EVAL "IF PERSON.SSN NE '' THEN 'Y' ELSE 'N'" TO DELIM "*" _HOLD_/24.EC2WD
DATA Y

Seems odd, EC2WD should just be a list of students. Could be some custom impact that I'm unaware of.

Shawn Waldie posted 03-27-2024 16:55

The text file generated in _HOLD_ labeled 24.EC2WD should contain asterisk-delimited records like the following:

0523569*Y

0654645*N

0542268*N

That first attribute is the PERSON record ID, and the second one is a calculated result based on the value(s) stored in the PERSON.ALT.IDS attribute of the PERSON file.

From the top:

You're activating a SAVEDLIST labeled 'EC2WD' that should contain PERSON record IDs.

You filter that active list of PERSON record IDs by excluding those without a PERSON.SSN.

From that list, you're generating your text file in _HOLD_.

Your DATA statement isn't doing anything in this context.

Adrian West posted 03-28-2024 16:42

@Shawn Waldie Thanks so much for this break down. I have a working template now. I appreciate your feedback so much.

For some reason it was giving me the ID twice, like this:

0523569*0523569*Y

I added ID.SUPP to the string and it worked accordingly.

If I wanted to pull a specific Alternate ID that is assigned to all students versus the Colleague ID would I define it here?

SELECT STUDENT.TERMS WITH STTR.TERM EQ "<<C2,SEMX>>" AND WITH STTR.ACTIVE.CRED GE "6.00" SAVING UNIQUE STTR.STUDENT

Something like this instead due to it being multi-valued??

SELECT STUDENT.TERMS WITH STTR.TERM EQ "<<C2,SEMX>>" AND WITH STTR.ACTIVE.CRED GE "6.00" SAVING UNIQUE STTR.PERSON.ALT.IDS EQ "Specific ALT ID type"

Or since I already have the EC2WD with Colleague IDs, I could still try something similar to your initial recommendation:

GET.LIST EC2WD
LIST PERSON WITH PERSON.ALT.IDS EQ "Specific ALT ID type" SELECT.ONLY EVAL "IF PERSON.ALT.IDS NE '' THEN 'Y' ELSE 'N'" TO DELIM "*" _HOLD_/24.EC2WD

I'm actively testing/tweaking diff queries as I research, just asking to make sure I'm on or close to the right path.

Shawn Waldie posted 03-28-2024 18:39

For some reason it was giving me the ID twice, like this:

0523569*0523569*

Yeah, I had my wires crossed.  The default behavior is to include the record ID:

As for your next question, let's back up a little since the filters you're employing are based on data stored in two different files, i.e., STUDENT.TERMS and PERSON.

It looks like you want students with at least 6 active credit hours in a specific term:

SELECT STUDENT.TERMS WITH STTR.TERM EQ "<<C2,SEMX>>" AND WITH STTR.ACTIVE.CRED GE "6.00"

And of these students, you only want those with a certain alternate ID type, correct?

Although you can access the alternate ID info from STUDENT.TERMS, it actually lives in PERSON so I would go there to check for it.

Another reason I would go to PERSON is because the fields in question - PERSON.ALT.IDS and PERSON.ALT.ID.TYPES - are muli-valued and associated, concepts you may need to brush up on.

So putting it altogether, I would do something like this:

SELECT STUDENT.TERMS WITH STTR.TERM EQ "<<C2,SEMX>>" AND WITH STTR.ACTIVE.CRED GE "6.00" SAVING UNIQUE STTR.STUDENT

The "SAVING UNIQUE STTR.STUDENT" activates the pertinent STUDENTS/PERSON record IDs for your next query:

SELECT PERSON SELECT.ONLY WITH PERSON.ALT.ID.TYPES EQ "Specific ALT ID type"

Note that the expression PERSON.ALT.IDS NE '' should always be 0/N in this result set.

Adrian West posted 03-29-2024 02:01

@Shawn Waldie Of the students, I want to list them all with a specific alternate ID they all have assigned instead of their Colleague IDs or SSN.

Preferred deliverable = Specific Alt ID Type | Y/N flag

Optional deliverable = Colleague ID | Specific Alt ID Type | Y/N flag

Based on the technical reference, it seemed like my recent test should have worked, unless I should be using SELECT PERSON SELECT.ONLY WITH PERSON.ALT.IDS PERSON.ALT.ID.TYPES EQ "Specific ALT ID type"

My most recent test: 

• --- Get Students with 6 or more credits ---
SELECT STUDENT.TERMS WITH STTR.TERM EQ "<<C2,SEMX>>" AND WITH STTR.ACTIVE.CRED GE "6.00" SAVING UNIQUE STTR.STUDENT
SAVE.LIST EC2WD
*
*
* --- Get 3rd line Alt ID of Students with 6 or more credits from EC2WD savedlist data
GET.LIST EC2WD
SELECT PERSON SELECT.ONLY WITH PERSON.ALT.ID.TYPES EQ "Specific ALT ID type"
SAVE.LIST EC2WD
*
* --- Get the data ---
GET.LIST EC2WD
SELECT PERSON WITH SSN NE '' SELECT.ONLY
LIST PERSON WITH PERSON.ALT.ID.TYPES EQ "Specific ALT ID type" SELECT.ONLY @ID EVAL "IF PERSON.SSN NE '' THEN 'Y' ELSE 'N'" ID.SUPP TO DELIM "|" _HOLD_/24.EC2WD

Results:

No data retrieved from current (S)SELECT statement.
Overwriting existing saved list.
No active select list, creating empty saved list.
No record retrieved.
0 records retrieved to list 0.
No active select list. Processing terminated.
No active select list. Processing terminated.

Shawn Waldie posted 03-29-2024 10:48

The results you posted make it look like you're running this from a paragraph (script), and this is what appears to me to be happening:

If so, we see that your very first SELECT statement is not returning any results, which means you will never get any results from this since all of your other statements depend on getting results from each preceding one.

That said, we should break it down in pieces and isolate/execute one line at a time, rather than from within a paragraph.

The first statement includes a prompt for a value to compare to STTR.TERM:

Instead, hard-code that value and execute that single statement at the colon prompt. E.g.:

SELECT STUDENT.TERMS WITH STTR.TERM EQ "24SP"

Note that '24SP' may not be valid at your school, so use something that is.

Ex:

That greater than prompt indicates that 891 STUDENT.TERMS records met that condition, and the record IDs are now active.

Next statement:

This result indicates that of the initial 891 STUDENT.TERMS records, 815 of them have at least 6 active credits.

Next, save those PERSON record IDs to the EC2WD savedlist:

At this point, instead of working with STUDENT.TERMS record IDs, now you're working with PERSON record IDs (...SAVING UNIQUE STTR.STUDENT).

BTW, I recommend that you review at least two of Rocket's user manuals:

Using Uniquery and Using Unidata

Adrian West posted 03-29-2024 11:25

@Shawn Waldie

Yes, it is a paragraph.

But it worked perfectly fine until I added:

--- Get 3rd line Alt ID of Students with 6 or more credits from EC2WD savedlist data
GET.LIST EC2WD
SELECT PERSON SELECT.ONLY WITH PERSON.ALT.ID.TYPES EQ "Specific ALT ID type"
SAVE.LIST EC2WD

I only added that to try to get the Alt ID I'm interested in, from the initial list...which would produce the record IDs

Thanks again for the feedback, I actually am using those manuals, as well as the Commands Reference for both.

Peter Cheney posted 04-07-2024 18:24

Hi Adrian,

Re: "The vendor is requesting a Y or N flag included to be in the data, based on if they meet the selection criteria or not."

Is the Y/N flag based on the value for STTR.ACTIVE.CRED? So if GE "6.00" then "Y" else "N". If it is then it looks to me that you've excluded all the "N" possibilities with that initial select.

Or have I missed something?

Cheers,
Peter