Logic Problem In Cursor/SPROC

Jul 23, 2005

SQL Server 2000

I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.

However, the SPROC has an unexpected side effect.

If I run it in its current incarnation, it only sends one email and
then exits. However, if I remove or comment out the block

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @JobID)

then it runs through the whole list as anticipated. Conceptually, it
seems that the records in the cursor are changed if the underlying
table is updated.

Here is pseudo-code for what the SPROC does - whole SPROC below (with
part of the "WHERE" clause removed for readability). I haven't
included any table schemae but I don't think they're relevant.

1. Open a cursor and fetch a list of all companies that need email
notification for pending jobs.

2. While records in the cursor...

a) Format and send email from the cursor
b) Write a record to the audit table
c) Update the jobs table for the current record

3) Fetch next from cursor


There is an update trigger on the tblJobs table thus:

CREATE TRIGGER "tblJobs_UTrig" ON dbo.tblJobs FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblDistributionMaster' */
IF UPDATE(fldDistributionID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDistributionMaster, inserted WHERE
(tblDistributionMaster.fldDistributionID = inserted.fldDistributionID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tblDistributionMaster''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblPrinterJobs' */
IF UPDATE(fldJobID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblPrinterJobs WHERE
(deleted.fldJobID = tblPrinterJobs.fldJobID)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or
changed. Since related records exist in table ''tblPrinterJobs'',
referential integrity rules would be violated.'
ROLLBACK TRANSACTION
END
END

I can't see that this is relevant - I think it's something to do with
where I'm updating the fldEmailProcessed field, but I need to do this
here, as outside the loop the fldJobID might be lost.

Sorry it's all such a mess. Hope someone can help!

Thanks

Edward

/*
Checks all Jobs that were set to Despatched more than 24 hours ago,
selects those that the companies elected to get email notification, and
sends them emails.
*/

CREATE PROCEDURE stpSendEmailNotification

AS

DECLARE @rc int
DECLARE @JobID int
DECLARE @CompanyID int
DECLARE @DocumentNumber varchar(50)
DECLARE @Email varchar(50)
DECLARE @DocumentURL varchar(750)
DECLARE @Dat varchar(20)
DECLARE @Subject varchar(100)

SET @Dat = LEFT((CONVERT(varchar, GETDATE(), 100)), 11)


DECLARE MailList CURSOR FOR

SELECT
tblJobs.fldJobID,
tblJobs.fldDocumentNumber,
tblCompany.fldEmail,
tblCompany.fldCompanyID,
tblJobHistory.fldDocumentURL
FROM
tblJobHistory INNER JOIN
tblJobs ON tblJobHistory.fldJobID = tblJobs.fldJobID
INNER JOIN
tblDistributionMaster ON tblJobHistory.fldDistributionID =
tblDistributionMaster.fldDistributionID INNER JOIN
tblCompany ON tblJobHistory.fldCompanyID =
tblCompany.fldCompanyID
WHERE
(tblJobs.fldEmailProcessed = 0)

OPEN MailList


FETCH NEXT FROM MailList INTO
@JobID,
@DocumentNumber,
@Email,
@CompanyID,
@DocumentURL

WHILE @@FETCH_STATUS = 0
BEGIN

/* Format and send the email to the customer here */
SET @Subject = N'Document Distribution No: ' + @DocumentNumber +
N' - Date: ' + @Dat

exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'techlib@myco.co.uk',
@FROM_NAME = N'Edward Collier',
@replyto = N'techlib@myco.co.uk',
@TO = @Email,
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = @Subject,
@type = N'text/plain',
@message = @DocumentURL,
@messagefile = N'',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'12.34.5.67',
@timeout = 10000
select RC = @rc

/* Write result to audit table */
INSERT INTO tblEmailAudit
(
fldRCNo,
fldEmail,
fldDocumentNumber,
fldDate,
fldCompanyID
)
VALUES
(
@rc,
@Email,
@DocumentNumber,
GETDATE(),
@CompanyID
)

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @JobID)

FETCH NEXT FROM MailList INTO
@JobID,
@DocumentNumber,
@Email,
@CompanyID,
@DocumentURL
END

CLOSE MailList
DEALLOCATE MailList
GO

View 1 Replies


ADVERTISEMENT

SQL/SPROC Beginner: Loop Logic Problem

Sep 23, 2005

hi.

i am getting some weird behaviour in my sql server 2000 code
pasted below. When using the step-through, it seems that
i get to the line: While objReader.Read()
and then the compiler jumps to "End Try" without going inside the objReader.Read() statement. I am new to this and would
appreciate some insight. why isn't entering the conditions within the while loop? I am new to sql and stored procedures so, i'd appreciate any advice at the moment.

thanks in advance.



Code:


Private Function VerifyCredentials(ByVal emailAddress As String, _
ByVal password As String) As Boolean
'<sumamry>
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
'</summary>
' ||||| First is the Connection Object for an Access DB
Dim MyConn As SqlConnection = New SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")

'<sumamry>
' ||||| Create a OleDb Command Object
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
'</summary>

' ||||| To Access a Stored Procedure in Access - Requires a Command Object
Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object

MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2 As SqlParameter
'<sumamry>
' ||||| Add the parameters to the parameters collection of the
' ||||| command object, and set their datatypes (OleDbType in this case)
'</summary>
objParam1 = MyCmd.Parameters.Add("@emailAddress", SqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@password", SqlDbType.VarChar)

'' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
'' ||||| Set the value(s) of the parameters to the passed in values
objParam1.Value = _emailAddress.Text
objParam2.Value = _password.Text

' ||||| Try, catch block!
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
If MyConn.State = ConnectionState.Closed Then
' ||||| DB not already Open...so open it
MyConn.Open()
End If

' ||||| Create OleDb Data Reader
Dim objReader As SqlDataReader
objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
' ||||| Close the Reader and the Connection Closes with it

'PROBLEM HERE: NEVER ENTERS CONDITIONS OF WHILE LOOP
While (objReader.Read())
If CStr(objReader.GetValue(0)) <> "1" Then
Return False
'lblMessage.Text = "Invalid Login!"
Else
objReader.Close() ' ||||| Close the Connections & Reader
Return True
End If
End While
Catch ex As Exception
lbTEMP.Text = ex.ToString 'tmp errorhandling
Return False
'lblMessage.Text = "Error Connecting to Database!"
End Try

View 7 Replies View Related

Create A Cursor Inside A Sproc

May 16, 2008

I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:


StudentInfo
StudentID StudentName DeptID
101 John 10
102 Alex 10
103 Beth 20
ClassInfo
ClassID DeptID
901 10
902 10
225 20
I want to create a Sproc which will retreive the student's classes in DeptID 10

Following is the Sproc and cursor:

use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int

Declare classCursor CURSOR FOR
SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID)

DECLARE @ClassID INT
DECLARE @DeptID INT

OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
@ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
(Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
+CONVERT(NVARCHAR (10), @ClassID) + ','
+CONVERT(NVARCHAR (2), @DeptID)+')'
FROM [myTest].dbo.StudentInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID))

PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101

Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)

Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.

View 8 Replies View Related

Cursor In Sproc To Kill Spids Loops Forever

Mar 20, 2000

I need a stored proc to kill spids, but the following sproc loops infinitely with the same [correct] spid being printed out. What am I doing wrong?

The select statement, when I execute it via the query grid, returns the correct and finite number of spids.

Any help greatly appreciated.
Judith


CREATE PROCEDURE rasp_KillDBProcess
@dbname varchar(128)
AS
declare @KillSpid smallint
declare @SQL varchar(1000)
--
declare DBCursor cursor Forward_only for SELECT distinct l.spid
FROM master.dbo.syslocks l INNER JOIN
master.dbo.sysdatabases d ON l.dbid = d.dbid
WHERE (d.name = N'coj_pcisdata')
open DBCursor
--
Fetch next from DBCursor into @Killspid
--
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
begin
print 'spid = ' + cast(@killspid as varchar(12))
--exec ('kill ' + @killspid)

end
--
end
Fetch next from DBCursor into @Killspid
--
close dbcursor
deallocate dbcursor
print 'end'

return

View 1 Replies View Related

Copy Subtree, Recursive Sproc With Cursor Doesn't Work

Sep 20, 2007

Hi all,

I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London

now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy


I have this sproc:



Code Snippet

alter proc CopyObject


(@ObjectId int,

@NewParentId int)

as


declare @NewId int,

@NewName varchar


select @NewId = max(Id) + 1 from Object

select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId


-- copy object

INSERT INTO [Object]


([Id]

,[Name]

,[ParentId]

select @NewId,


@NewName,

@NewParentId

from [Object]

where Id = @ObjectId


-- copy children and set their parent to the newly created object

declare c cursor fast_forward for


select Id

from [Object]

where ParentId = @ObjectId


declare @ChildId int


open c

fetch next from c into @ChildId


while @@fetch_status = 0

begin


exec CopyObject


@ObjectID = @ChildId,

@NewParentId = @NewId

fetch next from c into @ChildId

end

close c

deallocate c





But htis throws an error that the cursor already exists:

Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66

A cursor with the name 'c' already exists.

Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72

The cursor is already open.

I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.

Any ideas?

Thanks in advance,

Gert-Jan

View 3 Replies View Related

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.

Feb 13, 2007

I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View 3 Replies View Related

EXEC Of A Sproc Within Another Sproc

Apr 23, 2004

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy



Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int

EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended

WHERE
EntryLogID = @EntryLogID

EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID



Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID

RETURN


*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID


UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID

View 2 Replies View Related

Exec Sproc In Sproc

Jan 20, 2004

create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID


GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??

Tks

View 2 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View 1 Replies View Related

Cursor Inside A Cursor

Oct 5, 2004

I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View 1 Replies View Related

Need Some Logic Help

May 10, 2008

Can anyone see where my logic may have gone a stray?
A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150.
I want to then tell them they cannot do this.
@AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT  tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM         tblPurchase INNER JOIN                      tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND 

View 2 Replies View Related

Need Some Logic Help

Jan 6, 2004

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?

View 2 Replies View Related

Help With Logic...

Apr 27, 2006

tbl_one hv 8mil rows, tbl_2 have 8k rows...

if
select count(*) from tbl_one
where sub_col1 = 2

return 3mil rows

and

select count(*) from tbl_2
where ad_col1 = '000009'

return 4k rows

HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')

return more than 12 billion rowss?? helpp..

View 7 Replies View Related

Help The Logic

May 3, 2006

I have to write trigger to relate two table.

If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.

How this can be done, do we need to point the common key fields in both table while inserting

View 8 Replies View Related

Is This Right Logic

Jun 12, 2007

What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.

This is what i came up with, will it evaluate the way i need it to?

case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV

Thank-you

View 4 Replies View Related

SQL Logic

Mar 13, 2008

I have a logic problem,

I am selecting from a table

Select * from TAB_A
where state not in (1,4)
and create_date < '2008-01-01'
and
(
(x != 2 and y != 1 and z != 4) or
(x != 6 and y != 3 and z != 1) or
(x != 8 and y != 0 and z != 9)
)


then for example i am getting results where x,y and z is equal to one
or more of above combinations ?

Now i vaguely remember that using or with a != messes up the logic ?
if so can i use an NOR ? does it exist ?

View 5 Replies View Related

Logic To Use

Mar 25, 2008



I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.


Table 1 has columns A and B (tblemployee has location and employeenumber )
Table 2 has columns C and A (tblocation has locationID (identity) and location)
Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)

I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.

Thanks in advance.

View 7 Replies View Related

Logic

Nov 14, 2007

I am having a little problem with my logic.
i have a table simplehoursassignment that has a field named br.
i need to get the value from simplehoursassigment & say if @mybit AND br > 0
set each day.




--CREATE PROCEDURE rpt_siteMealList(

-- (@cmb1 AS VARCHAR(100)) WITH ENCRYPTION

--)

--

--AS

DECLARE @dtm1 AS DATETIME

DECLARE @mybit AS INTEGER

SET @mybit=10

SELECT @mybit = CASE datepart(dw,@dtm1)

WHEN 1 THEN 1 -- 'Sunday'

WHEN 2 THEN 2 -- 'Monday'

WHEN 3 THEN 4 -- 'Tuesday'

WHEN 4 THEN 8 -- 'Wednesday'

WHEN 5 THEN 16 -- 'Thursday'

WHEN 6 THEN 32 -- 'Friday'

WHEN 7 THEN 64 -- 'Saturday'

END

SELECT br FROM simplehoursassignment

IF @mybit AND br > 0

BEGIN

CASE WHEN @mybit = 1 THEN 'Sunday'

WHEN @mybit = 2 THEN 'Monday'

WHEN @mybit = 3 THEN 'Tuesday'

WHEN @mybit = 4 THEN 'Wednesday'

WHEN @mybit = 5 THEN 'Thursday'

WHEN @mybit = 6 THEN 'Friday'

WHEN @mybit = 7 THEN 'Saturday'

END

END

View 3 Replies View Related

Logic In T-SQL

Apr 30, 2008

I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote.
Does anyone know what the problem is?




Code Snippet
SELECT s.*
FROM Quote q
INNER JOIN SubQuote s
ON q.id = s.quoteID
INNER JOIN TakeOffSheetItem t
ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid
INNER JOIN PipeGroup p
ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID
WHERE (q.id = 1
AND q.deleted = 0
AND s.deleted = 0
AND t.deleted_takeoffitem = 0
AND p.deleted_PipeGroup = 0)
ORDER BY s.subquoteid






Thanks

K

View 4 Replies View Related

Client Side Cursor Vs Sever Side Cursor?

Jul 20, 2005

I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View 1 Replies View Related

Database Logic

Jul 31, 2006

I am trying to figure out how to set up this database.
Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory)
Thanks,Mick

View 1 Replies View Related

Need The Logic To Get This Done Without Cursors

Mar 20, 2007

Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it? 

View 4 Replies View Related

SQL Logic Problem

May 21, 2007

I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number.
Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a  trivial task.

View 2 Replies View Related

Db Logic - Can't Put It Back Together

Feb 27, 2006

Hi there!
Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.
Overview:
I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.
Layout for a magazine will look something like this:
Size              Trim          Type           Bleed
Full Page      280x440     270x430       290x450
Half Page     140x220     130x210       150x230
etc...
Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.
Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.
 
Break it down!
Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).
I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!
 
The Problem.
The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.
I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.
 
Any ideas on how i could manage this?
 
If you made it tis far through my question then thanks anyways! I hope you can help me out!
 
Ta

View 4 Replies View Related

Views Having Logic? - Please Help!!!

Oct 26, 1999

I need to create a view joining 2 tables(1st with data, 2nd is a lookup) but I need to have some
logic(IF/ ELSE,ELSE and an UPDATE).

What is the best way?

-aw

View 1 Replies View Related

Complex Sql Logic... Need Help

Feb 15, 2007

okay;

i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.

tblSlots:
PKSlotINDEX
datStartTime
datEndTime

then i have a table called tblPersons

tblPersons:
PKPersonINDEX
txtLast
txtFirst

then i have a table called tblSchedule

tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX

i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.

wow complicated isnt it...

the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.


thanks!

View 1 Replies View Related

Query Logic

Jul 28, 2005

I have 2 tables:
First table: empID,PlanID,groupID
Second: PlanID,groupID,EffectiveDate,TerminationDate,DeadlineDate
I need to show only employee with in spesific group who is not
enroll for the current month until deadline passed.

Example:
empID PlanIDgroupID
11012
PlanIDGroupIDEffectiveDate TerminationdateDeadlineDate
101208/01/200508/31/200508/15/2005
111209/01/200509/31/200509/15/2005
91208/01/200508/31/200508/15/2005

If I run it today I should not get any results back. If I run in
on 8/15/2005 I should get back data with palnID11.

View 1 Replies View Related

Help With JOIN Logic...

Jul 27, 2004

Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...

Zip Code OR City State

By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.

The Psuedo code is somethinglike this...

SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @This ZipCode
OR
(In @State AND @City)

-------------------
Table Dealers
-------------------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
-------------------

-------------------
Table ZipCodes
-------------------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
-------------------

I have achieved this with Joins but the query takes 2+ minutes to execute

What am I doing wrong??? I am sure there is a better approach to this!

Thanks,
--Lito

View 13 Replies View Related

Logic Problem

Oct 9, 2006

Hi,

This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.

The table:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)



The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".

I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'

The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?

View 3 Replies View Related

Logic Needed

Jan 4, 2007

Hi pundits,

I need a logic for solving a realtime issue.

I have series 2^0,2^1,2^2,.......2^n ( i.e 1,2,4,8,16.....2^n)

I want to write a function(algorithm) which returns all the terms used(added) to form the number

e.g fun(7) :-1,2,4
fun(5) :-1,4
fun(257):-1,256
fun(6):-2,4

Ur help is highly appreciated.

Thanks in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved