problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql

problem with split function to select multiple values

When running the split function from my data pane within ssrs, or when
running it from sql server directly everything works fine.
But..when running the report in preview I receive dbo.split too many
arguments specified. I assume reporting services in some way passes it
like a string value once running the report. Otherwise it's also
noticeable the join function works fine within
code i'm using is like this within my dataset
Select myfield
from table
where field in
(
select item
from dbo.split(@.par,',')
)
The parameter passed to the split function has an 'integer value'
specified for the 'parameter value' . The split value is the well
known generic split function.If @.Par is a multivalued parameter, then try joining the items together in a
string:
Select myfield
from table
where field in
(
select item
from dbo.split(join(@.par,','),',')
)
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Update to my previous post, the parameter to the split function must be a
varchar parameter as this will be sent through as a single comma separated
string list to the function.
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Branden, sorry for taking your time...I've been using a direct query
within SSRS so there was no need for the split (this explains the
error). I tend to use SP's or cubes regularly and confused the way
they handle parameters with a direct query i'm using now. So this is
the simple solution
Select myfield
from table
where field in
(
@.par
)

Problem with sp2... bug?

After I've installed the sql server 2005 sp2, some reports have a strange
behaviour. For example
In some case the Inscope function in a matrix, in the subtotal column,
returns different result (between sp1 and sp2)
And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
(using the inscope function) , in some case it do the sum of the column.
I think that the two stange behaviour are correlateOn Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> After I've installed the sql server 2005 sp2, some reports have a strange
> behaviour. For example
> In some case theInscope function in a matrix, in the subtotal column,
> returns different result (between sp1 and sp2)
> And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> (using theinscopefunction) , in some case it do the sum of the column.
> I think that the two stange behaviour are correlate
We just ran across this today too. The Inscope function evaluates
differently depending on where its being evaluated.|||Allen M ha scritto:
> On Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> > After I've installed the sql server 2005 sp2, some reports have a strange
> > behaviour. For example
> >
> > In some case theInscope function in a matrix, in the subtotal column,
> > returns different result (between sp1 and sp2)
> > And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> > (using theinscopefunction) , in some case it do the sum of the column.
> > I think that the two stange behaviour are correlate
> We just ran across this today too. The Inscope function evaluates
> differently depending on where its being evaluated.
I also have the same problem.
is it a bug of SP2 ?
Alessio|||On May 17, 1:26 am, mxl...@.tiscali.it wrote:
> Allen M ha scritto:
> > On Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> > > After I've installed the sql server 2005 sp2, some reports have a strange
> > > behaviour. For example
> > > In some case theInscope function in a matrix, in the subtotal column,
> > > returns different result (between sp1 and sp2)
> > > And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> > > (using theinscopefunction) , in some case it do the sum of the column.
> > > I think that the two stange behaviour are correlate
> > We just ran across this today too. TheInscopefunction evaluates
> > differently depending on where its being evaluated.
> I also have the same problem.
> is it abugof SP2 ?
> Alessio
Have either of you found anything on this?sql

Problem with Sleep function in SSIS

Hi,

I have a script task in SSIS which uses a sleep command. sleep(600000) which waits for 10 minutes. This works fine when i execute the package through GUI. But when i execute the same with Commandline, it does not work. Is there some thing which I have not set. I have also tried the Windows API call for sleep still it behaves the same way.

Any help?

Thanks in advance

Srividya

I don't know any reason why it would not work in command line. Are you getting any error?|||did you try using the system.timers.timer class?

Problem with SET QUOTED_IDENTIFIER ON

Hi,
I am creating User defined function with
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF.
What is wrong.
ThanksHi,
look here, Iposted that some time ago:
http://forums.microsoft.com/MSDN/Sh...228076&SiteID=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||How do you know the settings are OFF? What version of SQL Server? The
following works for me under SQL 2000:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN @.Parameter1
END
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating User defined function with
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS
> OFF.
> What is wrong.
> Thanks
>|||I'm working with sql 2000 and result of
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
GO
is null for myUdf.
Result of
select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
is 1.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
> How do you know the settings are OFF? What version of SQL Server? The
> following works for me under SQL 2000:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN @.Parameter1
> END
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>|||The 'sticky' SET options for table valued functions are apparently not
reported correctly in SQL 2000 SP4. The create-time settings are used for
execution though. No problem in SQL 2005.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
RETURNS @.MyTable TABLE (Col1 int)
AS
BEGIN
RETURN
END
GO
CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN 1
END
GO
SELECT
OBJECTPROPERTY(id, 'IsInLineFunction'),
OBJECTPROPERTY(id, 'IsScalarFunction'),
OBJECTPROPERTY(id, 'IsTableFunction'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
FROM sysobjects
WHERE id IN
(
OBJECT_ID('dbo.myTableFunction'),
OBJECT_ID('dbo.myInLineFunction'),
OBJECT_ID('dbo.myScalarFunction')
)
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> I'm working with sql 2000 and result of
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
> GO
> is null for myUdf.
> Result of
> select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
> is 1.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thank you Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e4CxrBoTGHA.196@.TK2MSFTNGP10.phx.gbl...
> The 'sticky' SET options for table valued functions are apparently not
> reported correctly in SQL 2000 SP4. The create-time settings are used for
> execution though. No problem in SQL 2005.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
> RETURNS TABLE
> AS
> RETURN (SELECT 1 AS test)
> GO
> CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
> RETURNS @.MyTable TABLE (Col1 int)
> AS
> BEGIN
> RETURN
> END
> GO
> CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN 1
> END
> GO
> SELECT
> OBJECTPROPERTY(id, 'IsInLineFunction'),
> OBJECTPROPERTY(id, 'IsScalarFunction'),
> OBJECTPROPERTY(id, 'IsTableFunction'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
> FROM sysobjects
> WHERE id IN
> (
> OBJECT_ID('dbo.myTableFunction'),
> OBJECT_ID('dbo.myInLineFunction'),
> OBJECT_ID('dbo.myScalarFunction')
> )
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
>

Problem with SELECT, GROUP BY and aggregate function

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.

The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.

Thanks in advance!

If you really want to use that dynamic query, I would use a Subquery to use the column names to group, otherwise it often the case that you use the overview over the statement.

SELECT col1,col2
FROM
(
Your dynamic query here
) Subquery
Group by col1, col2

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||Assuming the value of STAI.Z4400 is always the same for the group do this:
.
.
.
MAX(Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), '')),
MAX(Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), '')),
.
.
.

That should fix it.

In this case, it would be easier to JOIN the STAI file intead of doing the subquery. Then just do

CASE WHEN Srtas.z02 IS NULL THEN '' ELSE Srtas.z02 END,
CASE WHEN Klant.z0102 IS NULL THEN '' ELSE Klant.z0102 END,

|||Thank you both for your opinion and help on this problem.

Reading your posts gave me some new ideas so I started changing the query like this:

SELECT

0,

Stati.z4414,

Stati.z4402,

'',

'',

'',

Max(Srtas.z02),

Max(Klant.z0102),

'',

'',

'',

sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)

FROM STATI

LEFT JOIN KLANT ON STATI.Z4400 = KLANT.Z0100 AND STATI.Z4402 = KLANT.Z0101

LEFT JOIN SRTAS ON STATI.Z4400 = SRTAS.Z00 AND STATI.Z4414 = SRTAS.Z01

WHERE

(Stati.z4402 Between '40000' AND 'ZONE6') AND

(Stati.z4414 Between '2005028' AND '2005028') AND

(Stati.z4417 = 'A')

GROUP BY Stati.z4414, Stati.z4402

This query seems to work.

I have to admit that there's a huge pile of dust ontop of my MSSQL-knowledge. Been a while since I last used it.

So if any of you have any comments on me doing something wrong, I would be pleased to hear it :-)

Problem with Scope parameter for SUM function

Is it possible to use a string expression for the scope parameter of the SUM
function?
I am using conditional grouping in my report and do not know the name of the
group I need to SUM for until the report is run.
I need to use an expression like this: = SUM(Fields!Field1.Value,
Code.GetMaxGroupNameUsed()), where I am determining the name of the
containing group in custom code.
Unfortunately I get this error:
The value expression for the textbox 'textbox18' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
Any ideas?
Thanks,
TessaScope names have to be string constants.
I'm not sure why you need dynamic scopes, but you can simulate them.
Assuming you have a function that returns a number between 1 and N, use an
expression similar to this:
=Choose( Code.DetermineScope(), Sum(Fields!F1.Value), Sum(Fields!F1.Value,
"Group1"), Sum(Fields!F1.Value, "Group2"), ......, Sum(Fields!F1.Value,
"DataRegionName"), Sum(Fields!F1.Value, "DataSetName") )
Notes:
* you can only use scope names of containing groups, and data regions (e.g.
table, list, matrix)
* for huge reports (i.e. with many data rows in the dataset) this approach
of simulating dynamic scopes will degrade performance
* documentation on the Choose function is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tessa" <nospam@.thanks> wrote in message
news:eXcayiYWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Is it possible to use a string expression for the scope parameter of the
SUM
> function?
> I am using conditional grouping in my report and do not know the name of
the
> group I need to SUM for until the report is run.
> I need to use an expression like this: = SUM(Fields!Field1.Value,
> Code.GetMaxGroupNameUsed()), where I am determining the name of the
> containing group in custom code.
> Unfortunately I get this error:
> The value expression for the textbox 'textbox18' has a scope parameter
that
> is not valid for an aggregate function. The scope parameter must be set to
a
> string constant that is equal to either the name of a containing group,
the
> name of a containing data region, or the name of a data set.
> Any ideas?
> Thanks,
> Tessa
>

Problem with ROUND function

When using ROUND(104.6146,2) - it comes out 104.61 (?)
When using ROUND(104.6146,3) - it comes out 104.615
When using ROUND(104.615,2) - it comes out 104.62
Same behavior in EXCEL with currency formatting and
changing decimal values from 2 to 3.
This seems inconsistent to me. The first one should be
104.62...This comes from a penny rounding debate with a customer -
I withdraw the question...
I can see how the "46" is less than "50" - so that the
first one could ROUND as 104.61...
Why am I working on Saturday on stuff like this?
>--Original Message--
>When using ROUND(104.6146,2) - it comes out 104.61 (?)
>When using ROUND(104.6146,3) - it comes out 104.615
>When using ROUND(104.615,2) - it comes out 104.62
>Same behavior in EXCEL with currency formatting and
>changing decimal values from 2 to 3.
>This seems inconsistent to me. The first one should be
>104.62...
>.
>|||These are consistent - if the next digit is 5 or greater round will =round the last significant digit you are requesting UP, otherwise it =will be rounded down. To get the behaviour you want ypou couyld have a =look at the ceiling functionin BOL.
Mike John
"Steve Z" <szlamany@.antarescomputing.com> wrote in message =news:008301c3b69e$5928a5f0$a301280a@.phx.gbl...
> When using ROUND(104.6146,2) - it comes out 104.61 (?)
> When using ROUND(104.6146,3) - it comes out 104.615
> When using ROUND(104.615,2) - it comes out 104.62
> > Same behavior in EXCEL with currency formatting and > changing decimal values from 2 to 3.
> > This seems inconsistent to me. The first one should be > 104.62...|||In SQL Server, ROUND(x,n) rounds x up or down to the nearest n decimal
places.
104.6146 is closer to 104.61 than 104.62 so the value is rounded down.
If you always want to round upwards (for positive values only):
ROUND(x+0.004,2)
--
David Portas
--
Please reply only to the newsgroup
--

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY0
UI1
PO1
LI0
MK1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY0
UIYes
POYes
LI0
MKYes
What i need is
course branch_exist
BYNo
UIYes
POYes
LINo
MKYes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!
Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegr oups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with replace function

Hi,

Please find the below scenario

Original Table

course branch_exist
BY0
UI1
PO1
LI0
MK1

select REPLACE(branch_exist,1,'yes') as branch from university;

displays

course branch_exist
BY0
UIYes
POYes
LI0
MKYes

What i need is

course branch_exist
BYNo
UIYes
POYes
LINo
MKYes

Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!What i need is:

Quote:

Originally Posted by

course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes

select
CAST branch_exist
WHEN 1 THEN 'yes'
WHEN 0 'no'
ELSE '?' END as branch
from university;

--
Tom
http://kbupdate.info/ | http://suppline.com/|||Oops, typo happens. Right version is:

select
CASE branch_exist
WHEN 1 THEN 'yes'
WHEN 0 THEN 'no'
ELSE '?'
END as branch
from university;

--
Tom
http://kbupdate.info/ | http://suppline.com/|||On Apr 9, 5:37 pm, "kb" <a...@.kbupdate.infowrote:

Quote:

Originally Posted by

Oops, typo happens. Right version is:
>
select
CASE branch_exist
WHEN 1 THEN 'yes'
WHEN 0 THEN 'no'
ELSE '?'
END as branch
from university;
>
--
Tomhttp://kbupdate.info/|http://suppline.com/

Hi Kb,

Thanks you !

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY 0
UI 1
PO 1
LI 0
MK 1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY 0
UI Yes
PO Yes
LI 0
MK Yes
What i need is
course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegroups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY 0
UI 1
PO 1
LI 0
MK 1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY 0
UI Yes
PO Yes
LI 0
MK Yes
What i need is
course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegroups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with PATINDEX function for case-sensitive information

Hi,

My database is not case-sensitive, but I want output like...

SELECT patindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

The default collation “SQL_Latin1_General_CP1_CI_AS” is case insensitive - CI stands for case insensitive, change the collation as “SQL_Latin1_General_Cp1_CS_AS” – here CS means case sensitive.

So the final query is,

SELECT patindex('%[A-Z]%','gaurang Ahmedabad' COLLATE SQL_Latin1_General_Cp1_CS_AS)

|||

Thanks for your reply, but still this will not work.

It will work like this, as I got reply in another forum forums.asp.net.

SELECT patindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','gaurang Ahmedabad' COLLATE SQL_Latin1_General_CP1_CS_AS)

Thanks,

Gaurang.

Problem with PATINDEX function

Hi all,

My database is not case-sensitive, but I want output like...

SELECTpatindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

Hi,

You can do this in two ways.

1) You can permamnetly change the case sensitivity settings for your database. Assuming you have SQL 2000 the following command will work for you

ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS

Run you query after this and it will perform case sensitive searches.

2) Use Collation key word. Change you query to

SELECTpatindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','gaurang Ahmedabad'COLLATE SQL_Latin1_General_CP1_CS_AS)

Collate SQL_Latin1_General_CP1_CS_AS stands forLatin1-General,case-sensitive, accent-sensitive

|||

Hi Girish,

Thanks a lot. It works fine.

Regards,

Gaurang Majithiya