Hi,
it's not clear to me how to use direct SQL
with DB_EXPR.

Can I just Group things or what are the possiboilities.

I know that I can only get 1 row.

Can someone make some examples with selects?
The normal SQL Functions are clear to me.

Something like
define..
example/A10 = db_expr(select substr() from db);
end

thanks

This message has been edited. Last edited by: FP Mod Chuck,

Hi Jenni,

dhagen gave a few examples in a past thread:

 PASCALESUM/I11  WITH DMHR.ID_NUM=DB_EXPR(SUM(PAYSCALE) OVER(PARTITION BY PLANT));
START_DOW/A10  WITH DMHR.ID_NUM=DB_EXPR( DATENAME( dw,  START_DATE ) );
START_MONTH/I2=SQL.MONTH( START_DATE );   

Here's the discussion thread, hope it's useful for you: http://forums.informationbuild...957091486#5957091486

Thanks @Mike Williams, but I am looking for examples, where "select whatever" is used

for example:

DEFINE FILE TABLE
TEST/I11 WITH ID = DB_EXPR(select max(value) from table);
END

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?

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.

We use Oracle Adapter, but Group by doesn't work.

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?

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.

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

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)
	)