October 8, 2008 at 5:04 am
Hi Guys
I am down to one column that causes a problem. I have a simple INSERT:
INSERT INTO dbo.myTable(Code,Car, Amount)
SELECT
[AR] As [Code], --Year
[SDF] As Car , --Vendor
[SVB] As Amount --Amount
FROM tmpmyTable
If I delete the Amount it works. With the Amount it causes :
"Error converting data type nvarchar to real."
The strange thing is that Amount is already a real in the tmpmyTable.
Also strange is the following:
SELECT [SVB] FROM dbo.tmpmyTable WHERE [SVB] LIKe '%[a-Z]%'
Results in:
-156.9
99.9
-7.8
1.8
-1.5
So does it mean that rows have chars instead of a real?
Any ideas?
October 8, 2008 at 5:11 am
metalray (10/8/2008)
Hi GuysI am down to one column that causes a problem. I have a simple INSERT:
INSERT INTO dbo.myTable(Code,Car, Amount)
SELECT
[AR] As [Code], --Year
[SDF] As Car , --Vendor
[SVB] As Amount --Amount
FROM tmpmyTable
If I delete the Amount it works. With the Amount it causes :
"Error converting data type nvarchar to real."
The strange thing is that Amount is already a real in the tmpmyTable.
Also strange is the following:
SELECT [SVB] FROM dbo.tmpmyTable WHERE [SVB] LIKe '%[a-Z]%'
Results in:
-156.9
99.9
-7.8
1.8
-1.5
So does it mean that rows have chars instead of a real?
Any ideas?
Can you post the DDL of
- tmpmyTable (and the source table if you use select ... into tmpmyTable from...)
- myTable
Also keep in mind you are using the column assumed datatype real in the where clause of your query, comparing it with characters !
SELECT [b]SVB[/b] FROM dbo.tmpmyTable WHERE [SVB] LIKe '%[a-Z]%'
So that will also give conflicts.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2008 at 5:30 am
Hi ALZDBA,
well I was just checking if some of the rows (real) contain characters. - they don't, but I still get a few:
-156.9
99.9
-7.8
1.8
-1.5
Odd right?
The source table is
CREATE TABLE [tmpmyTable](
[AR] [Nvarchar](255) NULL,
[SDF] [Nvarchar](255) NULL,
[SVB] [real],
)
The destination:
CREATE TABLE [myTable](
Code [Nvarchar](255) NULL,
Car [Nvarchar](255) NULL,
Amount [real],
)
October 8, 2008 at 7:48 am
Issue solved,
I just changed the order of columsn in my INSERT statement from
INSERT INTO dbo.myTable(Code,Amount, Car)
to
INSERT INTO dbo.myTable(Code,Car, Amount)
October 8, 2008 at 8:04 am
metalray (10/8/2008)
Issue solved,I just changed the order of columsn in my INSERT statement from
INSERT INTO dbo.myTable(Code,Amount, Car)
to
INSERT INTO dbo.myTable(Code,Car, Amount)
Hey, hey, ..... that's not what you posted first :doze:
INSERT INTO dbo.myTable(Code,Car, Amount)
SELECT
[AR] As [Code], --Year
[SDF] As Car , --Vendor
[SVB] As Amount --Amount
FROM tmpmyTable
Keep in mind the select list must provide the columns in th eorder of the insert statements list !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2008 at 8:38 am
Yes, you are right. Had a little error in my post.
I am working on a select case statement.
INSERT INTO CarType (ID,CarType)
SELECT ID,
'CarType' =
CASE -- for cartype
WHEN [CAR1] IS NOT NULL AND [AMOUNT] IS NOT NULL THEN [BigCarValue] --1st priority
WHEN [CAR2] IS NOT NULL THEN [SmallCarValue] --2nd priority
WHEN [CAR3] IS NOT NULL THEN [TinyCarValue] --3rd priority
END
FROM myTable
The problem is, that I always get the first WHEN even if being false (the two conditions) in that
case CarType is just empty.Somehow the
case statemtn does not take into account the second WHEN. I was trying to buit in multiple ELSE's but it did not work.
Any idea?
October 8, 2008 at 10:52 am
Yet another query 🙁
Maybe you'd be better off just copy/paste-ing the actual thing you want...
What is this selects giving you as a result:
Select count(*)
from myTable
where [CAR1] IS NOT NULL AND [AMOUNT] IS NOT NULL
Select sum (case when [CAR1] IS NOT NULL and [AMOUNT] IS NOT NULL then 1 else 0 end ) as Both_Not_Null
, sum (case when [CAR1] IS NOT NULL and [AMOUNT] IS NULL then 1 else 0 end ) as Amount_Null
, sum (case when [CAR1] IS NULL and [AMOUNT] IS NOT NULL then 1 else 0 end ) as Car1_Null
, sum (case when [CAR1] IS NULL or [AMOUNT] IS NULL then
case when [CAR2] is not null then 1 else 0 end else 0 end) as Car2_active
, sum (case when ([CAR1] IS NULL or [AMOUNT] IS NULL ) and [CAR2] is null then
case when [CAR3] is not null then 1 else 0 end else 0 end) as Car3_active
from myTable
INSERT INTO CarType (ID,CarType)
SELECT ID,
CASE WHEN [CAR1] IS NOT NULL AND [AMOUNT] IS NOT NULL THEN [BigCarValue]
WHEN [CAR2] IS NOT NULL THEN [SmallCarValue]
WHEN [CAR3] IS NOT NULL THEN [TinyCarValue]
else -1
END
FROM myTable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2008 at 2:32 pm
thanks a lot !
I will try it tomorrow!
October 10, 2008 at 2:06 am
great stuff, it worked !
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply