I’m new to Business Objects and I have not been able to find a solution to my problem in the “Designer’s Guide” book or the online help in Designer.
I’m hoping someone here can give me some direction.
I have 5 SQL statements that I need to implement in Designer, and some pseudo-code logic indicating how the results of the SQL statements interact. Here is a simplified version of the logic…
A) SQL statement #1 selects all sold items from my sales & inventory
database.
B) For each item in the results of SQL#1, a flag determines whether it
is a single “solid” item, or a “bundle” of items. C) For “solid” items we must use two queries:
SQL#2 - picks up inventory data for solid items sold as stand-alones.
SQL#3 - picks up inventory data for solid items sold as part of a bundle.
There is no way to tell which of the above applies until they are run (every item will have data returned by one of these queries, but never both).
D) For “bundle” items identified in step (B), run SQL#4
E) The results of SQL#4 contain a flag that indicates which of the two
queries to use to retrieve inventory data for the bundle:
SQL#2 - used for bundles sold as stand-alones. SQL#5 - used for bundles sold as part of a larger bundle.
I’ve created an ITEM class in designer that contains all the fields the users will need.
But I don’t know where to put this SQL and the logic that goes with it. I’ve tried setting up SQL#1 as a condition for the ITEM class, but where do I go from there?
I’ve considered combining everything into one big SQL statement, but that would involve re-quiring the database with the same select statements several times… unless there is some sort of temporary table functionality that would allow me to hold the results of one query to use in other queries.
I don’t have the time to really figure out a solution for you, but to have a temporary table, you should try using derived tables. I don’t know what database you are using, but Teradata systems allow us create derived tables, which is another query given a name in the ‘from’ clause. I hope this simple example helps.
Select table1.name, table2.address
from table1,
(select account_id, address from table3
where zip = 73071) table2
where table1.account_id = table2.account_id;
Kurt Kerchner
Southwestern Bell
(314)340-9853
Fax#(314)235-4679
I’ve considered combining everything into one big SQL statement, but that would involve re-quiring the database with the same select statements several times… unless there is some sort of temporary table functionality
that would allow me to hold the results of one query to use in other queries.
This is a universe design / consulting issue, and many more details would be required to provide a solution for you; such as what tables are you using, what’s your RDBMS / middleware (so you know what SQL functions you have at your disposal), what’s the ultimate target of the report, etc. When you design a universe you need to break it down to object level, and think SQL later, otherwise you can end up with a bunch of SQL programs, and no user friendly / open architecture / flexible design for the end users.
Any way, a couple of hints - aliases and contexts.
Aliasing tables enables you to create a pseudo view and specific / complex SQL streaming into the table.
Contexts enable you to create multiple paths through your database, without iteration / looping of data.
It sounds as though with a combination of alias / context and SQL in the joins, the solution would be relatively straightforward. In Designer you can achieve just about anything if you combine the right functionality.