As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
SQL passthrough is in this case no Option. There are users, who want to use db_expr the way I explained and are not allowed to work in Textmodus. So only DB_EXPR is a solution and I would love to understand the possibilities of DB_EXPR anyway.
Group by etc. will work, but only in the context of the query itself. The DB_EXPR should return a single value, because the result is supposed to be a value in a row. The ordering should be bound to the overall query. You can reference columns from the parent table (as in TABLE FILE parent-table) but it can be a little tricky sometimes.
For possibilities, the following is the most complicated query I've had to do. We are trying to get the age of a person, not their current age, rather their age at the time of the transaction:
DEFINE AGE_AT_OCCURANCE/I5 WITH RESTRICT_AUTH_BY=DB_EXPR(
SELECT DATEDIFF(year,T6.DATE_OF_BIRTH,T3.OCC_DATE) AS AGE_AT_OCCURANCE
SELECT T5.OCCURRENCE_SOURCE_KEY,T4.FULL_DATE AS DATE_OF_BIRTH,T5.source_system
FROM (dbo.dim_date T4
INNER JOIN dbo.fact_person T5 ON T4.DATE_PK = T5.DATE_OF_BIRTH AND T5.PERSON_SOURCE_KEY = "FACT_PERSON.PERSON_SOURCE_KEY" )
) T6 INNER JOIN
SELECT T2.FULL_DATE AS OCC_DATE,T1.OCCURRENCE_SOURCE_KEY,T1.source_system
FROM (dbo.fact_occurrence T1 INNER JOIN dbo.dim_date T2 ON T1.OCCURRENCE_DATE = T2.DATE_PK AND T1.OCCURRENCE_SOURCE_KEY = "OCCURRENCE_SOURCE_KEY" )
) T3 ON T6.OCCURRENCE_SOURCE_KEY = T3.OCCURRENCE_SOURCE_KEY AND T6.SOURCE_SYSTEM = T3.source_system)
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott