This is my first post, as I am a new Business Objects user!
I am creating a Universe which includes two tables: Circuit and District. Both tables are included in the Universe I am building. I want to create a Derived Table which contains each circuit and the number of districts for each circuit.
When I enter the following SQL into the Derived Tables SQL Expression textfield and click the Check Syntax button:
SELECT
jfactdba.circuit.circuit,
count(jfactdba.district.district) as number_of_districts
FROM jfactdba.circuit, jfactdba.district
WHERE jfactdba.district.circuit = jfactdba.circuit.circuit
GROUP BY jfactdba.circuit.circuit
I receive the following error: Exception:DBD,[Informix][Informix ODBC Driver][Informix]A syntax error has occured.State:42000
Does anyone have any ideas on what I am doing wrong or what I am missing in the SQL Expression field?
The SQL syntax should be correct, because it produces the right results when the SQL is run against the database in Server Studio.
I am using BusinessObjects XI R2 with an Informix database.
If the SQL is really valid then try giving the first column the name too. Something like :
SELECT
jfactdba.circuit.circuit AS CIRCUIT_NAME,
count(jfactdba.district.district) as number_of_districts
FROM jfactdba.circuit, jfactdba.district
WHERE jfactdba.district.circuit = jfactdba.circuit.circuit
GROUP BY jfactdba.circuit.circuit
Did you try basic troubleshooting such as trying to create a derived table along the lines of:
SELECT
jfactdba.circuit.circuit
FROM
jfactdba.circuit, jfactdba.district
WHERE
jfactdba.district.circuit = jfactdba.circuit.circuit
SELECT
count(jfactdba.district.district) as number_of_districts
FROM
jfactdba.circuit, jfactdba.district
WHERE
jfactdba.district.circuit = jfactdba.circuit.circuit
Not sure if Informix is supported with this option anyway…
i’m working in oracle so i’m just guessing but
does the account in the connection need the jfactdba mentioned?
perhaps it already has access and doesn’t need it
SELECT
circuit.circuit
FROM
circuit, district
WHERE
district.circuit = circuit.circuit
I have tried the suggestion from Andreas by attempting to create a basic derived table:
SELECT
jfactdba.circuit.circuit
FROM
jfactdba.circuit, jfactdba.district
WHERE
jfactdba.district.circuit = jfactdba.circuit.circuit
and the suggestion from Inge:
SELECT
circuit.circuit
FROM
circuit, district
WHERE
district.circuit = circuit.circuit
but both suggestions give the same error, Exception:DBD,[Informix][Informix ODBC Driver][Informix]A syntax error has occured.State:42000
I have renamed the tables in Universe Designer and the SQL in the Dervied Table so the jfactdba is removed from the owner name and still received the same error message.
I am using version 11.5.0.0 according to About Designer from the Help menu. Has anyone had any problems creating derived tables using version 11.5.0.0?
Informix does not support derived table syntax ( well, not without using “TABLE(MULTISELECT())” to identify that it is a derived table ). The latest release of Informix, 11.X does support the “standard” syntax.
We are using Informix Dynamic Server 2000 (IDS) Version 9.21, which would explain why I am unable to create a Derived Table.
According to documentation, the TABLE(MULTISET(SELECT…FROM…WHERE) is added in IDS Version 9.40; thus, the TABLE(MULITSET()) function is not working for me either, being we are using IDS Version 9.21.
Using other database providers, I have success. I was able to create a Derived Table using the provided sample eFashion Universe from Business Objects, which uses an Access Database. Furthermore, I was able to create a Derived Table in Universe Designer using an Oracle 10g Express Edition (XE) database.
From this information, i can conclude that this is not a Business Objects Universe Designer problem, but more an Informix problem.
Thank you guys for helping me identify the problem,
Depending on the version of Business Objects “select *” may or may not work. It has been shown to work in later versions, and the derived table gets all of the columns from the underlying tables. However, I consider this a bad practice.
Normally you would explicitely list every column you want to use.
Derived tables aren’t supported with the standard sql syntax in Informix 9.4. The required syntax in 9.4 is “TABLE(MULTISET( YOUR SELECT ) )”, which I could never get working in BOBJ. I ended up creating a view instead. Derived tables with standard syntax are supported in Informix 11.
so, pre-11 - notice the “table(multiset(” that surrounds the derived table definition:
select o1.*
from
orders o1, table(multiset(
select max(order_num)::integer as order_id,
c.customer_num::integer as customer_id
from orders o0, customer c
where c.customer_num = o0.customer_num group by 2
)) max_orders
where o1.order_num = max_orders.order_id
with 11, standard syntax is allowed:
select o1.*
from
orders o1, (select max(order_num) , c.customer_num
from orders o0, customer c
where c.customer_num = o0.customer_num
group by 2
) as max_orders(order_id,customer_id)
where o1.order_num = max_orders.order_id
Whenever I included the “table(multiset( MY QUERY ) )” in designer, I think I would get syntax errors. I remember trying a lot of things before giving up and creating a view instead.
(BOB member since 2007-08-15)
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)
(BOB member since 2004-06-22)