float vs decimal

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.

1.2334e+006 is not a legal literal for decimal. You will have to convert
in two steps, first to float, then to decimal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Am 21 Nov 2006 22:09:05 -0800 schrieb othellomy@.yahoo.com:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.

select convert(decimal(20,2),cast('1.2334e+006' as float))

bye,
Helmut

Float to Datetime Conversion

I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Thanks in advance,
sajmeraconvert(datetime,cast(cast(foo as integer) as char),112)|||Thanks for the quick reply. I also tried the following and got the result.

convert(datetime,convert(varchar(20),convert(int,c onvert(float,<field name>))))

Thanks again!|||SELECT CAST(CONVERT(VARCHAR, Col1) AS DATETIME) AS NewValue
FROM Table1

flat file to xml

Hey, all. Is it possible to read a flat file in and convert it to xml in SSIS? Xml is not listed as one of the destination types... OR, is there some easy way to take a fixed width flat file and convert it to xml?

Thanks for any insight!

Jim Work

SSIS does not include an XML destinaiton component. The explanation that I've heard is that this is because XML is too rich and complex a format to easily map to. At least in the first release.

You should be able to do this using a Script Component as a destination, if you have a known XML format to which you should be writing.

|||"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim
|||

It's my pleasure to help. It's always great to see how people are using SSIS, and always better to learn from others' pain than from my own.

Take a look at this page: http://msdn2.microsoft.com/en-us/sql/aa336314.aspx

There are sample component source code projects and tutorials/documentation on creating custom SSIS components available here for download.

|||

Jim Work wrote:

"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim

I've run into this a few times recently myself, so I created an example and posted it to my blog. Hope it helps.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns[vbcol=seagreen]
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-Scott
Scott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>
|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns
>
|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
--
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> > I have this query in access and I woul like to convert it to SQL.
> > --
> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> > FROM DIMAGE INNER JOIN KEYC
> > ON DIMAGE.ProvID = KEYC.ProvID
> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> > GROUP BY KEYC.ProvID;
> > --
> >
> > the problem here is the Last function. I am running SQL 7 and it
returns
> > with "'Last' is not a known function."
> > Any sugestions?
> >
> > --
> > -Scott
> >
> >
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Scott,
>> Try MIN() and MAX().
>> HTH
>> Jerry
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>> > Hello,
>> > I have this query in access and I woul like to convert it to SQL.
>> > --
>> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
>> > FROM DIMAGE INNER JOIN KEYC
>> > ON DIMAGE.ProvID = KEYC.ProvID
>> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
>> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
>> > GROUP BY KEYC.ProvID;
>> > --
>> >
>> > the problem here is the Last function. I am running SQL 7 and it
> returns
>> > with "'Last' is not a known function."
>> > Any sugestions?
>> >
>> > --
>> > -Scott
>> >
>> >
>>
>

First statement from Access to SQL

I'm trying to convert this from Access to SQL:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL

But it won't accept the First statement, specifically "First" is not recognized.

First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC

Does anyone know a way around?I'd test this in the SQL Anaylyser:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, Top(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL|||Let a brother know if it works

And remember

The RedSkins are back and Championship will follow.|||Understand that the concept of first and last doesn't have any meaning

Don't know how access doesn't...but you can do

SELECT ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
GROUP BY ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7)

Or

SELECT TOP 1 ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
ORDER BY SUBSTRING(ITEM_NUMBER,2,7)

You need to know if you want the MAX or MIN, or to order by something

The order of data in a database has no meaning...

Go Ellie!

PS> Another fluf post on my part...damn...|||well, i wouldn't exactly call it a fluf post, but your second code example is wrong, it has a non-aggregate expression in the SELECT list along with the MAX aggregate, but it's lacking the GROUP BY clause

:cool: