Converting DB2 Timestamp Data to SQL Server 2005 - Problems with Unique Index

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

Redirecting errors from the FastLoad to a regular OLEDB destination is a technique that I've used before to capture only the row that caused the error - that should work fine.

I have another question, though. If you can change this timestamp value (as you are planning in step 3), it doesn't seem to be all that significant. Why not drop just add an identity column to the unique index?

|||

Adding an Identity column to the table and then adding into the index sounds like a good idea to get around the problem. Especially since the package is going to be scheduled to run every 15 to 30 minutes to sync the DB2 table to the SQL Server table during the migration time frame.

Thanks for the suggestion. I'll let you know what we have decided to do.

|||

Upon further inspection of this process and doing a small test this method is not an option for me.

The addition of an indentity column to the unqiue index would now in essence make the index non-unique. The identity column is a unique value and would then allow for the business rule of this date time column to be broken.

I am attempting to try and see if I can capture the data column as a string for manipulation, but right now the OleDB connection is pulling the catalog info from DB2 and knows that the column is supposed to be a date time. Any attempt to use the column in the SSIS package as a character I get errors.

Still looking for suggestions before starting down the path of writing an application or off the shelf tool to handle the sync between these tables.

Thanks.

|||

You should be able to use a derived column to convert the value to a string - that is probably your best bet to avoid violating the business rule.

Code Snippet

(DT_WSTR, 10) [ColumnName]

Converting Date-Time to Date

Hi: I am new to Reporting Services.
My issue is that I have a Date-Time Field which I want to use as a column heading for a crosstab. However since there are multiple date entries with varying times I get multiple column headings. Could anyone tell me how to convert this datetime field to a date field so that I only get one column per day.
ThanksThere are are number of VB.Net functions for manipulating dates which you
could use for this.
The simplest (but probably not most efficient) way would be to convert the
date to a string then back to a date: =CDate(CStr(Fields!DateTime.Value))
You might want to consider doing the conversion in your query instead.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:F6AFC614-180B-4C75-94B8-DB3149275D3D@.microsoft.com...
> Hi: I am new to Reporting Services.
> My issue is that I have a Date-Time Field which I want to use as a column
heading for a crosstab. However since there are multiple date entries with
varying times I get multiple column headings. Could anyone tell me how to
convert this datetime field to a date field so that I only get one column
per day.
> Thanks

Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

Converting date to Varchar? and Varchar to Date?

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?

I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

This should give you an idea of how to handle these conversions:

DECLARE @.MyDateTimeValue datetime
SET @.MyDateTimeValue = '2006-03-26 00:00:00.000'

SELECT convert( varchar(10), @.MyDateTimeValue, 101 )

-
03/26/2006

SELECT cast( '03/26/2006' AS datetime )

2006-03-26 00:00:00.000

Converting datatype on column...

What impact will changing the datatype of a column from
smalldatetime to datetime? Currently, I need to allow for record insertion
where the date may exeed year 2079.Eric wrote:
> What impact will changing the datatype of a column from
> smalldatetime to datetime? Currently, I need to allow for record
> insertion where the date may exeed year 2079.
That should be fine. The column will require twice the number of bytes
of storage in order to support datetime (8 bytes total). Newly inserted
values will show additional precision unless you account for this.
create table ABC (MyDate smalldatetime)
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00
2005-01-10 14:22:00
Alter Table dbo.ABC
ALTER COLUMN MyDate DATETIME
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
2005-09-01 17:46:57.827
2005-01-10 14:22:22.000
drop table ABC
David Gugick
Quest Software
www.imceda.com
www.quest.com

Converting data types

Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.

I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.

any ideas?

Obviously, as you know, you get this error because the conversion could not convert some of the data (as it was not in the correct format). First of all, you can use IsDate function to see which data is giving you problem. Other than that, try this post. Just as a precaution, backup the table or database before you try anything.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785290&SiteID=1

|||

Rather than trying to update the data type, try creating an additional field with a DateTime type. You can then copy all the dates that aren't null into this column and then drop the original column (and rename the new one if necessary).

|||

I would look through the data. When dates are stored as varchar, and especially if there isn't any validation when it is inserted into the database, you'll often see values like 01/01/3007 or 06/32/2007 or some other way it was entered that caused ambiguity with SQL Server. I've had the same thing happen in the past.

|||

Hi,

Review the Globalization configuration in the web.config, put en-US to work fine in the culture and uiculture attributes, by default is en-US, may be you change it.

greetings.

Converting data to UPPERCASE

Setup: SQL2000 Server running on Win2k Server
I can't find any difinitive answer to this. I need to convert the data in a
column to uppercase. Do I simply change the collation from the default of
SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
do this while the database is being replicated?
Thanks in advance for your help.
John SteenNo, changing case sensitivity will no change the casing of the data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:DBF3F536-9B8A-4426-B03D-C1647EDF170B@.microsoft.com...
> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>|||Why can't you just do an update?
UPDATE Table
SET col1 = upper(col1)|||John,
You have to update the column.
update t1
set c1 = upper(c1)
Changing the collation does not change the data.
AMB
"John Steen" wrote:
> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>|||Thanks, Alejandro. I was thinking that I not only had to change the
collation, but also reimport the data. This way makes more sense.
But back to the collation -- will changing it to
SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
Thanks,
John Steen
"Alejandro Mesa" wrote:
> John,
> You have to update the column.
> update t1
> set c1 = upper(c1)
> Changing the collation does not change the data.
>
> AMB
> "John Steen" wrote:
> > Setup: SQL2000 Server running on Win2k Server
> >
> > I can't find any difinitive answer to this. I need to convert the data in a
> > column to uppercase. Do I simply change the collation from the default of
> > SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> > do this while the database is being replicated?
> >
> > Thanks in advance for your help.
> >
> > John Steen
> >|||> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
force only uppercase data?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...
> Thanks, Alejandro. I was thinking that I not only had to change the
> collation, but also reimport the data. This way makes more sense.
> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> Thanks,
> John Steen
>
> "Alejandro Mesa" wrote:
>> John,
>> You have to update the column.
>> update t1
>> set c1 = upper(c1)
>> Changing the collation does not change the data.
>>
>> AMB
>> "John Steen" wrote:
>> > Setup: SQL2000 Server running on Win2k Server
>> >
>> > I can't find any difinitive answer to this. I need to convert the data in a
>> > column to uppercase. Do I simply change the collation from the default of
>> > SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
>> > do this while the database is being replicated?
>> >
>> > Thanks in advance for your help.
>> >
>> > John Steen
>> >|||No that wopn't force anything, you could however do this thru a instead
of trigger or from the front-end
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I couldn't find a good explanation, so it was an inferrence on my part.
Thanks,
John Steen
"Tibor Karaszi" wrote:
> > But back to the collation -- will changing it to
> > SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
> force only uppercase data?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...
> > Thanks, Alejandro. I was thinking that I not only had to change the
> > collation, but also reimport the data. This way makes more sense.
> >
> > But back to the collation -- will changing it to
> > SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> >
> > Thanks,
> > John Steen
> >
> >
> > "Alejandro Mesa" wrote:
> >
> >> John,
> >>
> >> You have to update the column.
> >>
> >> update t1
> >> set c1 = upper(c1)
> >>
> >> Changing the collation does not change the data.
> >>
> >>
> >> AMB
> >>
> >> "John Steen" wrote:
> >>
> >> > Setup: SQL2000 Server running on Win2k Server
> >> >
> >> > I can't find any difinitive answer to this. I need to convert the data in a
> >> > column to uppercase. Do I simply change the collation from the default of
> >> > SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> >> > do this while the database is being replicated?
> >> >
> >> > Thanks in advance for your help.
> >> >
> >> > John Steen
> >> >
>
>|||Thanks, Denis. I'll convert the current data and mention the trigger to our
developer.
John Steen
"SQL" wrote:
> No that wopn't force anything, you could however do this thru a instead
> of trigger or from the front-end
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Converting data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
Chris
Hi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

converting columns in an INSERT (was "SQL Query")

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...First of all, I'd strongly suggest that you list out the columns. That solves all kinds of problems before they get a chance to happen to you. If you are determined to do things the hard way, you don't have to enumerate the columns yourself, but I'd still recommend it.

You ought to be able to use any of those conversions if you like, but as long as the contents of the column can be converted to MONEY, the SQL Server engine ought to handle the conversion for you.

-PatP|||"SELECT *" is shorthand for "I'm a lazy programmer". I would never leave it in any finished code. Bad. Bad. Bad bad code.

Converting column values

I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.

Can anyone suggest an approach.I'd use something like:UPDATE myTable
SET bogusColumn = - Abs(bogusColumn)-PatP

Converting binary to string

is there a way to query a varbinary column and print the readable char in query analyzer?

thanks in advance!

Ryan

No way to do this, because SQL only store the binary data and has no sense for character encoding and structure of the doc.|||

There is a way, here it is...

declare @.pass varchar(30)
declare @.binPass varbinary(8)
set @.binPass = (select password from tblEMP where empid = '123')
set @.pass = (select cast(@.binPass as varchar(20)))
print reverse(@.pass)

I get characters like squares after the text, but I know to disregard them and only look at the text. Anyone know how to get rid of the squares and weird characters? thanks

Ryan

|||

SQL Server will convert varbinary to varchar what it will not convert is varbinary to text. Try the link below for the complete conversion chart and code samples. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

sqlsql

Converting a varchar to int

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.

But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9

A classic violation of first normal form:

http://en.wikipedia.org/wiki/First_..._ single_field
If at all possible, change your table to look like this:

Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

and then this simply becomes

SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)

That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.|||I can't change the actual table, but I can create a stored proc that
inserts it correctly into another table. I didn't even think to do
that (**duh**)! Thank you very much for your assistance!

Ed Murphy wrote:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

>
A classic violation of first normal form:
>
http://en.wikipedia.org/wiki/First_..._ single_field
>
If at all possible, change your table to look like this:
>
Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9
>

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

>
and then this simply becomes
>
SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)
>
That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)
>
SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'
>
Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.

|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Seems like some % are missing.

--
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|||This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) 0

Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9
>
I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/
>
Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.
>
But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT Loan#
>FROM Table1
>WHERE '-'+Codes+'-' like '-2-'
> OR '-'+Codes+'-' like '-13-'
> OR '-'+Codes+'-' like '-1-'

>
Seems like some % are missing.

Yes, of course you're right, should be

WHERE '-'+Codes+'-' like '%-2-%'
OR '-'+Codes+'-' like '%-13-%'
OR '-'+Codes+'-' like '%-1-%'

but the approach of "use a stored procedure to copy the data to a
better-normalized table" is probably better. (Oh, and that new
table should probably have an index on the Code column.)

Converting a smallint to an nvarchar

For a SQL statement in an Alias column I am am combing several
columns.
But I am having problems with one column as it is a smallint.

I get this error
Syntax error converting the nvarchar value to a column of data type
smallint

My Sql statement "
Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColName
from Table1

I was wondering is there anyway to format/convert the smallint to
nvarchar, without changing the database.On 29 Sep 2004 23:06:12 -0700, ree32 wrote:

>I was wondering is there anyway to format/convert the smallint to
>nvarchar, without changing the database.

Hi ree32,

Use CAST(intcol1 AS varchar(3)).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Use CAST(intcol1 AS varchar(3)).
> Best, Hugo

Thanks

I found another way of getting around this
convert(char,intcol1 )

Where would you place your CAST(intcol1 AS varchar(3)) in the context
of the SQL statement|||On 30 Sep 2004 15:31:50 -0700, ree32 wrote:

>I found another way of getting around this
>convert(char,intcol1 )

Hi ree32,

CONVERT and CAST are pretty much equivalent. Except that CONVERT allows
for more control when you're converting date and time data or fractions
and CAST is ANSI standard (ie more portable to other databases).

I use CAST, except when I need the added control CONVERT gives me.

>Where would you place your CAST(intcol1 AS varchar(3)) in the context
>of the SQL statement

It should replace "intcol1".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting a MySQL multiple column subselect to SQL Server

I am adding SQL Server support to an application that currently uses
MySQL 4.1.
I have a table that looks like this in SQL Server 8.0:
CREATE TABLE dbo.sales_estimates
(
ID int NOT NULL,
YearMonth datetime NULL,
CountryCode char(3) NULL,
StoreCode int NULL,
SalesEstimate decimal(18, 0) NULL,
UserID int NULL,
DateTimeStamp datetime NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.sales_estimates ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
GO
It contains multiple sales estimates for stores, eg different users can
enter their own SalesEstimate for each store's monthly sales.
I want to select the most recent sales estimate for each store for a
given month.
In MySQL 4.1 I can do this by with the folling nested selects:
select YearMonth, CountryCode, StoreCode, SalesEstimate from
store_estimates
where (YearMonth,CountryCode, StoreCode, DateTimeStamp)
in (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode)
I'd like to write a similar statement for SQL Server (version 8.0) if
this is possible, but it appears that I can't have multiple rows in
subselects. Ideally I'd like to find a simpler query that works for
both DBs.SQL Server doesn't support the syntax, I believe it's referred
to as 'row constructors'
You can do this instead
select a.YearMonth, a.CountryCode, a.StoreCode, a.SalesEstimate
from store_estimates a
inner join (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode) b(YearMonth,CountryCode,
StoreCode, DateTimeStamp)
on a.YearMonth=b.YearMonth and a.CountryCode=b.CountryCode
and a.StoreCode=b.StoreCode and a.DateTimeStamp=b.DateTimeStamp
You can also achieve the same results with an EXISTS clause.

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury