Question
· Oct 16

Custom listing with inner subquery

#Analytics #SQL #InterSystems IRIS BI (DeepSee)

Hi community.

I have a query:

SELECT
nameField,
dateField,
anotherDateField
FROM
(      
SELECT
MIN(someDate) as dateField,       
nameField,
anotherDateField
FROM $$$SOURCE
WHERE $$$RESTRICT               
GROUP by 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 (2)2
Log in or sign up to continue
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

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