How many Derived tables can be places in universe?
I’m using BO 6.5.1.
Thanks in advance.
apo777 (BOB member since 2005-07-05)
Hi,
I amnot sure there is a limit to the number of derived tables that can be added. Why do you ask are you having an issue?
irish_stan 
(BOB member since 2003-05-13)
Yes. I have 14 derived tables in universe.
Export to repository is normal - no any errors.
When import universe to another PC it crashes.
Import to my PC is OK?
apo777 (BOB member since 2005-07-05)
- Does it give an error message?
- Can the user import other users without derived tables?
- What happens if you create a new universe with just 1 table and a simple derived table and try and import that?
irish_stan (BOB member since 2003-05-13)
1.The error message is “Exeption: DBD, ORA-24373:invalid length specified for statement State: N/A”
2.Other universes has not problem importing.
3. Universe with one Derived table is OK.
P.S. I see you from Manchester are you UNITED FAN?
apo777 (BOB member since 2005-07-05)
I am not sure just check if the length of the names given to the table and columns are not too long.
Architect2009 (BOB member since 2005-10-26)
Also it sounds like the SQL for your derived table might be too long. I would check that too.
Judy
JMulders 
(BOB member since 2002-06-20)
Looks like , some of the table name is large .
Error:The length specified for the statement is either 0 or too large.
Action: Specify a valid length for the statement.
Could you first do Tools->integity check in the universe and check for “Parse Objects” and Universe Structure. If you check all it’ll take long time to go. so for quick results , just check just 2 of them.
Let me know if it helps.
sun (BOB member since 2005-10-25)
These are results of “Check Integrity” without checking cardinalities and Thorough parsing
Integrity Status
Structure: OK
Parse Objects: OK
Parse Joins: OK
Parse Conditions: OK
Check Loop: OK
Check for Contexts: OK
apo777 (BOB member since 2005-07-05)
Hi Guys!
I got the similar problem even when I am using a single derived table with simplest possible query.
Does any universe setting are associated with this.
Any help is greatly appreciated.
Thanks & Regards
Girish
girish.grover 
(BOB member since 2004-11-19)
Please, which version of Oracle and Business Objects are you using? What is the exact SQL statement for your derived table causing the error?
Andreas 
(BOB member since 2002-06-20)
Hi,
I am using BO 6.5 and Oracle 9i.
The query I tried in Derived table was a combination of “Inline queries”…
SELECT B.REP_ID, B.TABC3, B.TABC4, M AS MISDATE, YTD YTD_DATA FROM
(SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x') A,
(SELECT REP_ID, TABC3,TABN32 N32 ,MISDATE M, TABC4 FROM <table_name> WHERE REP_ID='x') B
WHERE A.REP_ID=B.REP_ID AND A.TABC3=B.TABC3 AND TO_CHAR(A.M1,'DDMONYYYY')='31DEC'||TO_CHAR(TO_NUMBER(TO_CHAR(M,'YYYY'))-1) AND A.TABC4=B.TABC4 AND A.TABC3 = B.TABC3Even the query as simple as
SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x'throws the same error.
Please let me know if you need more info on this.
PS: I don’t get any error when I parse the SQL and I am able to run the sql in TOAD without any errors.
Thanks & Regards
Girish :? :? :?
girish.grover (BOB member since 2004-11-19)
girish.grover, I have a universe with 14 derived tables and there is no problem. Derived tables do have limitation on query size.
What exactly is your error message?
substring (BOB member since 2004-01-16)
The error message is
girish.grover (BOB member since 2004-11-19)
Hi,
I don’t think girish and apo777 are having the same problem. apo777 is talking about crashing (I think he means designer crashes during import) and girish has a parsing problem.
For both: look at the connection, are both pc’s using the EXACT same connection and user to logon ? I have seen a lot of errors with this, when it was a simple problem of not having teh proper rights…
For girish: the ORA error you get points to a column with the name State. What’s the sql here ?
regards,
Gerard
highandstoned 
(BOB member since 2005-08-01)
Hi All,
Let me rephrase my problem. This shall answer all the questions raised by Gerard, substring & Andreas.
I am using following tools.
BO 6.5
Oracle 9i.
I have a universe with derived table in it. The objective of derived table was to use inline queries with one table. When I created the universe with derived table on my machine, it worked fine. Any query that can be run on sqlplus works in the universe. I created reports using these derived tables. I didn’t find any problem working on my machine. After finishing my work I exported the universe and reports in the repository.
Later when I import the same universe on another PC, nothing works. In universe I can’t see the derived table definition. The report created using those derived table fails as it can’t find the table definition in the universe. The error I got while importing the universe on other machine is:
“Exception: DBD, ORA-24373:invalid length specified for statement State: N/A”
SELECT B.REP_ID, B.TABC3, B.TABC4, M AS MISDATE, YTD YTD_DATA FROM
(SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x') A,
(SELECT REP_ID, TABC3,TABN32 N32 ,MISDATE M, TABC4 FROM <table_name> WHERE REP_ID='x') B
WHERE A.REP_ID=B.REP_ID AND A.TABC3=B.TABC3 AND TO_CHAR(A.M1,'DDMONYYYY')='31DEC'||TO_CHAR(TO_NUMBER(TO_CHAR(M,'YYYY'))-1) AND A.TABC4=B.TABC4 AND A.TABC3 = B.TABC3SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x'SELECT REP_ID, TABC3,TABN32 FROM <table_name>Hi Gerard,
I don’t think its a connection problem. What I have learnt that the secured connection is available in BO repository. So the same shall be available on all the machines. In my case I am using the same user to access the same universe on two different machines. Please guide me if I am wrong here. What are the other problems you see related to rights. May be I can talk to our BO administrator if something is missing.
Looking forward to the experts for help.
Warm Regards
Girish
girish.grover (BOB member since 2004-11-19)
Hi,
just to be clear: you get the ORA error when improting the universe ? Not while using the universe ?
Did you try copying the universe by just copying the .unv file to the other pc ? See if it works then on the other machine ?
What happens if you delete the actual .unv file on your source machine (MAKE A BACKUP!!) and import it there ?
Regards,
Gerard
highandstoned (BOB member since 2005-08-01)
Hi Gerard,
- I got the ORA error when improting the universe.
- When I copied the .unv file on the other machine it worked.
- I had deleted the .unv file on my machine and reimported from the repository. I got error
Best Regards
Girish :?
girish.grover (BOB member since 2004-11-19)
Hi,
ok. It means that the universe itself is ok (and I hope you have a backup before you deleted it… 
) and the problem is in the import/export facility somewhere and isn’t pc related.
Try creating a new universe, create the same derived table in it with some objects and try import/export with that one (including the delete before import).
It sounds like there’s some problem in the repository. Did you check the repository for errors ? (in Supervisor)
If there were no reports on it, I would delete the universe from the repository but I wouldn’t do it now…
Perhaps stupid, but try exporting the copied universe in the other pc to the repository and import it on the source pc.
Regards and keep your backup safe…
Gerard
highandstoned (BOB member since 2005-08-01)
Hi Gerard,
I have the backup so its not a problem.
I tried creating a new universe the problem remains as is.
I tried creating a new universe through another user on the same repository, problem remains as is.
Now as u said…
Although I don’t have access to Supervisor. If u guide me how it can be checked. I will talk to BO Adminstrator about the same.
Well I am doing all these experiments on a new test universe so no problem for backups.
Still looking for answers.
Warm Regards
Girish 
girish.grover (BOB member since 2004-11-19)