I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.
I need to run a query that multiplies one field by this above mentioned field.
Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?
Example:
Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage
Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123
I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.
This should be worked out as 234 * 1.23
Thanks for any help.
Quote:
Originally Posted by narpet
Hello all...
I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.
I need to run a query that multiplies one field by this above mentioned field.
Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?
Example:
Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage
Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123
I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.
This should be worked out as 234 * 1.23
Thanks for any help.
first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.
try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'
since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right
i did not test this query, is this right?|||
Quote:
Originally Posted by ck9663
first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.
try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'
since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right
i did not test this query, is this right?
I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.|||
Quote:
Originally Posted by narpet
I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.
then you use account number as the join key. whether it'll be an outer, left, right or inner join will be up to your requirement|||That worked perfectly. Thanks very much for the help!
