How To Give a Condition To Data Row based on First and Next Data?

Question

Question

#SQL #VSCode #TrakCare #InterSystems IRIS #Other

Hello my Friends,

I have a question, I have a data like this

And I want to create a display like this, which I can put a status column beside the Item Column

The status become new, if there's no previous item, and if there's a same item after the first one, the status become Old

Does anyone could help me ?

I need to display it without subquery if possible

Thank You

Best Regards,

Steven Henry

Product version: IRIS 2024.3

$ZV: $ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2023.1.1 (Build 380_0_23477U) Wed Jun 12 2024 18:10:40 EDT [Health:5.1.0-1.m1

Discussion (5)1

Log in or sign up to continue

Robert Cemper · Dec 17

Suggestion:
you crate your own stored procedure to decide during SELECT
example:

/// Return NEW for first occurance of item 
/// otherwise return OLD
Class User.ItemStat Extends %RegisteredObject
{
ClassMethod NewOld(item As %String = "") As %String [ SqlProc ]
{
    if item="" quit "?"
    if $d(^||list(item)) quit "OLD"
    if $i(^||list(item)) quit "NEW"
}
}

How to use it:

SELECT *, ItemStat_NewOld(item) as Status
FROM items order by 2

Result:

ID	date	  item	Status
1	09/13/1932	A	NEW
2	04/06/1933	D	NEW
10	06/15/1940	A	OLD
4	11/26/1940	A	OLD
6	02/19/1956	B	NEW
8	04/22/1957	D	OLD
7	05/01/1959	D	OLD
9	06/29/1961		?
3	07/04/1992	B	OLD
5	12/08/2020	D	OLD

0 0

score 0 · Answer 1 · 2024-12-17T11:09:07-05:00

I'm not sure this is the answer you're looking for, but I would loop through the result set and store the data in a local array and then loop through the array and output the data with the calculated column set as you wish. The array would allow you to loop through each ID to find the latest to mark as new. This is an ObjectScript approach along with SQL. I wonder if you could just re-save the data back to the table once calculated so you can still project it as needed to other systems.

score 0 · Answer 2 · 2024-12-17T20:24:23-05:00

Steven Henry Su... · 22 hr ago

Hy Robert, thank you for your help

but can I use Query without store procedure ?

Thank you

Steve Henry

0 0

score 0 · Answer 3 · 2024-12-18T00:43:18-05:00

Robert Cemper · 18 hr ago

If available anyhow any SQL or TSQL is slower

0 0

score 0 · Answer 4 · 2024-12-18T04:38:12-05:00

Hi,

Using COS is pretty simple. As you loop the global with $o() simply put the ID value in an array or local global at the last line before going into the loop for the next id. Then for each loop, if $d(array(id)) it means is OLD, otherwise is NEW.

Right?