UNION field is being truncated

[jcrouch]

Hi,

I have a report that is pulling data from 3 queries that are Unioned together. All 3 queries have a Data Item named 'Type'. In each query Type is the same, being 'Area', 'Region' or 'District'. In the final Union query I needed to know which table the data came from. But this Type field is truncated to 4 characters. I am getting 'Area', Regi' and 'Dist'.

Also I tried changing the 'Area' to 'Areaaa', thinking somehow the Union was using the length from the first query added to the first Union... it seems to be. after that change I get 'Areaaa', 'Region' and 'Distri'.

Is there a way to fix this?

Thanks,

Jeremy

[Lynn]

Put the District query as the first one in the union.

[jcrouch]

Thanks Lynn,

Yep, for me that works.

But is this a bug, or is it supposed to behave this way? What if you didnt know which query had the longest string?

edit...

Wait, no its even a bigger issue than that. I also have a price field in there, do I have to worry about that being truncated just because the longest field wasnt put into the Union first? There has to be something with this Im not seeing, I refuse to belive this is the way IBM designed it.

[Lynn]

I suspect it is an artifact of how union SQL works. If you wrote a union SQL statement directly against the database (outside of Cognos) you'd find that the first one in the set controls what is expected for the rest. At least this is what I've observed in the databases I've encountered over the years.

It is the datatype of the field that is what is important...not the content within it. In your case you used a hard coded string so the length of the first one in the set was the basis for setting the datatype. You know these ahead of time and know which needs to be first.

If you used database columns instead of hardcoded values you would know the datatype of each and would just put the longest one first. If one query used a VARCHAR(20) field and the other query used a VARCHAR(10) for that same column in the union, you'd put the VARCHAR(20) query first.

Easy as pie, right?  :o

[jcrouch]

Ah, that does make sense :) So I dont have to worry about the fields I pull from our data source, just 'Data Item' fields that I create myself.

This leads to a final question, can you set the Data Type for custom Data Item fields? Im guessing no since I cannot view the properties of these fields...

Thank again for clearing that up Lynn.

Jeremy

[Lynn]

Happy to help and I really hope the information I provided is correct ;-)

You could try using a cast operation to control the datatype. Maybe wrap your strings in something like

Code Select Expand

cast( 'Area', VARCHAR(20) )

That would let you put the queries in any order that suits your fancy and might avoid a bug in the future if the strings change and the longest one is no longer first in the set.

I'm not 100% sure that would work, so test it out before you rely on it if you decide to go that route.

[jcrouch]

Happy to help and I really hope the information I provided is correct ;-)

You could try using a cast operation to control the datatype. Maybe wrap your strings in something like

Code Select Expand

cast( 'Area', VARCHAR(20) )

That would let you put the queries in any order that suits your fancy and might avoid a bug in the future if the strings change and the longest one is no longer first in the set.

I'm not 100% sure that would work, so test it out before you rely on it if you decide to go that route.

I just tested it, works great. Ill stick this little tip in my ever growing Cognos library.

Thanks,

Jeremy