Focal Point
[CLOSED] db_expr direct SQL

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4367057096

September 10, 2018, 07:07 AM
jenni
[CLOSED] db_expr direct SQL
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,


WebFOCUS 8.1.05
Windows, All Outputs
September 10, 2018, 12:18 PM
Mike Williams
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


WF Version 8105
September 11, 2018, 02:49 AM
jenni
Thanks @Mike Williams, but I am looking for examples, where "select whatever" is used


WebFOCUS 8.1.05
Windows, All Outputs
September 11, 2018, 03:16 AM
Frans
for example:

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


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 11, 2018, 03:45 AM
jenni
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.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 11, 2018, 08:42 AM
jenni
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?


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?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 11, 2018, 09:45 AM
jenni
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;


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 12, 2018, 02:52 AM
jenni
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