Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] db_expr direct SQL
Go
New
Search
Notify
Tools
Reply
  
[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, 2017Reply With QuoteReport 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: 36 | Registered: October 07, 2015Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 389 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 389 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 389 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 389 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.