Database

CASE / LIKE code will not compile.

17-Jan-09 7:53

No problem. I found that there are severa problems for SQL Server 2000 on Windows 2003 server concerning DTC. Lots of hotfixes available so you might want to check those also.

The need to optimize rises from a bad design.My articles[^]

dptalt16-Jan-09 7:00

Re: CASE / LIKE code will not compile.

16-Jan-09 7:00

The following code is used in the where clause in a stored procedure and it will not compile.

CASE WHEN @ciSubjectId <> '' THEN tJ.cdSubjectCode LIKE '%' + @ciSubjectId + '%' ELSE tJ.cdSubjectCode LIKE tJ.cdSubjectCode END

How can I get this to compile?

Wendelius16-Jan-09 8:24

Re: CASE / LIKE code will not compile.

16-Jan-09 8:24

You cannot use CASE to modify the logic in where clause. Instead you must redefine the logic. If I understood you correctly, you use @ciSubjectId if it's not empty , otherwise you use an exact match which is always true (unless the value inside cdSubjectCode is NULL. If that was correct, your statement could be something like:

... tJ.cdSubjectCode LIKE (CASE @ciSubjectId
                              WHEN '' THEN tJ.cdSubjectCode 
                              ELSE '%' + @ciSubjectId + '%'
                           END)...

The need to optimize rises from a bad design.My articles[^]

dptalt19-Jan-09 3:15

Re: CASE / LIKE code will not compile.

19-Jan-09 3:15

The change you suggested compiles but always returns 0 rows.

Wendelius19-Jan-09 6:50

19-Jan-09 6:50

Could it be because of other conditions or the value in the variable. I made a simple test and it seems to work fine. You can try this on your database and change the value of @ciSubjectId to whatever you like:

declare @ciSubjectId varchar(50);
SET @ciSubjectId = 'table';
SELECT *
FROM sysobjects
WHERE
     --tJ.cdSubjectCode 
     name LIKE (CASE @ciSubjectId                              
                WHEN '' THEN name
                ELSE '%' + @ciSubjectId + '%'
                END)

The need to optimize rises from a bad design.My articles[^]

TSQL Case error

dptalt16-Jan-09 6:56

16-Jan-09 6:56

The following code is used in the where clause in a stored procedure and will error when @ciDept = '4,25'. It works when @cilocn = '4'

tLD.idClientDeptNumb IN (CASE WHEN @ciDept <> '' THEN CONVERT(INT, @ciDept) ELSE tLD.idClientDeptNumb END

The error thrown is:
Syntax error converting the varchar value '4,25' to a column of data type int.

How can this error be resolved?

Re: TSQL Case error

Wendelius16-Jan-09 8:08

16-Jan-09 8:08

dptalt wrote:
Syntax error converting the varchar value '4,25' to a column of data type int

decimal separator in SQL is dot. If you're unable to modify it on the calling side, you can use replace function to modify it in the stored procedure or in the statement.

Also integer cannot handle decimals so you should use for example real as datatype:

tLD.idClientDeptNumb IN (
   CASE 
     WHEN @ciDept <> '' 
         THEN CONVERT(REAL, REPLACE(@ciDept, ',', '.')) 
      ELSE tLD.idClientDeptNumb 
   END...

But if you can, prefer using numeric variables if they contain numeric data.

The need to optimize rises from a bad design.My articles[^]

Re: TSQL Case error

dptalt19-Jan-09 3:14

19-Jan-09 3:14

Replacing ',' to '.' always returns 0 rows.

I thought the IN list command requires the elements in the list to be separated by commas?

If I do not replace the commas with decimals I get the following error:

Error converting data type varchar to real.

Re: TSQL Case error

Wendelius19-Jan-09 6:51

False Result at a SQLite Query [modified]

19-Jan-09 6:51

As in the other post from you, could it be because of other conditions. If it's not working, could you post the whole query.

The need to optimize rises from a bad design.My articles[^]

sdf VS mdf

Mohammad Dayyan16-Jan-09 2:34

Mohammad Dayyan

16-Jan-09 2:34

Hi experts.
I have a basic question . Blush | :O

I'm going to create a local database for may WPF App, but I don't know which one is better, sdf database or mdf database.
I've searched , but couldn't find anything special.

Could you explain the differences between them ?
Thanks in advance.

Re: sdf VS mdf

Steve Hansen16-Jan-09 2:54

Steve Hansen

16-Jan-09 2:54

mdf will require a Sql Server installation (can be express) while sdf will run without any database.
Mdf should have better performance since it runs on an actual database, but sdf will be much easier to deploy Smile | :)

Re: sdf VS mdf

Mohammad Dayyan16-Jan-09 2:58

Mohammad Dayyan

16-Jan-09 2:58

Is sdf a XML file ?

Re: sdf VS mdf

Steve Hansen16-Jan-09 3:00

Steve Hansen

16-Jan-09 3:00

Both are a real database file. .sdf file are called embedded database (the database server is just a class library that you access) so your application will become the database "server".

Marty Glaubitz16-Jan-09 2:24

Marty Glaubitz

16-Jan-09 2:24

First: i'm sorry for my bad english

I've a problem,
in my webaplication, i use sqlite to manage some data for links
in a simple favourite links page. To get all links i use:

$connectionhandle = sqlite_open('mystart2', 0666, $sqliteerror);
$result = sqlite_query($connectionhandle, $query, SQLITE_ASSOC);
$result = sqlite_fetch_array($result);
print_r($result);

ok, the functions for connecting and doing the query are capsuled
in classes but i think that doesn't matter

This query returns just the first row from this table,
so just one row, but i'm sure that there exists at least 3 rows.
That means that the query should return 3 rows, not just one.
What's the problem?

modified on Friday, January 16, 2009 4:43 PM

Re: False Result at a SQLite Query

Luc Pattyn16-Jan-09 2:36

Luc Pattyn

16-Jan-09 2:36

Hi,

it may be wise to show us some code, the SQL line seems fine.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

I use ListBoxes for line-oriented text output (not TextBoxes), and PictureBoxes for pictures (not drawings).

modified on Friday, January 16, 2009 8:57 AM

Getting Count(*) ,SQL Server query for below requirement..

K V Sekhar15-Jan-09 23:13

K V Sekhar

15-Jan-09 23:13

Hi all,

I have table with data in my database as follows

table name :sample (col 'ps' is primary key)

village ps val
1 1 56
1 2 67
1 3 65
1 4 70
2 5 74
2 6 64
2 7 32
3 8 46
3 9 56
4 10 64

My requirement is i have to get the Count of 'village' whose val > 60 and val < 60

for that i tried in the following way..(for finding avg 'val' of villages)

select sum(val)/count(ps) assessment from sample group by village
output:
assessment
66
56
59
54

But i need the count of above assessment values > 60 and < 60
i.e count of assessment > 60 , < 60

For that i tried in following way(i know i wrote wrong...but it gives you the idea what i am trying for)

select count(*) sample where (select sum(val)/count(ps) assessment from sample group by village) > 60

Please suggest me how to do that...

thanks in advance.

Re: Getting Count(*) ,SQL Server query for below requirement..

Caio Kinzel Filho15-Jan-09 23:51

Caio Kinzel Filho

15-Jan-09 23:51

you need to use HAVING...

something like:
select sum(val)/count(ps) assessment from sample group by village HAVING sum(val)/count(ps) > 60

let me know if it helps

Intelligence is almost useless for those who have nothing else!

Email: caiokf@gmail.com

Re: Getting Count(*) ,SQL Server query for below requirement..

K V Sekhar16-Jan-09 0:05

K V Sekhar

16-Jan-09 0:05

thank you for your suggestion.
I got it.
Its similar approach as you suggested.

select count(*) from sample group by village
having avg(value) > 60

select count(*) from sample group by village
having avg(value) < 60

Database Restore Error TSQL

Vimalsoft(Pty) Ltd15-Jan-09 21:33

Vimalsoft(Pty) Ltd

15-Jan-09 21:33

Good Morning ALL

I want to Restore a database, i have the Following SP

Create PROCEDURE [dbo].[sp_RestoreDatabase] 

	@dbname char(32), 	-- the database name to restore as
	@filename char(32),
	@Results int OUTPUT
  
AS

set nocount on

declare @path char(256)
set @path = 'C:\DATABASES\' 	-- the location of the backuped up database file	(on the SQL Server)

execute('sp_ClearDatabaseConnections ' + @dbname) --kill the existing connections to the current Database

-- Restore the database
RESTORE DATABASE  @dbname 
FROM DISK ='C:\DATABASES\'+ @filename
WITH MOVE 'TNGoedit_Data' TO 'C:\DATABASES\'+ @dbname + '.mdf' ,
MOVE      'TNGoedit_Log' TO 'C:\DATABASES\'+ @dbname + '_log.ldf',
REPLACE,RECOVERY;
-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
  SET  @Results = 1
end
else begin
   SET @Results = 0
end

When i Compile this SP i get the Following Error

Msg 102, Level 15, State 1, Procedure sp_RestoreDatabase, Line 27<br />
Incorrect syntax near '+'.

Where is my Problem

Thanks

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za

Wendelius15-Jan-09 22:02

15-Jan-09 22:02

Do you have an extra comma at the end of this line:

MOVE      'TNGoedit_Log' TO 'C:\DATABASES\'+ @dbname + '_log.ldf',

The need to optimize rises from a bad design.My articles[^]

Vimalsoft(Pty) Ltd15-Jan-09 22:24

Vimalsoft(Pty) Ltd

15-Jan-09 22:24

No , the Problem points on the line that uses the "+" Sign

Wendelius15-Jan-09 22:59

15-Jan-09 22:59

There was a plus on that line too, but if you double click the error, which of those lines is highlighted?

The need to optimize rises from a bad design.My articles[^]

Vimalsoft(Pty) Ltd15-Jan-09 23:01

Vimalsoft(Pty) Ltd

15-Jan-09 23:01

It goes to this line

FROM DISK = @path + @filename

Thanks

Wendelius15-Jan-09 23:11