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: No events within the next 45 days.

Aliases in Union Query?

Goto page 1, 2  Next
 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Thu Dec 07, 2017 2:04 pm 
Post subject: Aliases in Union Query?

Total novice to WEBI here ...

I'm trying to get some data out of a universe by means of a union query, but I'm expecting to have define an alias? (It's not working, so I'm thinking this is the problem)

In SQL I would just do this:
Code:
SELECT wbs_projectID as ref_code FROM UNIVERSE
UNION
SELECT project_projectID as ref_code FROM UNIVERSE

Where does one set Aliases, or is that not required?

[Moderator Edit: Added code formatting - Andreas]
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 2:37 pm 
Post subject: Re: Aliases in Union Query?

No, there's no need to define an alias for UNION queries.

I assume you've added the UNION in the query panel? What is the problem tha you're having?
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Thu Dec 07, 2017 2:57 pm 
Post subject: Re: Aliases in Union Query?

Running Query
---------------
SQL generation failed. (Error: INF)
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 3:04 pm 
Post subject: Re: Aliases in Union Query?

That's an ugly one; it could be a number of different things. There was no other text in the error message?
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Thu Dec 07, 2017 3:37 pm 
Post subject: Re: Aliases in Union Query?

Nope.
I'm currently approaching the problem by using "extended properties"... But its not pretty.
Back to top
mikeil
Principal Member
Principal Member



Joined: 18 Feb 2015

Posts: 493
Location: Europe



PostPosted: Fri Dec 08, 2017 9:03 am 
Post subject: Re: Aliases in Union Query?

Both projectID have the same data type?

When you select them individually, there is no problem?

They're both from the same universe?
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Fri Dec 08, 2017 2:55 pm 
Post subject: Re: Aliases in Union Query?

Yep. String, 8 characters. Same universe.
Back to top
mikeil
Principal Member
Principal Member



Joined: 18 Feb 2015

Posts: 493
Location: Europe



PostPosted: Fri Dec 08, 2017 3:17 pm 
Post subject: Re: Aliases in Union Query?

Try selecting them through separate queries, and merging the dimensions to see what happens.
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Sun Dec 10, 2017 2:30 pm 
Post subject: Re: Aliases in Union Query?

Well, That is what I am currently doing....

WEBI > My Document > Design > Document Summary > Edit > Options > Extend Merged Dimension values

as this post suggests

https://www.clariba.com/blog/tech-20120407-a-workaround-for-union-statements-in-sap-businessobjects-bi4-webintelligence-clariba

Thing is that what follows is very laborious ...

So my two queries which I wold normally just union, I have to deal with as follows:
1. Create a variable for the merged dimension.

2. Create a variable for the query 1 Project Manager as a detail of the merged dimension - call it "No1-PM"
3. Create a variable for the query 2 Project Manager as a detail of the merged dimension - call it "No2-PM"
4. Create a new variable on a formula if No1-PM is NULL then query 1 PM ELSE No1PM - call it "The Project Manager"

Repeat steps 2-4 for each field I want in my report.

(It's complex, and this description may not be 100% accurate, but you get the idea)
I just know that if my Union Query approach wored, this would be vastly simpler!
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13976
Location: London


flag
PostPosted: Mon Dec 11, 2017 3:37 am 
Post subject: Re: Aliases in Union Query?

Don't know if this post may help guide you.

http://www.forumtopics.com/busobj/viewtopic.php?p=653844

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Mon Dec 11, 2017 3:40 pm 
Post subject: Re: Aliases in Union Query?

Well, that's Union Queries 101... I know all that. Thanks anyway.

What makes it really difficult is that I'm never sure about whether the underlying universe's / SQL stuff is right. In this organisation those roles are seperated out.

So I have no access to detailed error logs, etc, to try understand why it doesn't work.
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13976
Location: London


flag
PostPosted: Tue Dec 12, 2017 4:27 am 
Post subject: Re: Aliases in Union Query?

Maxcot wrote:
Well, that's Union Queries 101... I know all that. Thanks anyway.

What makes it really difficult is that I'm never sure about whether the underlying universe's / SQL stuff is right. In this organisation those roles are seperated out.

So I have no access to detailed error logs, etc, to try understand why it doesn't work.


If you knew all that, what made you think you had to declare an alias icon_rolleyes.gif .
You don't.

Does the generated BO union code run in a SQL tool?

Does the proposed union contain the same amount of columns with the correct data types, on both sides of the query.
Sometimes you can create dummy universe objects that return blanks to help create Unions In BO.

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Maxcot
Forum Member
Forum Member



Joined: 06 Dec 2017

Posts: 9



PostPosted: Tue Dec 12, 2017 1:56 pm 
Post subject: Re: Aliases in Union Query?

"...Does the generated BO union code run in a SQL tool? ..."


I would very keen to try that... but where could I find the code? As I mentioned... I'm a SAP BO noob
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 10852
Location: Manchester, UK


flag
PostPosted: Wed Dec 13, 2017 6:01 am 
Post subject: Re: Aliases in Union Query?

Head left from the Run Query button in your query designer.

From there, you'll see an icon that looks like a piece of paper. That's the view script button and will show you the generated SQL.

_________________
Current version I'm using: 4.1 SP3 over Oracle
Next available for contract or consultancy work: Jan 2nd 2018
______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13976
Location: London


flag
PostPosted: Wed Dec 13, 2017 10:59 am 
Post subject: Re: Aliases in Union Query?

Mark P wrote:
Head left from the Run Query button in your query designer.

From there, you'll see an icon that looks like a piece of paper. That's the view script button and will show you the generated SQL.


If he is still getting the SQL can't be generated error, then he wont be able to view the SQL.

I believe his Union query may cross two universe contexts, therefore he gets the SQL generation error.

https://www.clariba.com/blog/tech-20120407-a-workaround-for-union-statements-in-sap-businessobjects-bi4-webintelligence-clariba

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
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.0143 seconds using 17 queries. (SQL 0.0035 Parse 0.0003 Other 0.0105)
CCBot/2.0 (http://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