Focal Point Banner


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 myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] db_expr direct SQL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] db_expr direct SQL
 Login/Join
 
Platinum Member
posted
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
 
Posts: 177 | Registered: October 17, 2017Report This Post
Silver Member
posted Hide Post
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
 
Posts: 45 | Registered: October 07, 2015Report This Post
Platinum Member
posted Hide Post
Thanks @Mike Williams, but I am looking for examples, where "select whatever" is used


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Guru
posted Hide Post
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.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 177 | Registered: October 17, 2017Report This Post
Guru
posted Hide Post
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.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 177 | Registered: October 17, 2017Report This Post
Guru
posted Hide Post
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.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 177 | Registered: October 17, 2017Report This Post
Guru
posted Hide Post
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.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] db_expr direct SQL

Copyright © 1996-2020 Information Builders