Hi,
I have created two queries, please find below their (simplified) layout:
Query Afilters: a,b,c
layout:
Date 1 | Quantity 1 | ... |
1.1.2013 | 80 |
2.1.2013 | 100 |
3.1.2013 | 120 |
... |
Query 2filters: d,e,f
layout:
Date 2 | Quantity 2 | ... |
1.1.2013 | 40 |
2.1.2013 | 80 |
4.1.2013 | 100 |
... |
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 1 | Quantity 1 | Quantity 2 | ... |
1.1.2013 | 80 | 40 |
2.1.2013 | 100 | 80 |
3.1.2013 | 120 | 0 |
4.1.2013 | 0 | 100 |
... |
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.
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.
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.
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.
Both are dateTime data type.
Now moved to the Report Studio section.
MF.
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.