18

I have a table with several columns. I want to do essentially:

select * from myTable and CAST(COLUMN1 as INT) where STUFF

and get all the columns as well as my first column casted as an int. Obviously this does not work.

I know I can do the following, but its not portable whatsoever incase I need to add more columns or what have you. This is so heavy handed, and it makes my sql statements extremely long. 

select (CAST COLUMN1 as INT), COLUMN2, COLUMN3, COLUMN# from TABLE where STUFF

So my question is simply, can I query all rows and columns and just cast one of them?

If it matters, I am using DB2.

EDIT: I just tried to run the command listing out all the columns and SQuirreL wont even execute it.

R4F6
  • 782
  • 1
  • 9
  • 26
  • 1
    You can do `Select Cast(Column1 As Int) As NewColumnName, * From Table Where Stuff`, but otherwise, you'll have to explicitly name the columns. – Siyual Aug 13 '14 at 22:54
  • 4
    FWIW, Its better coding practice to name the columns that you want to query. Its very rare (or should be) that you'd actually want to display to your user every column in a table at a given time. – crthompson Aug 13 '14 at 22:56
  • I get an error when I try to perform that select. Should it be something like select cast(Column1 as int as NewColumnName) ...? @paqogomez, I need this for data export purposes. – R4F6 Aug 13 '14 at 22:59

1 Answers1

18

If you can handle all the columns from the table, I would guess you could handle all the columns plus 1. So, do the conversion and rename the value:

select t.*, CAST(COLUMN1 as INT) as column1_int
from myTable t
where STUFF;

Otherwise, you have to list each column separately.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    To reiterate what @paqogomez said in a comment on the post...There are very few instances where you should be using `SELECT *` in production code. You should almost always explicitly list columns so that you can add columns to the table without changing your app unless it happens to also need to change to use the new column. Same idea applies to `INSERT`. Use an explicit column list so new columns can be added with an appropriate default without changing the app. – Charles Aug 14 '14 at 13:09