Manipulating Data

[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.

[ck9663]

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?

[narpet]

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.

[ck9663]

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

[narpet]

That worked perfectly. Thanks very much for the help!