Written by

Developer, analyst, qa, moderator at TECCOD

Question Dmitrij Vladimirov · Oct 16

Custom listing with inner subquery

#InterSystems IRIS BI (DeepSee) #Analytics #SQL

Hi community.
I have a query:

SELECT
nameField,
dateField,
anotherDateField
FROM
(      
SELECTMIN(someDate) as dateField,       
nameField,
anotherDateField
FROM $$$SOURCEWHERE $$$RESTRICT               
GROUPby someOtherField    
)
WHERE dateField >= anotherDateField

This query should filter the data by the minimum value of the somDate field, but it doesn't. It displays all values together, regardless of the external filter. The exact same query (without the $$$ tokens, of course) works fine in a regular SQL runtime.
My guess is that the $$$RESTRICT does this

WHERE source.%ID IN (SELECT _DSsourceId FROM MyTable.Listing WHERE _DSqueryKey = 'en3460403753')

And outer WHERE is just left unseen
Is there a way to fix this?

Product version: IRIS 2025.2

Discussion (3)0

Add reply

Comments

Stephen Canzano · Oct 16

You may have an issue with the format of the date.

What is the datatype of

somedate
anotherdatefield

Are they %Date, %TimeStamp, %UTC, or PosixTime

I'm not certain but does MAX(somedate) cause the value to no longer be in a format that would support

WHERE dateField >= anotherDateField

When in Analyzer you can see the actual query being run aftet the query runs by clicking on the Show Query button in the toolbar

which it seems like you have done so as you have

WHERE source.%ID IN (SELECT _DSsourceId FROM MyTable.Listing WHERE _DSqueryKey = 'en3460403753')

you can copy the query SQL Query Listing and paste into (albeit remove the portion that has

WHERE source.%ID IN (SELECT _DSsourceId FROM MyTable.Listing WHERE _DSqueryKey = 'en3460403753')

)

System Explorer->SQL and take special note of the Runtime mode of the query.

The Runtime mode has the greatest impact on columns that are

%Date

%UTC

as these columns will have different values based on the runtime mode(Logical/Display/ODBC)

0 0

Dmitrij Vladimirov Oct 16 to Stephen Canzano

Thanks for the reply!
As i said, the query works perfectly fine when executed normaly (without $$$ tokens). The data is correct
I should add some details then

When I compare dates in the WHERE clause, I convert them to INTs, just to make sure all the data is consistent. I also do a slightly more complex comparison. I've omitted the details because they're not relevant to my case

anotherDateField BETWEEN CAST(TO_CHAR(dateFieldOne, 'YYYYMM') as int) and CAST(TO_CHAR(dateFieldTwo, 'YYYYMM') as int)

Next, the date format is %TimeStamp, and it's very common in my data. I work with it every day, and every time function you can think of works without a problem
So the time data works fine, the outer WHERE clause doesn't catch up because of $$$RESTRICT in the inner section but without it the listing would not work at all

0 0

Dmitrij Vladimirov · Oct 26

I solved this problem by creating a SQL view and then join it inside a listing query

SELECT
v.somefields
FROM $$$SOURCE, CustomSchema.MyView as v
WHERE $$$RESTRICT and source.Field = v.SameField

0 0