Re: query optimization - Database Discussion Boards

First Prev Next

Re: IIf In SQL Compact Edition 3.5

Ashfield21-Jan-09 22:56

Ashfield

21-Jan-09 22:56

No, you have missed the point a bit.

SELECT Sum(CASE WHEN Sku_Statistics_File.ExcessSA=0 
AND Sku_Statistics_File.ExcessSOH=0
AND Sku_Statistics_File.StockOutFirst6wks=0 
AND Sku_Statistics_File.StockOutNext6wks=0 
AND Sku_Statistics_File.PastDue=0) THEN 1 ELSE 0 END) AS BALANCED
FROM SELECTEDPRODUCTS1
INNER JOIN Sku_Statistics_File ON (SELECTEDPRODUCTS1.LOCATION=Sku_Statistics_File.Location) AND (SELECTEDPRODUCTS1.PRODUCT=Sku_Statistics_File.Product);

the THEN is the first condition of your IIF (true), the ELSE is the second condition (false)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Sql Script to Pull Records Based on Specific Date within A Year

dboy22121-Jan-09 3:10

dboy221

21-Jan-09 3:10

Hello All,
I have a sql script that pulls certain records by my company's fiscal year which ends 8/31/yyyy. As I am trying to pull records based on the year (yyyy) I have written a condition as such a.THRU_DATE = '8/31/' + Year(GetDate()), however I receive the following syntax error Syntax error converting the varchar value '8/31/' to a column of data type int. Of the course the Thru_Date column type is datetime. Can someone please assist with the proper syntax for such a condition. Thanks in advance to all that reply

Re: Sql Script to Pull Records Based on Specific Date within A Year

dboy22121-Jan-09 3:26

dboy221

21-Jan-09 3:26

I found the answer to my own question using the CAST function. FYI, a.THRU_DATE = CAST('8/31/' + @year AS DateTime).

query optimization

Uma Kameswari21-Jan-09 1:05

Uma Kameswari

21-Jan-09 1:05

I have to use aggregate function sum() on a table having more than 4 millions of records. It is a history table. Are there any tips to be followed. the queries are a follows

SELECT @TransferIn = isnull(sum(abs(trxqty)),0) 
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr 
and trnstloc = @route and docdate between @FromDate and @ToDate
				
SELECT @TransferOut = isnull(sum(abs(trxqty)),0) 
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr 
and trxloctn = @route and docdate between @FromDate and @ToDate 
				
SELECT @Sales = isnull(sum(abs(trxqty)),0) 
FROM iv30300 WHERE doctype=6 and itemnmbr = @itemnmbr and 
trxloctn = @route

In the above query @route,@FromDate and @ToDate are user inputs

Re: query optimization

Senthil_M21-Jan-09 1:20

Senthil_M

21-Jan-09 1:20

Dear Uma,

Please give me the table structure with the available indexes.

Senthil

Re: query optimization

Uma Kameswari21-Jan-09 2:44

Uma Kameswari

21-Jan-09 2:44

Dear Senthil,

The table structure is as follows
[TRXSORCE] [char](13)NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LNSEQNBR] [numeric](19, 5) NOT NULL,
[UOFM] [char](9) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[TRXLOCTN] [char](11) NOT NULL,
[TRNSTLOC] [char](11) NOT NULL,
[TRFQTYTY] [smallint] NOT NULL,
[TRTQTYTY] [smallint] NOT NULL,
[IVIVINDX] [int] NOT NULL,
[IVIVOFIX] [int] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[QTYBSUOM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] NOT NULL

There are no indexes on this table. I cannot create any indexes as it a table used by ERP named Microsoft Dynamics GP

Re: query optimization

J4amieC21-Jan-09 4:28

J4amieC

21-Jan-09 4:28

Uma Kameswari wrote:
I cannot create any indexes as it a table

Then you cant really optimize the table, can you?

Re: query optimization

Wendelius21-Jan-09 4:57

Wendelius

21-Jan-09 4:57

Since you don't have any indexes, you cannot create them and you don't have joins, there's not much you can do. The only thing that comes in mind is that yu copy the data to another table (perhapes in another database), index it and execute the query there. However this isn't a good solution if you need the data to be up-to-date.

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

Transfering Database?

Karthick_gc20-Jan-09 23:58

Karthick_gc

20-Jan-09 23:58

How to transfer database from one system to other system?

Re: Transfering Database?

perle121-Jan-09 0:18

perle1

21-Jan-09 0:18

Simply make a database backup and restore. Cool | :cool:

Re: Transfering Database?

Reza Raad21-Jan-09 0:19

Reza Raad

21-Jan-09 0:19

you can do one way of these:
1-Backup from source database and Restore on destination, for this option your database server must be same
2-using Import/export tool from Integration service of Sqlserver, for this option database server doesn't matter usually.

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

Re: Transfering Database?

Senthil_M21-Jan-09 1:14

Senthil_M

21-Jan-09 1:14

Detach the Database files (.mdf and .ldf).
Move the files physically.
Attach the files to the database.
If previous log is required attach both .mdf and .ldf or else only .mdf can be attached.

Re: Transfering Database?

Karthick_gc28-Jan-09 19:49

Karthick_gc

28-Jan-09 19:49

What s mean by Detach?

Re: Transfering Database?

Senthil_M28-Jan-09 20:33

Senthil_M

28-Jan-09 20:33

Karthik,

Detaching means, removing database files from the server and files will remain in the same place without connecting to server.

Check
sp_detach_db
sp_attach_db
sp_attach_single_file_db

Senthil

select problem

mahmoudinirat20-Jan-09 21:39

mahmoudinirat

20-Jan-09 21:39

i have 3 tables like this
t1 --->table1

t1Id t1Name
1 jj
2 qq

t2----->table2
t2Id t2Name
1 rr
2 uu

t3---table 3
t3Id tableName
1 t1
2 t2

i need the foolwing result
name
jj
uu

Re: select problem

Blue_Boy20-Jan-09 22:07

Blue_Boy

20-Jan-09 22:07

select t1.t1Name,t2.t2Name<br />
from t1,t2,t3<br />
where t1.t1id=t2.t2id <br />
and t1.t1id=t3.t3id and t2.t2id=t3.t3id

Hope it will hep you.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: select problem

Senthil_M21-Jan-09 1:11

Senthil_M

21-Jan-09 1:11

Please give more details about the relationship between those 3 tables.

TSQL Error on the Following Statement

Vimalsoft(Pty) Ltd20-Jan-09 20:54

Vimalsoft(Pty) Ltd

20-Jan-09 20:54

Good Morning Guys

I have the Following statement, that works well when i test it alone

declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] 'MasekoTS','MasterDatabase', @Results OUT

But now, i want to include this code in a Stored Procedure and i tried to do it like this

SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + '''@Results OUT'''
execute ( @sql1 )

and i get the Following Error

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'sp_RestoreDatabase_O_Bookin'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_RestoreDatabase_O_Bookin'.

Thank you

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

Re: TSQL Error on the Following Statement

Ashfield21-Jan-09 0:37

Ashfield

21-Jan-09 0:37

Instead of doing execute ( @sql1 ) try Print @sql1 to see where its missing a quote - thats my usual method.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: TSQL Error on the Following Statement

Vimalsoft(Pty) Ltd21-Jan-09 21:46

Vimalsoft(Pty) Ltd

21-Jan-09 21:46

Thanks i have Executed the statement without String Manipulation and its working

Thanks

Re: TSQL Error on the Following Statement

Ashfield22-Jan-09 1:13

Ashfield

22-Jan-09 1:13

Pleased to have been of service Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: TSQL Error on the Following Statement [modified]

Jon_Boy21-Jan-09 2:28

Jon_Boy

21-Jan-09 2:28

Just a pointer to help you out in the future with debugging dynamic SQL. When you have a problem like this, rem out the EXEC(@SQL) and put a select @SQL or PRINT @SQL.

Take the returned value and attempt to exec it in a diff. query window. Compare your orig statement to the dynamic one and find the error.

I think the error in this case is the missing , after the file name.

Any suggestions, ideas, or 'constructive criticism' are always welcome.

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

modified on Wednesday, January 21, 2009 8:46 AM

Re: TSQL Error on the Following Statement

Vimalsoft(Pty) Ltd22-Jan-09 3:50

Vimalsoft(Pty) Ltd

22-Jan-09 3:50

Thanks i got the Solution, i just used it without any string Manipulation

thanks for your help Smile | :)

Delete instead of Update

CodingYoshi20-Jan-09 19:06

CodingYoshi

20-Jan-09 19:06

Is it sometimes better to just delete and then reinsert some records instead of updating them? I have a complex scenario which is hard to explain but I am thinking that instead of updating, deleting and inserting is a better option. Has anyone come across such a scenario where deletion and reinsertion is better than update?

Re: Delete instead of Update

Mycroft Holmes20-Jan-09 20:56

Mycroft Holmes

20-Jan-09 20:56

If you are doing a delete of a set of records, as apposed to a delete of ALL records, then the table is scanned to locate the records to be deleted. The delete action also affects any indexes on the table. Insert also affects the indexes but is less costly.

Update only affects the indexes if the indexed fields are updated.

You can do both in management Studio with the execution plan and costs turned on and compare your specific process.

Never underestimate the power of human stupidity
RAH

Last Visit: 31-Dec-99 18:00 Last Update: 7-May-24 2:40

Refresh

ᐊ Prev 1...930 931 932 933934935 936 937 938 939 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.