case statement using cast

Hi guys,

The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'

I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".

In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.

Please Help.

"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,

Aldo.

Hi,

What is the errormessage?

Is it possible that there are non-nummeric values at these positions?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

the data in the field is a string containing nummeric characters...

I solved the problem using the code below:

SLC = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)= 0 then 'Zero'" _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)>= 1 " _
& "And CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)<= 699 then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values', "

The ERROR in the code I uploaded earlier was using the word "Case" in both cases:

... Case When ...

...Case When...

instead of:

... Case

When

When

This code is working too:

sqlString = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)>= '001' And SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)<= '699' then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values' "

I'll be glad to learn some other good idea.

Thanks,

Aldo.|||Just an FYI, this is a transact-sql question, not an SSIS question. To tie this into SSIS, you could avoid doing the case statement in your SQL and do it in a derived column transformation.

Case Statement Really Urgent Please

Why will this case statement not work?
It checks the length of a char field TRAN_TIME and pads it accordingly.
SELECT a.TRAN_TIME
FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE = b.Policy_desc
AND a.TRAN_DT >= '20051221'
AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00' +
a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GEI
/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')or what is that padding function
lpad or something'
"marcmc" wrote:

> Why will this case statement not work?
> It checks the length of a char field TRAN_TIME and pads it accordingly.
> SELECT a.TRAN_TIME
> FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00'
+
> a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
EI/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')|||SELECT a.TRAN_TIME
FROM
QUINN_ST..get_bcp_h_cvcmis a(nolock),
QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE =3D b.Policy_desc
AND a.TRAN_DT >=3D '20051221'
AND (
CASE len(a.TRAN_TIME)
When 5 THEN '0' + a.TRAN_TIME
When 4 Then '00' + a.TRAN_TIME
ELSE a.TRAN_TIME END
) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GE=
=ADI/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')
Syntactically it looks OK, but there could be some conversion issue
depending on your datatypes of the table (which you didn=B4t sned in a
ddl script with the issue)
BTW: What do you mean by lpad ?
HTH, jens Suessmeyer.|||lpad was an oracle padding func i was thinking of, how can i pad one or two
zeroes to the left of lets say the char '93826 '
'
"Jens" wrote:

> SELECT a.TRAN_TIME
> FROM
> QUINN_ST..get_bcp_h_cvcmis a(nolock),
> QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (
> CASE len(a.TRAN_TIME)
> When 5 THEN '0' + a.TRAN_TIME
> When 4 Then '00' + a.TRAN_TIME
> ELSE a.TRAN_TIME END
> ) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
E_I/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')
> Syntactically it looks OK, but there could be some conversion issue
> depending on your datatypes of the table (which you didn′t sned in a
> ddl script with the issue)
> BTW: What do you mean by lpad ?
> HTH, jens Suessmeyer.
>|||e.g., pad resulting length of 6
right(replicate('0',6) + convert(varchar, '93826'), 6)
marcmc wrote:
> lpad was an oracle padding func i was thinking of, how can i pad one or tw
o
> zeroes to the left of lets say the char '93826 '
> '
> "Jens" wrote:
>|||Thats easy:
DECLARE @.SOMEVALUE VARCHAR(20)
SET @.SOMEVALUE = '998'
SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
PRINT @.SOMEVALUE
HTH, Jens Suessmeyer.|||ah - missed the char, have to trim it first
right(replicate('0',6) + convert(varchar, rtrim('93826 ')), 6)
Trey Walpole wrote:
> e.g., pad resulting length of 6
> right(replicate('0',6) + convert(varchar, '93826'), 6)
> marcmc wrote:
>|||combining Trey's and Jen's suggestions...
If your input is character data, you should probably trim spaces from both
sides unless you know and can rely on the incoming data.
RIGHT('000000' + LTRIM(RTRIM(@.SOMEVALUE)), 6)
if you need it to be dynamic, use the replicate version
RIGHT(REPLICATE('0', @.PadSize) + LTRIM(RTRIM(@.SomeValue)), @.PadSize)
if it's numeric data,
RIGHT(REPLICATE('0', @.PadSize) + CONVERT(VARCHAR, @.SomeNumeric), @.PadSize)
if you prefer CAST instead of CONVERT, that works too.
Hope that helps,
Joe
"Jens" wrote:

> Thats easy:
> DECLARE @.SOMEVALUE VARCHAR(20)
> SET @.SOMEVALUE = '998'
> SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
> PRINT @.SOMEVALUE
>
> HTH, Jens Suessmeyer.
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
Thanks in advance,
sck10
I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

case sensitive sql

Hi
how can i use the sensive case in a select field from table where fild='GhhY' ?



I think you'd have to do character by character comparison using SUBSTRINGs and UPPER/LOWERs unless someone else here has a better solution.|||select * from TABLE where cast(fild as varbinary) = cast('GhhY' as varbinary)

Nicksql

Case sensitive

Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT
statement, i have to be case sensitive on the table name and field name. Is
there anywhere to ignore this so that i don't have to care about the case s
ensitivity? Is there any se
tting on the server that i can turn this off? Or is it a design by feature?On Tue, 16 Mar 2004 19:11:05 -0800, Loon wrote:

>Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT statem
ent, i have to be case sensitive on the table name and field name. Is there anywhere
to ignore this so that i don't have to care about the case sensitivity? Is there an
y s
etting on the server that i can turn this off? Or is it a design by feature?
A binary sort order makes sorting and comparisons faster than any
other collation sequence can be, since SQL Server only has to compare
the byte values of the characters.
The drawback is that characters with different byte values (like 'A'
and 'a') compare unequal.
The only way to change collation sequence after installation is to
rebuild the master database and then recreate all your databases with
the new collation sequence. Refer to BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Case Sensitive

I have a table that has a field populated by single character data.
in it 'm' is different than 'M'. How do I differentiate this in my
queries...
It works automatically on my base SQL server, but not on the replicant.
Thanks for the help...Atley,
Please search the SQL Server 2000 Books Online re: "COLLATE" and
"COLLATIONS" to understand why you are getting different results on the
different SQL Server instances.
To help you out immediately, however, the following is an example of how to
use the COLLATE syntax within a SELECT:
create table Atley (c1 char (1) NOT NULL)
go
insert into Atley values ('m')
insert into Atley values ('M')
select *
from Atley
where c1 = 'm' collate Latin1_General_CS_AS
Chief Tenaya
"Atley" <atley_1@.homtmail.com> wrote in message
news:uwl89FFEEHA.3696@.TK2MSFTNGP10.phx.gbl...
> I have a table that has a field populated by single character data.
> in it 'm' is different than 'M'. How do I differentiate this in my
> queries...
> It works automatically on my base SQL server, but not on the replicant.
>
> Thanks for the help...
>
>|||As Tenaya says, this is related to collations. It seems that your collation
on the publisher is case-sensitive and this has replicated to the subscriber
as a case-insensitive collation. Tenaya's query will allow a case-sensitive
comparison to be done on case-insensitive data. However, if you want the
column collation itself to be transferred to the subscriber, so queries
don't need to be modified, then have a look at the article properties,
snapshot tab - there is a checkbox to select the collation there. You'll
need to reinitialize for this to take effect. BTW, the database collation on
the subscriber is the one you're inheriting, so modifying this (ALTER
DATABASE...) and reinitializing would give the same result.
HTH,
Paul Ibison

CASE problem

Hello all.
I'm trying to use a CASE statement to return the Month name from a month
number (1=Jan, 2=Feb, ect.) I have a field in my db that stores the date as
epoch time.
Here's the entire SQL, I get an "syntax error near =" when I run this:
SELECT
CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6, 'Jan 1, 1970'),
100) AS Create_Date_Converted,
DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6,
'Jan 1, 1970'), 100)) AS Create_Date_Converted_Month,
DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6,
'Jan 1, 1970'), 100)) =
CASE
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
END,
Create_Date,
Priority
FROM ARAdmin.Help_Desk
I'm a total newbie. Trying the CASE part of this query in different ways
hasn't resulted in any solutions. Any/all help is greatly apprecitated!lol, this is easy,, use the DateName() Function
Forget the case
Select DateName(month, getdate())
If you just want the abbreviation, take the Leftmost 3 Characters
Select Left(DateName(month, getdate()), 3)
"Drew" wrote:

> Hello all.
> I'm trying to use a CASE statement to return the Month name from a month
> number (1=Jan, 2=Feb, ect.) I have a field in my db that stores the date a
s
> epoch time.
> Here's the entire SQL, I get an "syntax error near =" when I run this:
> SELECT
> CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6, 'Jan 1, 1970')
,
> 100) AS Create_Date_Converted,
> DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * -
6,
> 'Jan 1, 1970'), 100)) AS Create_Date_Converted_Month,
> DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * -
6,
> 'Jan 1, 1970'), 100)) =
> CASE
> WHEN '1' THEN 'Jan'
> WHEN '2' THEN 'Feb'
> WHEN '3' THEN 'Mar'
> END,
> Create_Date,
> Priority
> FROM ARAdmin.Help_Desk
> I'm a total newbie. Trying the CASE part of this query in different ways
> hasn't resulted in any solutions. Any/all help is greatly apprecitated!|||Thanks CBretana.
Your suggestion is to use DateName instead of DATEPART?
"CBretana" wrote:
> lol, this is easy,, use the DateName() Function
> Forget the case
> Select DateName(month, getdate())
> If you just want the abbreviation, take the Leftmost 3 Characters
> Select Left(DateName(month, getdate()), 3)
>
> "Drew" wrote:
>|||On a quick glance, you have a invalid SQL expression in your SELECT
statement. IOW, the statement you post forms a construct similar to:
SELECT <some_expression>,
<some_expression>,
<some_expression> = CASE expression, -- wrong
<other_column>
FROM <your_table>
The third column in the SELECT statement is invalid since it does not return
a valid SQL datatype. CASE expressions evaluates a list of conditions and
returns one of multiple possible result expressions. It can be used in any
SQL clause where a regular expression can be used or in the LHS or RHS of a
valid comparison operator. See the topc CASE in SQL Server Books Online for
more details.
Anith|||Yes, this function does exactly what you said you wanted,, it returns the
monthname (or any date part name) from a date,
"Drew" wrote:
> Thanks CBretana.
> Your suggestion is to use DateName instead of DATEPART?
> "CBretana" wrote:
>|||Thanks CBretana, works like a charm.
"CBretana" wrote:
> lol, this is easy,, use the DateName() Function
> Forget the case
> Select DateName(month, getdate())
> If you just want the abbreviation, take the Leftmost 3 Characters
> Select Left(DateName(month, getdate()), 3)
>
> "Drew" wrote:
>

Case in SqlServer

Hi,
I have a table and field below.

Payroll table
Id Period Net_pay
001 010105 5000
001 011605 300
001 030105 1000
001 040105 150
002 010105 1000
002 011505 500

I want the display like this if posible.. The first to character of the field period is the Month January to December.
Id January February March April ...........
001 5300 00 1000 150 ..........
002 1500 00 00 00 .............

Thank...You want a CROSSTAB query. Look it up in Books Online and you will get as good an explanation of how to do this as you could get on this forum.

Your only challenge should be converting the numeric string date format to the MonthName columns you want. You will need to do a CONVERT, CAST, or some other function, depending upon whether you have to worry about spanning years or not.|||Thanks Mr. Blindman for the reply...

Case Expression in Where Claus

I there a way to differ the filtering field through a Case expression in the Where claus?

ex:

Where
Case
When @.StuNum = ''
Then S.SSN = @.SSN
Else
S.StuNum = @.StuNum
End

And ...

The actual field to filter by differs, but I can't seem to find the right syntax to do this.

Any help is appreciated : )

The answer to your question is yes... But it is unwieldy. Try this instead:

WHERE (@.StuNum='' ANDS.SSN=@.SSN) OR (@.StuNum<>'' AND S.StuNum=@.StuNum)

|||

Hi,

I am not sure what you want... so I am sending my answer on some guess :) if it is not the thing you are looking for then please explain your problem a bit more. Anyways please find the query below.

DECLARE @.EmpIDINT

SET @.EmpID=2

SELECT*

FROM HumanResources.Employee

WHERE

(SELECTCASE

WHEN @.EmpID=1THEN

ContactID

ELSE

NationalIDNumberEND)=(SELECTCASE

WHEN @.EmpID=1THEN

1209

ELSE

253022876END)

Hope it Helps!

Bhaskar!

|||

I think you hit the nail on the head. I have my syntax listed as below now. Seems the "=" sign was being interpreted as part of the Case statement and needed to be separated out. Thanks to both of yo for your input.

Where

(Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then S.SSN

Else

S.StuNum

End) = (Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then @.SSN

Else

@.StuNum

End)

CASE count wierd join needed maybe? just one more field needed!

Right, I used your handy answers to get a solution that worked.. Your
last method was the one to use but i left it running for 3 hours and
it still hadn't returned any results! (and it's a stupidly high spec
server with pretty much just sql on there)

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

WITH Bin1 AS (SELECT ItemID, MAX(DateTimeCreated) AS MDate
FROM dbo.BinItem AS Bin1
GROUP BY ItemID)
SELECT Bin2.ItemID, Bin2.BinName
FROM dbo.BinItem AS Bin2 INNER JOIN
Bin1 AS Bin1 ON Bin2.ItemID = Bin1.ItemID
AND Bin2.DateTimeCreated = Bin1.MDate

I could then go back to my orignal SQL and use the following to grap
the data from the newly created table kev_bin above:

Select ... .. ...
kbin.BinName as location

Quote:

Originally Posted by

>From ...

...
dbo.kev_bin kbin
...
WHERE ..
item.ItemID = kbin.ItemID
....

and this runs in about 20 seconds :D

thanks again for all your help, i couldn't have done it without!(kevin@.nu-urbanmusic.co.uk) writes:

Quote:

Originally Posted by

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

Views are just a logical concept. Except for indexed view, they are not
materialised, and they are expanded so that the optimizer works with the
expanded query. That is, essentiall a macro.

--
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|||On Nov 6, 10:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(ke...@.nu-urbanmusic.co.uk) writes:

Quote:

Originally Posted by

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

>
Views are just a logical concept. Except for indexed view, they are not
materialised, and they are expanded so that the optimizer works with the
expanded query. That is, essentiall a macro.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Kev,

Glad I could help :)

Jsql

case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

sql

Case - Sensitive field

I want to specify the data in columns are both upper & lower case(i.e. one column data having all lowercase data or uppercase data ).

Ex:- if i have one column - col1 and its

1st value is RAKESH JHA

2nd value is rakesh jha

then how i can find that, how much row are in upper case. respectively lower case

Use UPPER and LOWER functions. Like:
select * from MyTable where UPPER(fCOL1)=fCOL1

|||

Have a look at

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Carriage Returns in Data

I have a ntext field of data. I was trying to use the REPLACE function to
change the carriage returns to spaces, but have not any luck.
Can anyone make any suggestions?
Thank you,
JLFlemingYou don't need the text within <> -- it is just an example to show that
things are working as expected.
Here is an example:
create table #foo (col1 varchar(20))
insert into #foo values ('test')
insert into #foo values ('test
more')
select col1 from #foo
select REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>')
from #foo
Keith
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||You will have write a procedure that loops 8000 character chunks of the data
doing the replace.
Thomas
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||I missed the ntext bit the first time I read your post.
Keith
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMF47mHYFHA.4036@.tk2msftngp13.phx.gbl...
> You don't need the text within <> -- it is just an example to show that
> things are working as expected.
> Here is an example:
> create table #foo (col1 varchar(20))
> insert into #foo values ('test')
> insert into #foo values ('test
> more')
> select col1 from #foo
> select
> REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>') from
> #foo
>
> --
> Keith
>
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>|||The best way to do this is outside of SQL Server. Text data is a beast in
SQL Server 2000 and earlier to deal with in SQL. The chunking idea given by
Thomas is feasible, but you have to be careful about your search value
crossing the chunk boundry.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming

Carriage Returns and Line Breaks

I have an address field that is coming back as a single field with carriage returns and line breaks and I would like to have it properly wrap in a single box, but the wrapping is all off. How can I get this to properly break? ThanksMake sure you have text box property "cangrow" to true.|||

Nope. That is not the issue. It is cangrow = true.

What I want is to take a field that comes out as:

John Smith CR LB 123 Main Street CR LB Anytown, MA 01888 CR LB USA

as

John Smith

123 Main Street

Anytown, MA 01888

USA

What I am getting is:

John Smith 123

Main Street Anytown,

MA 01888 USA

So I am looking how to read the CR and LBs and maybe replace them with BR tags, not sure.

|||Also... what is being returned to indicate the line break are char(13)s. I am recreating a report that I had lost and was able to resolve this at one time, but forget how I got around it.|||

try select field1 + char(13) + char(10) + field2

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

carriage return inside a field of text data type?

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thankstry char(10) instead
or the combination of the two characters|||ive actually tried them both :(

edit: just tried using char(13) + char(10) and it works! thanks!|||you welcome ;)

Carriage Return In View

Is it possible to return one field in a view that has something similar to
vbCrLf in Visual Basic? I want to return it as a formatted envelope
address. Thanks.
Daviduse char(13) as your column value
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"David C" wrote:

> Is it possible to return one field in a view that has something similar to
> vbCrLf in Visual Basic? I want to return it as a formatted envelope
> address. Thanks.
> David
>
>|||Thanks. Actually, I had to use ... + Char(13) + Char(10)
David
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2CEEA448-B43B-457C-BE12-D0547A4F3AD4@.microsoft.com...
> use char(13) as your column value
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Database change management for SQL Server
>
> "David C" wrote:
>