query optimization - Database Discussion Boards

First Prev Next

Re: conversion from access to oracle-select ?

Wendelius21-Jan-09 22:39

Wendelius

21-Jan-09 22:39

premprakashbhati wrote:
where (cnor_cnee_id,1,2)=" & intBranchPrefix

What is the condition you're trying use? Field cnor_cnee_id should be what?

premprakashbhati wrote:
max(to_number(cnor_cnee_id,3,13))

Also what do you want to do with cnor_cnee_id here?

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

IIf In SQL Compact Edition 3.5

VinothRao21-Jan-09 20:54

VinothRao

21-Jan-09 20:54

Hi,

I have below select statement using MS Access.

SELECT Sum(IIf(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,1,0)) AS BALANCED
FROM SELECTEDPRODUCTS1
INNER JOIN Sku_Statistics_File ON (SELECTEDPRODUCTS1.LOCATION=Sku_Statistics_File.Location) AND (SELECTEDPRODUCTS1.PRODUCT=Sku_Statistics_File.Product);

How to convert to SQL CE? It seems i need to use CASE to replace the IIf, but i donno how to convert. Please help.

Thx.

Re: IIf In SQL Compact Edition 3.5

Ashfield21-Jan-09 20:59

Ashfield

21-Jan-09 20:59

Just replace IIF with

CASE WHEN x=1 and a=z ... THEN d=1 ELSE d=2 END

For further details see the BOL help on CASE

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

Re: IIf In SQL Compact Edition 3.5

VinothRao21-Jan-09 21:17

VinothRao

21-Jan-09 21:17

Thx for reply.

Is this the way should be done?
I get hitting error at "Sku_Statistics_File.PastDue = 0,1,0 " saying "," is invalid

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,1,0 THEN 0 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);

Pls help. Thx.

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