Nested Cursor

Jun 12, 2004

I think I am getting an endless loop here... anyone know how to fix it?





***********************





CREATE PROCEDURE TrigSendPreNewIMAlertP2


@REID int





AS





Declare @RRID int


Declare @ITID int


Declare @FS2 int


Declare @FS1 int








Declare crReqRec cursor for


select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null


open crReqRec


fetch next from crReqRec


into


@RRID











Declare crImpGrp cursor for


select ITID from RequestRecords where RRID = @RRID


open crImpGrp


fetch next from crImgGrp


into


@ITID





while @@fetch_status = 0


select @FS1 = @@Fetch_Status





EXEC TrigSendNewIMAlertP2 @ITID





FETCH NEXT FROM crImpGrp


into


@ITID








close crImpGrp


deallocate crImpGrp











while @@Fetch_Status = 0


select @FS2 = @@Fetch_Status





FETCH NEXT FROM crReqRec


into


@RRID











close crReqRec


deallocate crReqRec


GO

View 1 Replies


ADVERTISEMENT

Nested Cursor

Oct 21, 1999

I have a situation where I have two cursors: outer_cursor and inner_cursor. The inner_cursor is supposed to execute based on a value passed on from outer_cursor. It is not running as desired. At runtime, it complians that the inner_cursor is not open.

Thanks

View 1 Replies View Related

Cursor, Conditional Split Task, Nested Joins In SSIS

Aug 27, 2007



Hello

Can anybody help me out in
1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.

my few other questions are:
1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data)
2) I have a resultsets. I want to split the data according to data in a col.
Say for instance:
Col1 Col2
A 1
A 2
B 3
C 4
C 5
i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS


View 6 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

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

Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

Jan 3, 2007

Can you give a whole SQL statement an alias so you can use it later?

Eg.

SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown

SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)

Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.

So basically, I just want to alias a qhole SQL statement

Any help would be greatly appreciated - George

View 14 Replies View Related

Nested

Jul 20, 2005

Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam

View 3 Replies View Related

NESTED IF

Oct 15, 2007

I am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.

Can somebody advise me on this and also let me know the best practices and alternative to this if any.

IF (@CCTable = 'Claiminassoc')
BEGIN
IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID'
AND DWField = 'CatastropheDesignationFlag'
END
END


ELSE IF (@CCTable = 'EmploymentData')
BEGIN
IF (@VALUE ='TRUE')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END

ELSE IF (@VALUE ='FALSE')
BEGIN
UPDATE dbo.Table SET ColName = 'N'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
END

View 4 Replies View Related

Help With Nested CTE

Apr 2, 2008



Hi gang,

I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!

I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.

The CTE I am using for the day population is:



Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)






What I wanted to do was something like:


Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)






inserted just after



Code Snippet
FROM dates





in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.

Can anyone show me how to do this?

Thanks in advance for ANY insight/input!

Cheers,

Chris

View 5 Replies View Related

Nested IIF

Feb 14, 2008

Greetings -

Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?

Tks & B/R

View 1 Replies View Related

SQL Nested IF-ELSE

Feb 2, 2008

Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.

SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email
IF @@ROWCOUNT = 0 BEGIN


INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo)
VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo)
END
ELSE BEGIN

RETURN -2

END
ELSE BEGIN
RETURN -1

END
END

Thanks!

View 3 Replies View Related

Sql Server Nested Set, How?

Nov 8, 2006

Hi, I'm not sure if this is  a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the  whole tree of its decendents. (I don't need its parent)so say I have data liek soid     parentid1      null2      13      2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1.  when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.  

View 1 Replies View Related

Nested Views

Oct 16, 2007

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

View 3 Replies View Related

Nested Select

Apr 7, 2008

What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error  that  inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office 

View 2 Replies View Related

How Do I Use @@Identity In A Nested SP????

Nov 12, 2003

Hi All,

Im inserting some data into a table and grabbing the new UserID with this statement

SELECT @UserID = @@IDENTITY


I would like to use the @UserID to Execute another SP within the same proc..
..something like this

Exec AnotherSP(@UserId)

But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.

Thanks in advance
AMRITZ

View 2 Replies View Related

Nested SQL Problem

Apr 30, 2005

Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server.  My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data.  I believe the way to do this in SQL server is to use a nested query.  I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code> 
The results that the above SQL turns out though are not reliable.  For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago.  Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike

View 3 Replies View Related

Need Help With An SQL Nested Query

Sep 13, 2005

Hi,Please can somone help me with a nested SQL query.  I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal.  How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.

View 2 Replies View Related

Nested Cursors - Me Am Very Bad :(

Sep 26, 2005

Hi all,

I have a temp guy who is doing some work for us (seriously, I never wrote the query).

Now this is the scenario (hold tight). So we have a stored procedure as follows...

ALTER PROCEDURE createSegmentPoints AS

DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER

-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
    -- insert into table the segId, from lat, from lon and returned segment id from function.
    INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
   
    FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END

CLOSE c1
DEALLOCATE c1

As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...

ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
    -- function to find the closed segment point with address point 109 to the segment specified in procedure.
    DECLARE @tempDistance FLOAT(4)
    SET @tempDistance = 1000000
    DECLARE @id, @seg, lat, lon INTEGER
    DECLARE @distance, @xd, @yd FLOAT
    DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
    OPEN c1
    FETCH NEXT FROM c1 INTO @lat, @lon, @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- calucations to get distance.
        SET @xd = (@lat-@x)
        SET @yd = (@lon-@y)
        SET @distance = SQRT((@xd*@xd) + (@yd*@yd))

        -- test if you have shortest distance.
        IF (@distance < @tempDistance)
        BEGIN
            SET @tempDistance = @distance
            SET @seg = @id
        END
        FETCH NEXT FROM c1 INTO @lat,@lon, @id
    END
    CLOSE c1;
    DEALLOCATE c1;
RETURN @seg
END

(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.

So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.

Any ideas would be of great help.

Thanks

Tryst

View 2 Replies View Related

Nested Roles

Mar 20, 2002

Guys,

Has anybody out there successfully nested roles?

SQL Server seems to let you do it, but then no permissions are carried up the hierarchy.

Regards,
ChrisH

View 2 Replies View Related

Nested Transactions

Oct 22, 1999

Hello;

I have a question why does not the following nested transaction work?

begin tran
insert into t1 values('A')
begin tran
insert into t2 values('1')
commit
insert into t3 values('B')
begin tran
insert into t2 values('2')
commit
rollback

The rollback is rolling back everything.

Thanks

Nathan

View 1 Replies View Related

NESTED QUERY

Oct 4, 1999

Hi,

I want to write one query which will select multiple distinct records from one table
For e:g
Lets say in a table i have 3 fields name,tel_no,sex
Now i want to list all the records which are distinct in each of these fields
like distinct name,distinct address

IS IT POSSIBLE and if yes HOW

Thanks

Ashish

View 1 Replies View Related

Help With Nested Loops

Jun 27, 2007

Hi. newbie here.
Also new to coding in SQL.

Struggling with how to implement the following psuedo-code in SQL server 2000.
** Can you use more than one CURSOR variable?
If yes, when use FETCH_STATUS is it for cur1 or cur2 ??

Sample data is at the bottom.
Thanks for ANY suggestions !!

** Assume TABLE 1 is sorted by Record_Type, Order_no, Order_line_no

************************************************** ***
dim @rectyp
dim @ord#
dim @lin#

Fetch (?) 1st record in TABLE1
While Still Records in TABLE1
Set sub_line# = 0
set @rectyp = Record_Type,
set @ord# = Order_no,
set @lin# = Order_line_no

while @rectyp = Record_Type and
@ord# = Order_no and
@lin# = Order_line_no
Set sub_line# = sub_line# + 1
update TABLE1 set line_ctr = sub_line#
get next record
end inner WHILE

end outer WHILE

************************************************** ****************************
Sample data :
Data as it currently exists:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............0
OP .....................458001..............5 .................. 0
OP..................... 458001..............5..................0
OP .....................458001..............5........ ..........0
OP.....................458191..............1 ..................0
OP.....................458191..............1 .................. 0
OP..................... 458308..............73..................0
OP .....................458308..............73....... ........... 0
OP.....................458308..............73..... .............0
OP.....................458308..............73..... .............0


Want data to look like this after executing code:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............1
OP .....................458001..............5 .................. 2
OP..................... 458001..............5..................3
OP .....................458001..............5........ ..........4
OP.....................458191..............1 ..................1
OP.....................458191..............1 .................. 2
OP..................... 458308..............73..................1
OP .....................458308..............73....... ........... 2
OP.....................458308..............73..... .............3
OP.....................458308..............73..... .............4


************************************************** *********************

View 2 Replies View Related

Nested Case?

Jan 10, 2005

What's wrong with this...


Code:

SELECT case when tab1.col2=tab1.col3 and tab1.col3!=0
then (SELECT tab3.col3 FROM tab3)
else (case when tab5.col2=tab5.col6
then (SELECT tab7.col1 FROM tab7)
else (case when tab1.col2=tab1.col3 then tab1.col4+7 end)
end as value
From tab5, tab1
WHERE tab1.col1=tab5.col1


I get the error as-
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.
please help me.

View 1 Replies View Related

Nested Queries

Mar 15, 2005

I have this table record:

PHP Code:




 ID |    NUMBER    |   DSTART   |    DEND    |     ADDRESS     |
----------------------------------------------------------------
 1  | 9524123      | 12 Dec 95  | 24 Dec 95  | London          |
 2  | 9524123      | 06 Jan 96  | 15 Jan 96  | Paris           |
 3  | 084521       | 12 Mar 96  | 15 May 96  | New York        |
 4  | 084521       | 22 Aug 96  | 25 Aug 96  | Sidney          |
 --------------------------------------------------------------- 





Now, I need to build a query to show only the latest DTSTART date
for each NUMBER. The result would be something like this:

PHP Code:




 ID |    NUMBER    |   DSTART   |    DEND    |     ADDRESS     |
----------------------------------------------------------------
 2  | 9524123      | 06 Jan 96  | 15 Jan 96  | Paris           |
 4  | 084521       | 22 Aug 96  | 25 Aug 96  | Sidney          |
 --------------------------------------------------------------- 





Can you guys help me to build the queries? Thanks in advance.

View 2 Replies View Related

Nested Cursors

Jun 3, 2004

Morning everyone,

I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:

CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strViewVARCHAR(100)
DECLARE @strCommandVARCHAR(250)

DECLARE crsViews CURSOR FOR

SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'

OPEN crsViews
FETCH NEXT FROM crsViews INTO @strView
WHILE @@FETCH_STATUS = 0 BEGIN

DECLARE crsColumns CURSOR FOR

SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @strView

OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @strColumn
WHILE @@FETCH_STATUS = 0 BEGIN

PRINT @strView + ':' + @strColumn
FETCH NEXT FROM crsColumns INTO @strColumn
END

CLOSE crsColumns
DEALLOCATE crsColumns

FETCH NEXT FROM crsViews INTO @strView
END

CLOSE crsViews
DEALLOCATE crsViews

Thanks for looking, any ideas??

View 7 Replies View Related

Nested Or Subquery Help

Oct 20, 2004

i am new to SQL Server DB.
i have a typical query problem.
master table contains Data in following format
id stype sid svalue
1 status 1 approved
2 status 2 pending
3 request 1 draft
4 request 2 waiting
etc

in my query i need to substitute the svalue in place of sid
e.g.
select requestid ,bstatus, brequest from request ;
should give
---------------------------------
R1122 approved draft
------------------------------
instead of
R1122 1 1
*****************
link field is column name (quite unusual )
like for status column , it is bstatus column name and it is stored in master table as status .


i need to do the following in 1 query only.
kindly suggest some nested query to do the same
kindly help
Smita

View 1 Replies View Related

Nested Recordsets

Nov 19, 2004

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.

View 8 Replies View Related

Can SELECT Be Nested As

Nov 24, 2004

I know you can do something like:

SELECT
ColumnA,

(SELECT Columnb FROM Table Where...),

ColumnC
...

Can you select multiple columns, and how if possible, such as:

SELECT
ColumnA,

(SELECT ColumnB, ColumnC, ColumnD FROM Table Where...),

ColumnE
...

If this is possible, how would the columns be aliased?

Thanks in advance.

View 8 Replies View Related

Nested While Loop

Dec 10, 2004

Hi there. I want to avoid a cursur using a quite basing nested while loop. The problem is, that the outer index-variable (i) won't increment at all while the inner loop works perfectly.

This one should be quite easy to solve I guess, I'd be very happy if someone could give me a hint what I should try, though because I don't know what to try. The manual didn't help me much either, using CONTINUE and BREAKs didn't solve this problem for me.

My code:
DECLARE @i INTEGER
DECLARE @j INTEGER
SET @i = 1
SET @j = 0

WHILE(@i<= 10) BEGIN
WHILE(@j <= 100) BEGIN
SELECT @i, @j, COUNT(*) as anz FROM mytable WHERE dim1 = @i AND dim2 = @j
SET @j = @j + 1
END
SET @i = @i + 1
END

Thanks a lot for your help :)
Bernhard

corrected typo...

View 4 Replies View Related







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