Database

Re: Insert query and Cursor in same stored procedure

22-Jan-09 9:52

Ok Thanks. I have just looked at your example again and now understand a little more where you're coming from and i think that it would acually work for my insert statement by changing one of the lines from

UPDATE set sequence = sequence + 10 where id = @id

UPDATE set sequence = sequence + 5 where id = @id

which is great and I will use this, thanks Smile | :)

But I have a similar stored procedure for doing an update on an existing record, that is, updating the sequence of one of the records already in the table. Maybe I will look into my logic further for this too.

Thanks again Smile | :)

Wendelius22-Jan-09 9:50

Re: Insert query and Cursor in same stored procedure

22-Jan-09 9:50

I understood that you're trying to do ordering. For example you have ordinals 1,2,3,4 and 5 then you add a new record which has the ordinal 3 so old ordinals 3,4 and 5 will be replaced with 4,5 and 6 respectively. If that's the case, you could simply:
- update all rows, set ordinal = ordinal + 1 where ordinal >= 3
- insert the new row with ordinal 3

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

Jay Royall22-Jan-09 9:56

Re: Insert query and Cursor in same stored procedure

22-Jan-09 9:56

That is actually a good and very simple idea too, why didn't I think of that!

Will try that when I get to work tomorrow!

Thanks Smile | :)

Wendelius22-Jan-09 10:05

Re: Insert query and Cursor in same stored procedure

22-Jan-09 10:05

No problem.

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

Jay Royall22-Jan-09 21:31

Re: Insert query and Cursor in same stored procedure

22-Jan-09 21:31

This works great when inserting a new record and then updating each sequence but not when changing an existing record's sequence and then updating all the other sequences. But, with my new found knowledge, I'm sure that I'll work it out!

Thanks again all for your help.

Wendelius22-Jan-09 21:36

Re: Insert query and Cursor in same stored procedure [modified]

22-Jan-09 21:36

Liqz wrote:
, with my new found knowledge, I'm sure that I'll work it out!

I'm confident that you will Smile | :)

Liqz wrote:
Thanks again all for your help

No problem.

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

omlac21-Jan-09 23:35

omlac

21-Jan-09 23:35

hi,
i think you need to create a transaction for insert and then commit, there after you retrieve and update.

reagrds

modified on Thursday, January 22, 2009 5:49 AM

Re: Insert query and Cursor in same stored procedure

Jay Royall21-Jan-09 23:51

Reading html into a Sql Server Report

21-Jan-09 23:51

Yes, I thought this also and did try it but it didn't seem to work. Maybe I coded it incorrectly. I will try again!

Thanks for your help Smile | :)

omlac21-Jan-09 22:37

omlac

21-Jan-09 22:37

Hi all
How do i read html content into a sql server report. Or if possible may you please help me with links on where to do this.

Thank you.

conversion from access to oracle-select ?

premprakashbhati21-Jan-09 22:09

premprakashbhati

21-Jan-09 22:09

good morning sir/madam,
iam working on app in vb6.0 with msaccess ihave to convert that in oracle db..
here i have msaccess select statement..in which i am autogenerating codenumber..here iam trying to convert to oracle format..
but iam receiving Error:invalid relational operator

rsDetail.Open "SELECT max(to_number(cnor_cnee_id,3,13)) FROM cnor_cnee_master where (cnor_cnee_id,1,2)=" &amp; intBranchPrefix, DBConnection, adOpenStatic, adLockReadOnly

so please suggest the require corrections...
thanx inadvance..
prem...

Re: conversion from access to oracle-select ?

Wendelius21-Jan-09 22:39

IIf In SQL Compact Edition 3.5

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[^]

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

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

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

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?

Wendelius21-Jan-09 4:57