rsum causing ORA-01722: invalid number

[ry1633]

Hello,

I'm trying to troubleshoot a problem with a query item in my model.   For some reason est.code is doing some strange things.   In the output SQL from Report Studio you'll the RSUM clause, and running it throws a ORA-01722: invalid number

I have no idea why the SQL generates that RSUM clause.   est.code has a regular Attribute usage in my model with no special formatting.

with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           ESTABLISHMENT.CODE  as  Est_Code,
           RSUM(1  at ESTABLISHMENT.CODE, ENTRY_TEST.ID  for ESTABLISHMENT.CODE  order by ESTABLISHMENT.CODE asc, ENTRY_TEST.ID asc  local)  as  sc

[ry1633]

Here's a simplified version that is causing the ORA-1722 error.  I'd like to get rid of the stitched query.

with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc
     from
           ENTRY_TEST ENTRY_TEST
     group by
           ENTRY_TEST.ID
     order by
           Entry_Test_ID asc
    ),
D3 as
    (select
           AGENT_TABLE.AGENT  as  Agent,
           RSUM(1  at AGENT_TABLE.AGENT  order by AGENT_TABLE.AGENT asc  local)  as  sc
     from
           AGENT_TABLE AGENT_TABLE
     group by
           AGENT_TABLE.AGENT
     order by
           Agent asc
    )
select
       D.Entry_Test_ID  as  Entry_Test_ID,
       D3.Agent  as  Agent
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

[Michael75]

Code Select Expand


        bla bla bla
        from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

Hi,
Whenever you see generated SQL like this, alarm bells should start ringing! This means that Cognos has not not found a relationship defined in FM that it can use between the two tables on which you're trying to report, and rather than give up the ghost, it generates a fictitious key (always called 'sc') in each table, and performs a join on that. Even though your query will run, the results will be meaningless.

First step in debugging this is probably to go to the layer where you've defined your relationships (normally this will be Database Layer or Business Layer) Ctrl+click on the two query subjects, then right click and choose Launch Context Explorer.

Normally you'd see one or more dimension(s), joined to both ENTRY_TEST & AGENT_TABLE by 1-n relationships. I suspect that in your case you'll see something different.

Perhaps you'd care to take a screenshot of the Context Explorer diagram and post it here.

I'd like to get rid of the stitched query.

Note that stitch queries in themselves are not a bad thing, and are in fact the standard technique used by Cognos when reporting on multiple fact tables. You'll be able to find many good explanations of this on Cognoise, written by people far more competent than me. Executive résumé: what differentiates a "correct" stitch query is that instead of seeing a join on 'sc', you will see a join on

Code Select Expand

COALESCE (D1.KEY, D2.KEY)

Rgds, Michael

Edit: Another possibility is that your FM is modelled correctly, but that you need to include in your query some columns from the dimension(s) that join to the fact tables. Give that a try first.

[ry1633]

When I run the query with those two fields alone, it does run ok but it might be weird data.  Whenever I add in any other fields to query, it will crash with a ORA-01722: invalid number error - including adding fields that are inline with those other ones.   Because those two fields are in tables that are separated by two other tables, meaning I have to link through a total of 4 tables to get those two fields.   And so I added some more fields the query including the ID/PK fields of the tables that were in between and that did not help.

I checked the entities in Context Explorer and I didn't see any circular relationships.  I can't post my diagram though - has some organization-sensitive info in it.

[Michael75]

When I run the query with those two fields alone, it does run ok but it might be weird data.

Yes, it might run "ok" but as I said, the results will be meaningless because a fictitious key is being used to join the two tables.

Because those two fields are in tables that are separated by two other tables, meaning I have to link through a total of 4 tables to get those two fields.

These two other tables are dimensions, joined to ENTRY_TEST & AGENT_TABLE by 1-n relationships?

Your ORA-01722 error, is that the one you posted about some time ago? http://www.cognoise.com/index.php?topic=30163.0

This really needs to be resolved before you can get much further with your query. Either you have a column, defined as numeric, which contains illegal values, or (long shot here), you have a character field where you _know_ that it only contains numeric values, and so you're CASTing it to a numeric datatype, except that the assumption isn't quite correct.

[ry1633]

Is there a problem with the Agent field itself?  It validates OK when I validate the object.  Here are its properties

[Michael75]

It's not possible to say from your screenshot, which shows a normal character field,  whether the problem is with the AGENT column itself. All depends on what you're doing with it in your model. If you're joining AGENT to a dimension field which is numeric, this could be a problem. If you're doing summaries (other than count()) on it, idem.

You haven't exactly been forthcoming about your model, but I deduce that it looks something like the attached. If you care to amend this and post it back, that could help with debugging the problem.

But normally, by a process of elimination, i.e. adding and removing fields one at a time to / from your query, you should be able to identify the problem column(s)

[ry1633]

I'm pretty sure I checked for an eliminated any circular relationships at the db layer - but I can certainly recheck.   My model has some organization-sensitive info in it.   I'm trying to figure out a way I can post it but not have anything out there that shouldn't be.  :)

[Michael75]

I'm pretty sure I checked for an eliminated any circular relationships at the db layer

:o  :o
But nobody was talking about circular relationships! Personally, I've never encountered a circular relationship, and I'm not sure that I'd recognise one if it jumped out of the screen and hit me in the face  ;)

The generated SQL you posted in your 1st & 2nd mails has strictly nothing to do with circular relationships. As I tried to explain, the join on the fictitious 'sc' column came about because Cognos can not, on the basis of the relationships it finds in your FM model, define a join strategy for the query you're trying to test.

The discussion was purely about data types.
- Do the data types defined for the columns in your query really correspond to the actual data that these columns contain?
- In your FM model (which I'm not asking you to reveal in its entirety; with my mock-up I was just trying to get a handle so as to be able to give more relevant answers) . . . euh . . . oh yes, in your FM model, are you by any chance joining columns which have different data types?

Make a query which combines columns from both
- AGENT_TABLE & ENTRY_TEST, and
- the two tables (dimensions ??) in between

and if you hit ORA-01722 errors, progressively remove / replace columns one at a time until you can identify the column(s) causing the problem. Most importantly (I say this, because I don't always do it myself  :) ), note every combination of columns that you try, and the outcome of each test.

That's all I can offer, without more detailed feedback, and more precise replies to my questions.

Good luck
Michael

[ry1633]

We must have a different way of speaking.  My dba's and my entire development team have always called those circular relationships.

And yes, I have also tried a report query with only those two fields from those two query subjects.   Same error, and same RSUM in the generated SQL.

[ry1633]

Checking my data types.   ENTRY ID is LICENSE_ENTRY_ID and in Oracle it is a NUMBER(9) datatype.     AGENT is a VARCHAR(20).      As far as I can tell they came into Cognos that exact same type.       What do I need to do,  do I need to CAST() one of them differently?   I'd prefer to leave the ID field alone.

[ry1633]

Here are some of my examples of queries that do work and do not work.  Keeing in mind the datatypes as I have mentioned.  ENTRY ID is LICENSE_ENTRY_ID and in Oracle it is a NUMBER(9) datatype.    AGENT_ID is a NUMBER(9) datatype.  And AGENT is a VARCHAR(20).

1.  Here is a query that does successfully run, thought might give wrong/weird data

with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc
     from
           ENTRY_TEST ENTRY_TEST
     group by
           ENTRY_TEST.ID
     order by
           Entry_Test_ID asc
    ),
D3 as
    (select
           AGENT.AGENT  as  Agent,
           RSUM(1  at AGENT.AGENT  order by AGENT.AGENT asc  local)  as  sc
     from
           AGENT AGENT
     group by
           AGENT.AGENT
     order by
           Agent asc
    )
select
       D.Entry_Test_ID  as  Entry_Test_ID,
       D3.Agent  as  Agent
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)




2. This query does not run - as soon as I insert the Agent ID field, it crashes and hits the ORA-01722: invalid number error
with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc
     from
           ENTRY_TEST ENTRY_TEST
     group by
           ENTRY_TEST.ID
     order by
           Entry_Test_ID asc
    ),
D3 as
    (select
           AGENT.ID  as  Agent_ID,
           AGENT.AGENT  as  Agent,
           RSUM(1  at AGENT.ID,AGENT.AGENT  order by AGENT.ID asc,AGENT.AGENT asc  local)  as  sc
     from
           AGENT AGENT
            full outer join
           AGENT
            on (AGENT.AGENT_ID = AGENT.ID)
     group by
           AGENT.ID,
           AGENT.AGENT
     order by
           Agent_ID asc,
           Agent asc
    )
select
       D.Entry_Test_ID  as  Entry_Test_ID,
       D3.Agent_ID  as  Agent_ID,
       D3.Agent  as  Agent
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

[MFGF]

Here are some of my examples of queries that do work and do not work.  Keeing in mind the datatypes as I have mentioned.  ENTRY ID is LICENSE_ENTRY_ID and in Oracle it is a NUMBER(9) datatype.    AGENT_ID is a NUMBER(9) datatype.  And AGENT is a VARCHAR(20).

1.  Here is a query that does successfully run, thought might give wrong/weird data

with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc
     from
           ENTRY_TEST ENTRY_TEST
     group by
           ENTRY_TEST.ID
     order by
           Entry_Test_ID asc
    ),
D3 as
    (select
           AGENT.AGENT  as  Agent,
           RSUM(1  at AGENT.AGENT  order by AGENT.AGENT asc  local)  as  sc
     from
           AGENT AGENT
     group by
           AGENT.AGENT
     order by
           Agent asc
    )
select
       D.Entry_Test_ID  as  Entry_Test_ID,
       D3.Agent  as  Agent
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)




2. This query does not run - as soon as I insert the Agent ID field, it crashes and hits the ORA-01722: invalid number error
with
D as
    (select
           ENTRY_TEST.ID  as  Entry_Test_ID,
           RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc
     from
           ENTRY_TEST ENTRY_TEST
     group by
           ENTRY_TEST.ID
     order by
           Entry_Test_ID asc
    ),
D3 as
    (select
           AGENT.ID  as  Agent_ID,
           AGENT.AGENT  as  Agent,
           RSUM(1  at AGENT.ID,AGENT.AGENT  order by AGENT.ID asc,AGENT.AGENT asc  local)  as  sc
     from
           AGENT AGENT
            full outer join
           AGENT
            on (AGENT.AGENT_ID = AGENT.ID)
     group by
           AGENT.ID,
           AGENT.AGENT
     order by
           Agent_ID asc,
           Agent asc
    )
select
       D.Entry_Test_ID  as  Entry_Test_ID,
       D3.Agent_ID  as  Agent_ID,
       D3.Agent  as  Agent
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

Hi,

Have you spent any time trying to understand what this is doing, as Michael suggested? Look carefully at the SQL in each of these two query examples.

In both cases there is no common dimension linking the two facts. This should be setting off HUGE alarm bells that there are some fundamental flaws in your model. How can it be possible for there to be any sensible way of comparing facts from two different fact tables if there's nothing in your query that links them? Whether you get nonsense results or errors, it makes no difference in the end - the report is not producing anything that is remotely of use to the business - how can it?  Look at the first query, for example. On one side of the full outer join, the query is doing RSUM(1  at ENTRY_TEST.ID  order by ENTRY_TEST.ID asc  local)  as  sc - ie it is generating a running count for each value of ENTRY_TEST.ID. On the other side of the full outer join the query is doing RSUM(1  at AGENT.AGENT  order by AGENT.AGENT asc  local)  as  sc - ie it is generating a running count for each value of AGENT.AGENT. It is then using these running count values to join the two facts - ie 1 joins to 1, 2 joins to 2, 3 joins to 3 etc. How can this make any logical business sense? It simply can't. You might be getting errors where your second query is attempting to generate the same sorts of running counts, but the crux of the issue is your query shouldn't need to be doing this. You need to figure out how to bring in at least one conformed dimension that links the two facts, and this behaviour will cease. You're chasing a solution to a problem that shouldn't exist and only does exist because there's a problem in your model that allows unrelated facts to be brought in to the same reporting object. This is what you need to focus on fixing.

Just my opinion.

Cheers!

MF.

[ry1633]

The two tables are linked between two other tables for a total of 4 tables I have to go through.   I have tried bring the ID (primary key) fields of each table into the query in order - so I have a direct and sequential link between everything from the first to the last and every piece in between.  And it still gives the error.

[Lynn]

The two tables are linked between two other tables for a total of 4 tables I have to go through.   I have tried bring the ID (primary key) fields of each table into the query in order - so I have a direct and sequential link between everything from the first to the last and every piece in between.  And it still gives the error.

It might make sense to review the user guide and any training materials available with regard to star schemas, conformed dimensions, and cardinality. The query engine is not returning predictable and correct SQL based on the modeling approach you've taken. It doesn't matter how many joins you have if they aren't done according to the basic principles outlined in these materials.

The error is a red herring. You need a fundamental understanding of what is a fact and what is a dimension and how modeling decisions in Cognos need to be done in order to achieve meaningful and accurate results.

I would also suggest Ralph Kimball's books on data warehousing to help gain understanding on the type of structures that modern BI products like Cognos are expecting. Your database may not be specifically designed as such, but if you understand what it ought to be you can implement modeling techniques in Cognos that simulate it.

[MFGF]

The two tables are linked between two other tables for a total of 4 tables I have to go through.   I have tried bring the ID (primary key) fields of each table into the query in order - so I have a direct and sequential link between everything from the first to the last and every piece in between.  And it still gives the error.

Hi,

The idea is to bring in descriptive dimensional elements from a dimension that links to both facts (rather than bringing in keys) - have you tried this?

If you can do this and you're still getting odd results, it points to problems in your model. Can you post up the SQL you get in this situation?

MF.

[ry1633]

I should mention that I am not using Dimensions at all in this case.  These queries are in my Business layer and they are from just straight query subjects in my Database later

[MFGF]

I should mention that I am not using Dimensions at all in this case.  These queries are in my Business layer and they are from just straight query subjects in my Database later

Hi,

I really think you should heed Lynn's advice and review the user guide and if possible take some FM training. When we are referring to dimensions, this is not DMR - it's pertaining to the fact that the query engine will treat each of your query subjects as either a fact table or a dimension table, based on the cardinalities of the relationships that link them together. Where it finds (what it assumes to be) more than one fact table, the query engine generates a stitch query, and uses the common (conformed) dimension attributes in the query to stitch the facts together. Understanding how (and why) the query engine does this is fundamental to being able to create a model that delivers accurate, predictable results. This is why we're recommending you read up on / get some training in these areas.

Cheers!

MF.

[ry1633]

I did take Cognos FM training more than a year ago - this is the first time in my project I've ever encountered this problem.  And my training course was so big that it was like a big drink from a fire hose;  I likely didn't know enough at the time to even know what questions I'd have.   (middle age memory loss doesn't help matters :D )

Excerpt I found from my training materials:
"If automatic aggregation is not enabled and at least one conformed dimension is present, you will see the same stitch column generated in both derived tables of a stitch query. This stitch column takes a common key(s) from the conformed dimension(s) between the two queries and sorts it ascending locally on the IBM Cognos server. These columns and others then merge the two result sets. The fact values in the fact columns will be related to the conformed dimension but not necessarily to each other.
If automatic aggregation is not enabled and there are no conformed dimensions present, IBM Cognos will attempt to generate a stitch column by selecting a column from each query and using it to create unique values that will merge the queries. There are no definite relationships between the facts.
In either case RSUM(1....asc local) as sc is cause for investigation to ensure the correct results are returned."

Ergo...  I think the 2nd scenario is more like what I have.  I don't I have any conformed dimensions present.   Question being, should I?    Or... how do pull together a multi-fact query which is more like what I have.

[ry1633]

I think I have may have discovered a potential workaround.   A closer examination of the columns showed there was an Agent_ID in the Entry_Test table.  And since there was no direct link between the Entry_Test table and the Agent table (there were 2 tables between them for a total of 4 that the query engine was trying to make sense of...and probably failing).  So I drew a PK/FK relationship directly between those two on Agent ID and it works well now.  I just left it as a full outer join will now,  I will have to talk to my users next week and check how they want to see things.    So this is a new query now that runs just fine with no errors.  Much cleaner, and no RSUM...  :)

select distinct
       ENTRY.ID  as  Entry_ID,
       AGENT.AGENT  as  Agent
from
       ENTRY_TEST  ENTRY_TEST
        full outer join
       AGENT   AGENT
        on (ENTRY_TEST.AGENT_ID = AGENT.ID)
        full outer join
       ENTRY   ENTRY
        on (ENTRY_TEST.ENTRY_ID = ENTRY.ID)
order by
       Agent asc