Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

can one column hold different data types?

Posted on 2008-10-15
6
Medium Priority
?
162 Views
Last Modified: 2012-05-05
i think my answer is no. this is the situtation:
i have one column that now is "money". But i have data that i want to hold in this column that's percentage as well. right now, when i display this column thru my code...i see the percentage as $:

 select total from tableand when i display it in code..i see:

My amount: $6000
My percentage: $8.1  (this should be 8.1%)

--- i thought about having it as varchar, then casting it. i thought about having a case statement...
case when  "money" then <cast value as money>
       when "percentage" then <cast value as percentage>

but not sure..any ideas/
0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22720631
the casting would work,
the question is, who puts the $ sign there
that is probably done by the client you use and not by the sql server engine
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1000 total points
ID: 22720648
there is also a datatype called sql_variant which can holds different data types in different rows and when you select that column you have to cast it but i don't know if you want to use that data type
it is rather complex and error prone
0
 
LVL 7

Author Comment

by:Camillia
ID: 22720910
i think the client puts $. Not sure tho..wouldnt "money" field put $ automatically? have to look into it...

but still with sql_variant, we have to "cast". it's ok tho. I'll look into it.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Author Comment

by:Camillia
ID: 22725242
what's wrong with this case when i try to covert?? get an error with percent

select
   case coltype when 'A' then convert(money,colvalue)
                when 'P' then convert(percent,colvalue)
                else colvalue
  end
from testtable
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22725354
percent is not a data type.

select
   case coltype when 'A' then cast(convert(money,colvalue) as varchar(20))
                when 'P' then cast(convert(decimal(20,2),colvalue) as varchar(20)) + '%'
                else colvalue
  end
from testtable

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 22725382
yes, silly me, just looked at BOL. let me try your method. Kamila,
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question