Convert Access Query to Transact SQL 2000 IIF to CASE

I need to convert a MS Access 2000 query into SQL Server 2000. Since
the IIF statements are not allowed in Transact-SQL, I 'm trying to
use the CASE Statement
I have two tables, Results and Sections. I need to import the section
records into the Results table. I tried to do a CASE statement, but so
far I cant' get the second IIF right.
In a nutshell, this is what this update query does:
Update the TotalScore in the tblResults table. If the tblResults.type
field is A, B, or C, then make it null. Otherwise, check if the
tblSections.MainScore1 field is null. If it is, then use the
tblResults.TotalScore value. If is not null, then use the
tblSections.MainScore1 value.
Here is the actual Access query syntax :
UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
[tblSections].TestID
SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
[Type]="C",Null,
IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
I have several other fields to update in this query, but they're very
similar to this one.
Any help would be appreciated it.
Thanks.let's see..
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145560609.392893.303600@.e56g2000cwe.googlegroups.com...
>I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.
> Any help would be appreciated it.
> Thanks.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can't use Access SQL syntax in SQL'r. Use Standard SQL syntax.:
UPDATE tblResults
SET TotalScore =
CASE WHEN [Type] IN ('A','B','C') THEN NULL
ELSE CASE WHEN MainScore1 IS NULL THEN TotalScore
ELSE (SELECT MainScore1 FROM tblSections
WHERE TestID = tblResults.TestID)
END
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBREfx1oechKqOuFEgEQLAkgCfZlAFT1+TbFfW
cP+zDhv5c5hCnqgAnAji
grqbw9lVWW3Mm+3fC54YuPJj
=Rv7/
--END PGP SIGNATURE--
ILCSP@.NETZERO.NET wrote:
> I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.|||Hello Guys, thanks for replying. I've been trying both suggestions and
both give me the same results. I'm able to get some of the total
scores, but not all of them.
I can get some of the total scores for not all of them. My guess is
that the error happens when the query is evaluating for null or perhaps
the declared join for the 2 tables is wrong.
the Table tblResults has 1 instance of TestID. The Table tblSections
has several instances of a TestID and they can have something in the
MainScore1 or be Null.
Here's an example of the tblSections table. In this example, I'm able
to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
When running the old Access 2000 update query, I do get the 3
TotalScores.
Type TestID MainScore1
M 214209195 58
L 214371148 43
U 214217823 74
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823|||Hi,
Could you please post the DDL for the tables in question, INSERTs for the
sample data, and the expected results?
Thanks
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145640771.510008.142980@.v46g2000cwv.googlegroups.com...
> Hello Guys, thanks for replying. I've been trying both suggestions and
> both give me the same results. I'm able to get some of the total
> scores, but not all of them.
> I can get some of the total scores for not all of them. My guess is
> that the error happens when the query is evaluating for null or perhaps
> the declared join for the 2 tables is wrong.
> the Table tblResults has 1 instance of TestID. The Table tblSections
> has several instances of a TestID and they can have something in the
> MainScore1 or be Null.
> Here's an example of the tblSections table. In this example, I'm able
> to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
> When running the old Access 2000 update query, I do get the 3
> TotalScores.
> Type TestID MainScore1
> M 214209195 58
> L 214371148 43
> U 214217823 74
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
>|||Here's the DDLs
CREATE TABLE [dbo].[tblResults] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestDate] [datetime] NULL ,
[TotalScore] [float] NULL ,
[MainScore1] [float] NULL ,
[MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore2] [float] NULL ,
[MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore3] [float] NULL ,
[MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSections] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[MainScore1] [float] NULL ,
[MainScore2] [float] NULL ,
[MainScore3] [float] NULL ,
[MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
GO
Since I already posted a short example of the tblSections table, here's
the one for the tblResults table
TestID SSN ExamType TotalScore
214209195 123456789 M
214371148 987654321 L
214398090 234567890 B
214398102 098765432 A
214217823 590380445 U
214430778 567890123 C
Based on the update query, I should get the totalscore for the records
with the TestID: 214209195, 214371148, and the 214217823. However, I
only get the results for the 214371148. The other 2 total scores are
still null.
Hope this helps.|||Hi,
Thanks for the DDL. And if you've posted the actual INSERT script, would it
look something like this?
insert tblResults (TestID, SSN, Type)
select
214209195, '123456789', 'M'
union all
select
214371148, '987654321', 'L'
union all
select
214398090, '234567890', 'B'
union all
select
214398102, '098765432', 'A'
union all
select
214217823, '590380445', 'U'
union all
select
214430778, '567890123', 'C'
insert tblSections (TestID, MainScore1)
select
214209195, 58
union all
select
214371148, 43
union all
select
214217823, 74
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
If so, the update I posted yesterday should give the expected result. Once
again:
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
select TestId, TotalScore from tblResults
TestId TotalScore
-- ----
214209195 58.0
214371148 43.0
214398090 NULL
214398102 NULL
214217823 74.0
214430778 NULL
(6 row(s) affected)
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145644726.162771.207080@.g10g2000cwb.googlegroups.com...
> Here's the DDLs
> CREATE TABLE [dbo].[tblResults] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TestDate] [datetime] NULL ,
> [TotalScore] [float] NULL ,
> [MainScore1] [float] NULL ,
> [MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore2] [float] NULL ,
> [MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore3] [float] NULL ,
> [MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[tblSections] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [MainScore1] [float] NULL ,
> [MainScore2] [float] NULL ,
> [MainScore3] [float] NULL ,
> [MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
> GO
>
> Since I already posted a short example of the tblSections table, here's
> the one for the tblResults table
> TestID SSN ExamType TotalScore
> 214209195 123456789 M
> 214371148 987654321 L
> 214398090 234567890 B
> 214398102 098765432 A
> 214217823 590380445 U
> 214430778 567890123 C
>
> Based on the update query, I should get the totalscore for the records
> with the TestID: 214209195, 214371148, and the 214217823. However, I
> only get the results for the 214371148. The other 2 total scores are
> still null.
> Hope this helps.
>

Convert 1st letter to uppercase in SQL comand

Hi all!
This is the problem:
User enters student name, in the database, only the 1st letter is uppercase and the rest is in Lower case.
So, I want to fix this so as it is not case sensitive, i.e. the user can enter a name and it will return the record
whether they enter it in upper or lower case.
My Code:
CvtUpperCase.Text = UCase(Content.Text) //stores user's input

Select Case OptionChoice

Case "Student_FirstName"
MyCommand = New SqlDataAdapter("select * from [qryStudentDetails] where [qryStudentDetails].[Student_FirstName] like
'" & CvtUpperCase.Text & "'" , myConnection)

...
Any ideas??

User the T-SQL UPPER() function:
select * from [qryStudentDetails] where UPPER([qryStudentDetails].[Student_FirstName]) like '" & CvtUpperCase.Text & "'"
[edit]
By the way... I don't think text comparissons in SQL are case sensitive at all? I could be wrong though...
[/edit]|||Excellent!! Thank you so much! Was jammed on that for ages!

conversion in select statement

Hey y'all,

Can someone make this right? i have an int column and need text:

SELECT (SELECT CASE score WHEN 0 THEN 'qqqqqqqq' ELSE 1 END) AS Expr1, COUNT(Score) AS Expr1

Thanks in advance

SELECT CASE score when 0 then 'qqqqqqqq' ELSE '1' END AS Expr1, COUNT(Score) AS Expr2

Like that?

|||

score is always 0,1 or 2 but i need written labels (text) for my charting...

i can do it with a selection in the build of my chart but i was wondering of i could do it in sql...

|||

SELECT CAST(score as varchar(1)) As score,count(*)

?

conventions about namereferences

Hallo,
Can someone tell me the difference between pcd_mn..nslAfdeling and
pcd_mn.dbo.nslAfdeling?
I know that in this case pcd_mn references the database on the server
and nslAfdeling a table in that database. I also know that dbo
references the owner of the database, but who is '..'?
Thnx in advance for replying
Groeten RonWell, if you dont specify the owner ('..' syntax) then SQL server first
assumes that you are the owner of the object (googleRon.nslAfdeling). If
that object doesnt exists it goes with the default 'dbo' owner and finds the
table under 'dbo.nslAfdeling'. So, the behaviour should be the same, but I
would recommend using table owner.
MC
"googleRon" <ronvdheijden@.gmail.com> wrote in message
news:1148374201.120687.276900@.j55g2000cwa.googlegroups.com...
> Hallo,
> Can someone tell me the difference between pcd_mn..nslAfdeling and
> pcd_mn.dbo.nslAfdeling?
> I know that in this case pcd_mn references the database on the server
> and nslAfdeling a table in that database. I also know that dbo
> references the owner of the database, but who is '..'?
> Thnx in advance for replying
> Groeten Ron
>|||Marko,
Thnx.
Ron

Control storage of Data

Hi
From one of my co-workers I have got the question:
Is it possible to control the way that SQL Server stores
numeric data?
The case is when he e.g. want to store 0.5 (or in Denmark
0,5) - that is 1/2 (one half) - it is stored (as far as we
can se, in the tables as 0.5 (or as it's shown with the
Danish settings 0,5).
The team he belongs to also works with Oracle and they say
that it seems that Oracle stores the same data as .5
(og ,5) omitting the preceeding 0 (zero).
I can't find any "handles to turn in SQL Server", is there
any?
(Or maybe it is possible to change the Oracle way of
storing data?)
Yours truly
Jakob Persson
Numeric data isn't *stored* in that format at all. It's stored internally as
a binary number.
What you really want is to control the way it is displayed and entered on
the screen. How to control that formatting depends on your client
application or development platform not on SQL Server. Regional Settings are
usually determined by your Windows Control Panel settings.
David Portas
SQL Server MVP
|||J. Persson,
There are many different data types that you can use to represent a
numerical value.
bigint, int, smallint, tinyint
decimal, numeric (synonymous)
float, real (synonymous)
character types - char, varchar, etc
binary, varbinary
I would recommend you use the decimal data type to store a decimal
number. As David mentioned, there are two different elements to
retrieving numbers. The first one is the storage data type, and the
second is how this is represented in an application.
After you have retrieved your number using the most appropriate data
type, you can format it on the server-side using CAST or CONVERT, and
also in your client-side app.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
J. Persson wrote:
> Hi
> From one of my co-workers I have got the question:
> Is it possible to control the way that SQL Server stores
> numeric data?
> The case is when he e.g. want to store 0.5 (or in Denmark
> 0,5) - that is 1/2 (one half) - it is stored (as far as we
> can se, in the tables as 0.5 (or as it's shown with the
> Danish settings 0,5).
> The team he belongs to also works with Oracle and they say
> that it seems that Oracle stores the same data as .5
> (og ,5) omitting the preceeding 0 (zero).
> I can't find any "handles to turn in SQL Server", is there
> any?
> (Or maybe it is possible to change the Oracle way of
> storing data?)
> Yours truly
> Jakob Persson
>
|||Hi
Thank you for your reply.
I am aware of the facts your writing about, so I might
have to be a bit more specific.
My co-worker is writing an application that reads "raw
binary data" information directly from the SQL Server
transaction log file using MFC C++ (not "through the
tables" presented to us "normal database people" when we
use T-SQL, ADO.NET etc.).
Since my company plans to extend our application so it can
handle Oracle databases also, they have discovered (that's
what they have told me anyway) that there seems to be a
difference in how SQL Server and Oracle stores the data
binary (?). They think it may have a conection to how a
decimal value is stored when the value is btw. 1 and -1
(eg. does the binary pattern represent 0.5 or .5).
I hope this makes more sence.
yours truly
jakob Persson

>--Original Message--
>Numeric data isn't *stored* in that format at all. It's
stored internally as
>a binary number.
>What you really want is to control the way it is
displayed and entered on
>the screen. How to control that formatting depends on
your client
>application or development platform not on SQL Server.
Regional Settings are
>usually determined by your Windows Control Panel settings.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> there seems to be a
> difference in how SQL Server and Oracle stores the data
> binary (?).
That's not so surprising. Oracle supports non-Wintel platforms which have
different standards for binary numerics. Probably Oracle's internal
implementation changes depending on the platform but maybe there are some
things they have standardised across all OSs - I really don't know.
To answer your question, in SQL Server there is no method of controlling how
values are stored internally beyond using the standard datatypes.
Lumigent (www.lumigent.com) markets a Log Explorer product so it may help
you to check it out if you haven't already. Maybe it will help you analyze
the log data.
Hope this helps.
David Portas
SQL Server MVP

Control storage of Data

Hi
From one of my co-workers I have got the question:
Is it possible to control the way that SQL Server stores
numeric data?
The case is when he e.g. want to store 0.5 (or in Denmark
0,5) - that is 1/2 (one half) - it is stored (as far as we
can se, in the tables as 0.5 (or as it's shown with the
Danish settings 0,5).
The team he belongs to also works with Oracle and they say
that it seems that Oracle stores the same data as .5
(og ,5) omitting the preceeding 0 (zero).
I can't find any "handles to turn in SQL Server", is there
any?
(Or maybe it is possible to change the Oracle way of
storing data?)
Yours truly
Jakob PerssonNumeric data isn't *stored* in that format at all. It's stored internally as
a binary number.
What you really want is to control the way it is displayed and entered on
the screen. How to control that formatting depends on your client
application or development platform not on SQL Server. Regional Settings are
usually determined by your Windows Control Panel settings.
--
David Portas
SQL Server MVP
--|||J. Persson,
There are many different data types that you can use to represent a
numerical value.
bigint, int, smallint, tinyint
decimal, numeric (synonymous)
float, real (synonymous)
character types - char, varchar, etc
binary, varbinary
I would recommend you use the decimal data type to store a decimal
number. As David mentioned, there are two different elements to
retrieving numbers. The first one is the storage data type, and the
second is how this is represented in an application.
After you have retrieved your number using the most appropriate data
type, you can format it on the server-side using CAST or CONVERT, and
also in your client-side app.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
J. Persson wrote:
> Hi
> From one of my co-workers I have got the question:
> Is it possible to control the way that SQL Server stores
> numeric data?
> The case is when he e.g. want to store 0.5 (or in Denmark
> 0,5) - that is 1/2 (one half) - it is stored (as far as we
> can se, in the tables as 0.5 (or as it's shown with the
> Danish settings 0,5).
> The team he belongs to also works with Oracle and they say
> that it seems that Oracle stores the same data as .5
> (og ,5) omitting the preceeding 0 (zero).
> I can't find any "handles to turn in SQL Server", is there
> any?
> (Or maybe it is possible to change the Oracle way of
> storing data?)
> Yours truly
> Jakob Persson
>|||Hi
Thank you for your reply.
I am aware of the facts your writing about, so I might
have to be a bit more specific.
My co-worker is writing an application that reads "raw
binary data" information directly from the SQL Server
transaction log file using MFC C++ (not "through the
tables" presented to us "normal database people" when we
use T-SQL, ADO.NET etc.).
Since my company plans to extend our application so it can
handle Oracle databases also, they have discovered (that's
what they have told me anyway) that there seems to be a
difference in how SQL Server and Oracle stores the data
binary (?). They think it may have a conection to how a
decimal value is stored when the value is btw. 1 and -1
(eg. does the binary pattern represent 0.5 or .5).
I hope this makes more sence.
yours truly
jakob Persson
>--Original Message--
>Numeric data isn't *stored* in that format at all. It's
stored internally as
>a binary number.
>What you really want is to control the way it is
displayed and entered on
>the screen. How to control that formatting depends on
your client
>application or development platform not on SQL Server.
Regional Settings are
>usually determined by your Windows Control Panel settings.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> there seems to be a
> difference in how SQL Server and Oracle stores the data
> binary (?).
That's not so surprising. Oracle supports non-Wintel platforms which have
different standards for binary numerics. Probably Oracle's internal
implementation changes depending on the platform but maybe there are some
things they have standardised across all OSs - I really don't know.
To answer your question, in SQL Server there is no method of controlling how
values are stored internally beyond using the standard datatypes.
Lumigent (www.lumigent.com) markets a Log Explorer product so it may help
you to check it out if you haven't already. Maybe it will help you analyze
the log data.
Hope this helps.
--
David Portas
SQL Server MVP
--

Control storage of Data

Hi
From one of my co-workers I have got the question:
Is it possible to control the way that SQL Server stores
numeric data?
The case is when he e.g. want to store 0.5 (or in Denmark
0,5) - that is 1/2 (one half) - it is stored (as far as we
can se, in the tables as 0.5 (or as it's shown with the
Danish settings 0,5).
The team he belongs to also works with Oracle and they say
that it seems that Oracle stores the same data as .5
(og ,5) omitting the preceeding 0 (zero).
I can't find any "handles to turn in SQL Server", is there
any?
(Or maybe it is possible to change the Oracle way of
storing data?)
Yours truly
Jakob PerssonNumeric data isn't *stored* in that format at all. It's stored internally as
a binary number.
What you really want is to control the way it is displayed and entered on
the screen. How to control that formatting depends on your client
application or development platform not on SQL Server. Regional Settings are
usually determined by your Windows Control Panel settings.
David Portas
SQL Server MVP
--|||J. Persson,
There are many different data types that you can use to represent a
numerical value.
bigint, int, smallint, tinyint
decimal, numeric (synonymous)
float, real (synonymous)
character types - char, varchar, etc
binary, varbinary
I would recommend you use the decimal data type to store a decimal
number. As David mentioned, there are two different elements to
retrieving numbers. The first one is the storage data type, and the
second is how this is represented in an application.
After you have retrieved your number using the most appropriate data
type, you can format it on the server-side using CAST or CONVERT, and
also in your client-side app.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
J. Persson wrote:
> Hi
> From one of my co-workers I have got the question:
> Is it possible to control the way that SQL Server stores
> numeric data?
> The case is when he e.g. want to store 0.5 (or in Denmark
> 0,5) - that is 1/2 (one half) - it is stored (as far as we
> can se, in the tables as 0.5 (or as it's shown with the
> Danish settings 0,5).
> The team he belongs to also works with Oracle and they say
> that it seems that Oracle stores the same data as .5
> (og ,5) omitting the preceeding 0 (zero).
> I can't find any "handles to turn in SQL Server", is there
> any?
> (Or maybe it is possible to change the Oracle way of
> storing data?)
> Yours truly
> Jakob Persson
>|||Hi
Thank you for your reply.
I am aware of the facts your writing about, so I might
have to be a bit more specific.
My co-worker is writing an application that reads "raw
binary data" information directly from the SQL Server
transaction log file using MFC C++ (not "through the
tables" presented to us "normal database people" when we
use T-SQL, ADO.NET etc.).
Since my company plans to extend our application so it can
handle Oracle databases also, they have discovered (that's
what they have told me anyway) that there seems to be a
difference in how SQL Server and Oracle stores the data
binary (?). They think it may have a conection to how a
decimal value is stored when the value is btw. 1 and -1
(eg. does the binary pattern represent 0.5 or .5).
I hope this makes more sence.
yours truly
jakob Persson

>--Original Message--
>Numeric data isn't *stored* in that format at all. It's
stored internally as
>a binary number.
>What you really want is to control the way it is
displayed and entered on
>the screen. How to control that formatting depends on
your client
>application or development platform not on SQL Server.
Regional Settings are
>usually determined by your Windows Control Panel settings.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> there seems to be a
> difference in how SQL Server and Oracle stores the data
> binary (?).
That's not so surprising. Oracle supports non-Wintel platforms which have
different standards for binary numerics. Probably Oracle's internal
implementation changes depending on the platform but maybe there are some
things they have standardised across all OSs - I really don't know.
To answer your question, in SQL Server there is no method of controlling how
values are stored internally beyond using the standard datatypes.
Lumigent (www.lumigent.com) markets a Log Explorer product so it may help
you to check it out if you haven't already. Maybe it will help you analyze
the log data.
Hope this helps.
David Portas
SQL Server MVP
--

CONSTANTS in bcp .fmt files

Hi,
How can we insert CONSTANTS in table columns using bcp's .fmt files? In case SQLLoader, we can specify CONSTANTs in .ctl files. How can it be done in case of bcp utility?
Thanks in advance
VijayYou can't! BCP does not support this.|||Originally posted by Paul Young
You can't! BCP does not support this.

Any workaround used normally?

Thanks|||Either add the data to your file before BCPing or run an update script to add the data after BCPing the file.|||Originally posted by vijay_kumar74
Any workaround used normally?

Thanks

What do you mean by Constant?

UPDATE myTable SET col1 = 'Something'

???????????????|||Yes, I think I'll go by updating table columns with the "CONSTANT" (e.g. $PROGRAM_NAME) values after running bcp.

Thanks for the help!|||You can check %ERRORLEVEL% to see if the BCP went okay and then follow up with an OSQL command to update the table. Not eloquent but functional.