July 14, 2010 at 2:50 pm
Hi all..
I have a table that has a char(20) field. I need to hit this table (and field) from an external BI application.
Usually, we will create a view for tables that we are accessing using our BI package, and I am hoping I can continue to do the same here. However, this one field named 'Quantity' is a char(20), and we need it to be decimal for BI. Can I use CAST or CONVERT somehow in the SQL to create my view to achieve this?
select
field1 as field1,
field2 as field2,
cast(Quantity as dec(7,7)),
filed4 as field4,
from dbo.mytable with (nolock)
...obviously the above does not work 🙂
Thanks so much..
D
July 14, 2010 at 3:14 pm
David Levine-327541 (7/14/2010)
Hi all..I have a table that has a char(20) field. I need to hit this table (and field) from an external BI application.
Usually, we will create a view for tables that we are accessing using our BI package, and I am hoping I can continue to do the same here. However, this one field named 'Quantity' is a char(20), and we need it to be decimal for BI. Can I use CAST or CONVERT somehow in the SQL to create my view to achieve this?
select
field1 as field1,
field2 as field2,
cast(Quantity as dec(7,7)),
filed4 as field4,
from dbo.mytable with (nolock)
...obviously the above does not work 🙂
Thanks so much..
D
Other than the comma after field4 and the mismatch (?) of the Quantity column size (char(20)) vs. the target data type (dec(7,7)) together with a probably unintended target data type (only values with -0.9999999<= value <= 0.9999999 will be valid) I don't see any reason why it shouldn't work.
How do you guarantee that there will be only values stored in that column that can be converted into your target format?
July 14, 2010 at 4:09 pm
Thanks for the reply Lutz...
Yeah - I am pretty green when it comes to SQL, so...
The last comma after field4 was a typo.
As for the data that is in the Quantity field, it will always be numbers, but for other reasons, the field itself is set as char(20). The data in that field could be 1 or 700 or 23,000.00 or 600,000.00 or maybe even 2,101.50.
I tried this:
CREATE VIEW dbo.vw_Inventory_Test
AS
SELECT
PLANT AS plant,
ITEM_NUMBER AS item_number,
ITEM_DESCRIPTION AS item_description,
BATCH AS batch,
EXPIRATION AS expiration,
STOCK_TYPE AS stock_type,
UOM AS uom,
CAST(QTY AS dec(18,2)) AS qty,
REPORTING_DATE AS reporting_date
FROM
dbo.tbl_Inventory WITH (nolock)
and I get an error "error converting data type varchar to numeric"...
Thanks,
D
July 14, 2010 at 4:18 pm
The problem is the comma.
You would need to remove the comma in order to be able to convert the value to a decimal format.
SELECT
PLANT AS plant,
ITEM_NUMBER AS item_number,
ITEM_DESCRIPTION AS item_description,
BATCH AS batch,
EXPIRATION AS expiration,
STOCK_TYPE AS stock_type,
UOM AS uom,
CAST(replace(QTY,',','') AS dec(18,2)) AS qty,
REPORTING_DATE AS reporting_date
FROM
dbo.tbl_Inventory WITH (nolock)
Edit: there shouldn't be a reason to use the NOLOCK hint.
July 14, 2010 at 4:29 pm
Aha... very cool - thanks Lutz.
One more question... I am looking at the result set from the view now, and numbers like 6,400.00 in the table show up as 6400 in the view. The lack of comma is obviously expected, but are the .00 not showing up because it is essentially a whole number? If I had 6,200.50 in my field would it show as 6200.50 in the view? I can test it, but I figured I would ask as long as you are being so helpful! 🙂
D
July 14, 2010 at 4:43 pm
I can't confirm what you describe:
Example:
DECLARE @tbl TABLE
(
field4 CHAR(20)
)
INSERT INTO @tbl
SELECT '1' UNION ALL
SELECT '700' UNION ALL
SELECT '23,000.00' UNION ALL
SELECT '600,000.00' UNION ALL
SELECT '2,101.50'
SELECT *,CAST(REPLACE(field4,',','') AS DEC(18,2)) as converted_to_dec18_2
FROM @tbl
Please provide some sample data in the same format like I did above to show us what you see.
July 14, 2010 at 5:05 pm
Heh... so I was not in query analyzer... I was just in SQL EM... when I select from the view in QA it shows up as expected... in EM it drops off the decimal and trailing zeros... silly...
Thanks so much Lutz!
July 14, 2010 at 5:08 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply