thanks!!!! @Frans, that means I don't Need a Group by here.
And, because select value from table doesn't work -> I always Need a min, max dst. etc?
Sothat I always get only the same value from my table?
Or is the grouping managed by the with field?
WebFOCUS 8.1.05 Windows, All Outputs
September 11, 2018, 07:43 AM
Frans
That depends on the Database and Adapter. Some databases support multiple values for one cell. Normaly You would have a min, max, average without group by for such a select statement.
Is there a disription somewhere (not the one in the informationcenter.informationbuilders.com, because there are only little information)or do I have to try every single combination of SQL statements?
WebFOCUS 8.1.05 Windows, All Outputs
September 11, 2018, 09:17 AM
Frans
Well it's not a matter of how WebFOCUS works, Oracle just doesn't multiple values per cell.
Perhaps you want to run the whole SQL with SQL passtrough?
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.
WebFOCUS 8.1.05 Windows, All Outputs
September 11, 2018, 09:57 AM
Frans
DB_EXPR will work like this in the sql quer if this is the code:
DEFINE FILE TABLE
TEST/I11 WITH ID = DB_EXPR(select max(value) from table);
END
TABLE FILE TABLE
PRINT ID TEST
END
SQL to Oracle: select (select max(value) from table), ,id from table;
This wouldd not work in Oracle: select (select value from table group by value), ,id from table;
Ok, this is how far I already been. Which commands are not working? Except of Group by? Etc.
Sothat there is no Need to try every single SQL Code...
But thanks so far @Frans
WebFOCUS 8.1.05 Windows, All Outputs
September 12, 2018, 08:45 AM
dhagen
jenni,
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
FROM (
(
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