BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

scalar sub-query syntax


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
craibuc
Principal Member
Principal Member



Joined: 19 Oct 2009

Posts: 124
Location: Minneapolis, MN


flag
PostPosted: Fri Jul 22, 2016 8:12 am 
Post subject: scalar sub-query syntax

Scalar sub-queries are in-line views that are expressed the SELECT portion of a SQL statement:

Code:

SELECT  id, name,
             (
             SELECT sum(quantity) FROM sales WHERE person_id=p.id
             ) total_sales
FROM    Person p


Assumptions:

    - the `Person` table has been added to the Data Foundation
    - there is a `Person` class, with`Name` dimension


Is it possible to add a `Total Sales` measure, using a SQL fragment that

    - uses one or more tables that have NOT been added to the data foundation
    - *may* be correlated to table added to the data foundation


I know I could create a derived table, but I'm hoping for a simpler solution.

Using a UNX universe.
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6632
Location: Connecticut, USA


flag
PostPosted: Fri Jul 22, 2016 8:21 am 
Post subject: Re: scalar sub-query syntax

Sure, but the aggregate function has to be on the outside of the subquery in order to the query engine to detect it as such. So:
Code:
SUM ((
     SELECT quantity  FROM sales WHERE person_id=p.id
)) total_sales

(Note the two pairs of parentheses)

You should make sure that the PERSON table is selected in the object's properties, since it will not be automatically detected.

The SALES table does NOT need to be included in the model.

I assume that "p.id" is a typo and should be "sales.id"?

Joe
Back to top
craibuc
Principal Member
Principal Member



Joined: 19 Oct 2009

Posts: 124
Location: Minneapolis, MN


flag
PostPosted: Sun Jul 24, 2016 9:33 am 
Post subject: Re: scalar sub-query syntax

Are the double parentheses the key? Would this be a valid way to create a dimension?

Code:
(( SELECT sysdate FROM dual ))


** edit **

This doesn't work; error message about missing table in the WHERE clause.
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6632
Location: Connecticut, USA


flag
PostPosted: Sun Jul 24, 2016 5:22 pm 
Post subject: Re: scalar sub-query syntax

What does the complete generated SQL look like?
Back to top
craibuc
Principal Member
Principal Member



Joined: 19 Oct 2009

Posts: 124
Location: Minneapolis, MN


flag
PostPosted: Tue Dec 13, 2016 9:04 am 
Post subject: Re: scalar sub-query syntax

Solved.

I needed to associate a table with the dimension (using the 'Tables...' button).

I was also able to created a correlated scalar dimension (like Crystal Report's Sql Expression field):

Code:

(
  SELECT value
  FROM    table
  WHERE  id = ANOTHER_TABLE.id
)


I then associated this dimension with ANOTHER_TABLE by selecting it in the dialog associated w/ the Tables... button.
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6632
Location: Connecticut, USA


flag
PostPosted: Tue Dec 13, 2016 10:05 am 
Post subject: Re: scalar sub-query syntax

craibuc wrote:
I needed to associate a table with the dimension (using the 'Tables...' button).


Yes, as mentioned above icon_smile.gif
joepeters wrote:
You should make sure that the PERSON table is selected in the object's properties, since it will not be automatically detected.
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 902
Location: Woodinville, WA USA


flag
PostPosted: Thu Sep 19, 2019 5:30 pm 
Post subject: Re: scalar sub-query syntax

I didn't see a version associated with the OP's question (I'm using 4.2 SP3). I was trying to include a subquery in the SELECT list of a derived table, which worked fine in SQL Server. But I couldn't get it to parse in IDT, so I took the subquery out of the derived table and added it as a measure instead. Funny thing was that I had to code the measure exactly the same way as what worked in SSMS.
Code:
(select sum(A.[Duration])  from  [my_table] A 
where A.[Task_sid]      = dt.Task_sid and
      A.[Location_sid]  = dt.[Location_sid] and
      A.[Entity_sid]    = dt.[Entity_sid] and
      A.[User_sid]      = dt.[User_sid]
        )

_________________
Regards,

Chaz
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6632
Location: Connecticut, USA


flag
PostPosted: Fri Sep 20, 2019 6:34 am 
Post subject: Re: scalar sub-query syntax

What error did you get in the derived table? Did you give the subquery an alias? It might help to wrap the entire query in another select, i.e., "select * from (select a,b,c,(select ...) from ...)"
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 902
Location: Woodinville, WA USA


flag
PostPosted: Fri Sep 20, 2019 10:39 am 
Post subject: Re: scalar sub-query syntax

While trying to recreate the error, I noticed that there was part of the fully-qualified table name missing in the IDT derived table. After fixing that, it worked! So, I've learned 3 things.
1. You can use subqueries in the SELECT clause of a derived table,
2. You can code subqueries as measures (I've always just used them as filters), and
3. Look harder when parsing fails.

Joe, thanks for making me think harder!

_________________
Regards,

Chaz
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0353 seconds using 18 queries. (SQL 0.0058 Parse 0.0010 Other 0.0286)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo