If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

JOIN QUERY - different result than expected

Started by icestorm, 11 Sep 2015 02:27:54 AM

Previous topic - Next topic

icestorm

Hi,
I have created two queries, please find below their (simplified) layout:

Query A
filters: a,b,c

layout:







Date 1Quantity 1...
1.1.201380
2.1.2013100
3.1.2013120
...

Query 2
filters: d,e,f
layout:







Date 2Quantity 2...
1.1.201340
2.1.201380
4.1.2013100
...

Then I created outer join between these two queries, because I want to combine Table A and Table B, into Table C, which should contain anything, which is in Table A or in Table B. Link between these tables ( Date 1 <-> Date2, 0...n).

What I expected Table C would look like








Date 1Quantity 1Quantity 2...
1.1.20138040
2.1.201310080
3.1.20131200
4.1.20130100
...

The problem is that figures in columns are absolutely different and I do not know why. Could you please help me?

Notes:
Date 1 and Date 2 are different date types in database, but both are dd.mm.yyyy hh:mm
How I expected that Join works: 1) Query A creates Table A 2) Query B creates Table B, 3) Join combines Table A with Table B
Some other columns are the same in both tables, but there are no links between them in JOIN

Thanks for your help.

Lynn

Query Studio does not allow you to create multiple queries and join them so I suspect you are posting this in the wrong forum.

You don't explain what the incorrect output is...you only say what you expect and that the figures you are getting are different. Different in what way?

Assuming you are working with Report Studio then I would suggest that the differing data types for your date column is the issue. What are the two data types? Having the same format may be irrelevant. Is one a date and the other a string? Or a date and a date/time? A number? It is likely you will need to cast the dates to a common data type in order to join them.

icestorm

I am sorry, it should have been sent to Report Studio section.

Figures are different in that way, that instead of 80 and 40 for 1.1.2013, the quantities are 456 780 and 1 256 478 (and those are not cumulated values).

Regarding datatypes, I do not know, where to find datatypes of date columns in Cognos.

icestorm

And by a different date types I meant that they have a different meaning : Date 1 represents the date which invoice was issued on , Date 2 represents the date  which customer received goods on.

icestorm


MFGF

Now moved to the Report Studio section.

MF.
Meep!

Lynn

Quote from: icestorm on 11 Sep 2015 03:21:44 AM
Both are dateTime data type.

Is the time component populated? Unless the exact date AND time match between the two it will not be joining as you expect. If the time component is always 0 then this isn't the issue. If you change the outer join to an inner join do you get any result at all? If it is an issue then you'd need to cast each date/time to a date to omit the time portion, thereby joining only by date.