| Author |
Topic |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2008-11-06 : 10:04:51
|
| Hi ThereI have an sp which puts all of the max values of fields in a table into another table. (with Max_value field datatype varchar(max))Now when I view the max values of say large float fields i.e. AmountI will see 1.1904e+008, but when I copy & paste this value into Excel and change the column type to Text then I get the full value i.e.: 119040000Can anybody tell me how I would store this value 11904000 as it is in my SQL table?Thanks!!p.s The max values (irrespective of the datatype) need to be stored in one column of another table which has datatype varchar(max) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 10:06:37
|
| try casting the flaot values to decimal or numeric with maximum precision you want before appending to variable. |
 |
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2008-11-06 : 10:29:33
|
| Hmm... the issue is that I would need to know the datatype of the field which I'm trying to find the max value for, now my procedure will loop through each field (without me knowing the datatype) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 10:33:01
|
| actually what's the purpose behind creating this varchar(max) column with all variables? |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2008-11-06 : 10:44:46
|
| My purpose is to quickly identify all the min/max values of all fields in a table. Just to give me a picture of the range of values within the table.I'm not sure what datatype I can use to store all the MAX values of different variations of datatypes of fields.i.e. my Max_Value table could look something like this:Column_Name, Max_ValueName , ZXY LtdStart , 1990-01-01Employees , 99999Turnover , 9999999999999 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 11:00:00
|
| but why store it like this? you can always use MAX() function in select and retrive them in your selects. Any special reason for storing each column along with max value? |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2008-11-06 : 11:11:55
|
| The table (audit_Table) which stores all the values is created first, and then a procedure will loop through each field in the table take it's max value per field and insert into this Audit_Table. Hence I will need a datatype which will be able to store all the max values from the loop from each field.(Hope that makes sense!) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 11:18:12
|
| If anyways you're going to put max values of all columns into audit_table, why dont put it horizontally as a single row with all columns(that way audit_table will have exact structure of your main table) |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-11-06 : 11:25:27
|
| before convert it to VARCHAR(MAX) convert your FLOAT data type to DECIMAL select cast(cast(<float_field> as decimal) as varchar(max)) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 11:28:03
|
quote:
Originally posted by hanbingl
before convert it to VARCHAR(MAX) convert your FLOAT data type to DECIMAL select cast(cast(<float_field> as decimal) as varchar(max))
that was what i suggested in my first reply.But seems like OPs column datatypes will be determined only at runtime. |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2008-11-06 : 12:09:51
|
| Hi I've tried cast as varchar(max), but still no luck....It's fine if I just run the select statement, but when i insert into my table it displays 1.1904e+008 |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-11-06 : 13:43:38
|
| like visakh16 said, why not make the audit table the same structure as your data table:you can then get all the max values with 1 sql statement:select max(col1), max(col2), max(col3), max(col4)... from <table> |
|
|
|
|
large value in varchar(max) field...