Jason, UNION select assumes all following queries will have the same data types as the first query. As long as your first query has the bigcol, you can use an expression for the smallcol in the second query, and R:Base will do its best to cast smallcol into the data type of bigcol. Parentheses are all you need to select an expression in place of a column.
SELECT bigcol FROM MyTable WHERE <CONDITION> UNION SELECT (smallcol) FROM MyTable WHERE <CONDITION> Bill On Thu, Jan 8, 2009 at 10:40 AM, Jason Kramer <[email protected]> wrote: > Can someone please point me to the mailing list archives? I know the > answer to this question has been on the list before, but I can't find where > I saved the answer. Sorry about the duplication. > > I have two text columns that I want to UNION, but they are different > lengths (100 and 36, if it matters), so I get a Column type mismatch in > UNION (2372) error. Is there any way to cast or pad the smaller column into > a string that matches the larger one so the UNION will work? Something > along the lines of: > > SELECT bigcol FROM MyTable WHERE <CONDITION> UNION <CAST OR PAD> SELECT > smallcol FROM MyTable WHERE <CONDITION> > > I'm open to other suggestions. I am trying to take the contents of > multiple columns and combine them into one column in a view (I only need the > combined data temporarily). > Thanks, > Jason > > -- > Jason Kramer > University Archives and Records Management > 002 Pearson Hall > (302) 831 - 3127 (voice) > (302) 831 - 6903 (fax) > >
