.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

T-SQL - I guess have to use Cursor.. Need some help ASAP.

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

Hi All,

 

I'm stuck in SQL.

 

I've two tables. I've to concatenate serial number based on the items. I've achieved this functionality according to the rule that one item will not repeat in the invoice.

 

but somehow, now it appears that one item can be in invoice multiple times. now my sql repeats serial number multiple times.

 

the structure of the table is

 

PK     Invoice no        Itemno     Serial_no

1          22                  xyz1          123

2          22                  xyz1          124

3          22                  xyz1          125

4          22          &


View Complete Post


More Related Resource Links

Cursor Functions

  
A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

DECLARE CURSOR in Transact-SQL

  
Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Win32 Resources: Using C++ to Programmatically Retrieve a Global Cursor's Shape and ID

  

Getting global cursor information is useful when developing software that drives or gathers information about other UI-based applications, including information about a remote machine. This article describes a way to programmatically identify the current cursor's ID and bitmap at any point in time. The first technique described is based on polling for information and shows how to get the handle of the current global cursor. This handle will then allow you to get information about the cursor. You can also monitor WinEvents for changes to the global cursor.

Dmitri Klementiev

MSDN Magazine October 2001


Need Help with Radiobuttonlist Style ASAP

  

How could is style the button or sort of icon into the gif image i wan

Example here is the CSS code
.radio {
width: 19px;
height: 25px;
padding: 0 5px 0 0;
background: url(checkbox.gif) no-repeat;
display: block;
clear: left;
float: left;
}

.radio {
background: url(radio.gif) no-repeat;
}

.select {
position: absolute;
width: 158px;
height: 21px;
padding: 0 24px 0 8px;
color: #fff;
font: 12px/21px arial,sans-serif;
background: url(select.gif) no-repeat;
overflow: hidden;
}

How could i call it in ASP to be used in
<asp:RadioButtonList ID="RadioButtonList3" style="position: relative" 
runat="server" AutoPostBack="true" 
OnSelectedIndexChanged="RadioButtonList3_SelectedIndexChanged" Height="26px" 
Width="208px" " BorderStyle="None" 
Fo

DATABASE/ADO ERROR need help asap please!

  
  We are running SQL server 2005 under WS 2003.  This error is showing up in our log every time there is a select.  I  have looked everywhere for a solution and cannot find one that works.       DATABASE/ADO ERROR  Error Number: -2147467259 Error Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). Error Source: Microsoft OLE DB Provider for ODBC Drivers  

looking for CURSOR replacement

  
I got below solution from christa with cursor...could some one help me out without cursor solution.. here we are devloping WHERE clause... CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X /*CURSOR*/ declare @n varchar(50), @v varchar(50), @d varchar(50) declare @sql varchar(max) set @sql = ' where ' declare mycur cursor for select col_N, col_V, col_D from X order by uid open mycur fetch next from mycur into @n,@v,@d while @@fetch_status = 0 begin set @sql = @sql + @n + ' = ' if @d like '%char%' set @sql = @sql + '''' + @v + '''' else set @sql = @sql + @v set @sql = @sql + ' and ' fetch next from mycur into @n,@v,@d end close mycur deallocate mycur set @sql = LEFT(@sql, len(@sql) - 4) print @sql --WHERE S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'

is there alternate solution for CURSOR?

  
/* CREATE TABLE X (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES (3, 'S_NAME', 'VARCHAR(50)') INSERT INTO X VALUES (5, 'S_ROLL', 'DECIMAL(15, 0)') INSERT INTO X VALUES (8, 'S_MARKS', 'INT') INSERT INTO X VALUES (9, 'S_SCHOOL', 'VARCHAR(100)') SELECT * FROM X CREATE TABLE Y (UID INT IDENTITY(1,1), S_NAME VARCHAR(50), S_ROLL DECIMAL(15, 0), S_MARKS INT, S_SCHOOL VARCHAR(100)) INSERT INTO Y VALUES ('AYAZ', 10, 99, 'ZINQ') SELECT * FROM Y CREATE TABLE Z (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50), COL_V VARCHAR(50)) */ DECLARE @vSQL NVARCHAR(MAX) DECLARE @UID INT DECLARE @COL_N VARCHAR(50) DECLARE @COL_D VARCHAR(50) DECLARE cur CURSOR FOR SELECT UID, COL_N, COL_D FROM X ORDER BY UID OPEN cur FETCH cur INTO @UID, @COL_N, @COL_D WHILE(@@FETCH_STATUS=0) BEGIN SET @vSQL = 'INSERT INTO Z (UID, COL_N, COL_D, COL_V) SELECT ' + CAST(@UID AS VARCHAR) + ',' + '''' + @COL_N + '''' + ',' + '''' + @COL_D + '''' + ',' + @COL_N + ' FROM Y ' EXEC SP_EXECUTESQL @vSQL PRINT @vSQL FETCH cur INTO @UID, @COL_N, @COL_D END CLOSE cur DEALLOCATE cur SELECT * FROM Z

Hide HoverMenuExtender when cursor is in an associated TextBox

  
Hi.I use HoverMenuExtenders on TextBoxes to display some info in certain cases (typically; "you did something wrong here" - messages). Thing is, I want the HoverMenuExtender to disappear when the cursor is in its associated TextBox (and the mouse is outside its borders). Right now the HoverMenuExtender is still visible when the user have placed the cursor in a TextBox to write even though the mouse pointer is outside the TextBox.Is this possible? Thanks for any input! 

Could not complete cursor operation because the table schema changed

  
Microsoft SQL Server  2000 - 8.00.2039 Got this error: Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000 Is this a known issue?  I suspect the application logic may cause this error. Please advise. Thanks a lot!  

fetch data from cursor(what wrong?)

  
hi there i have a SP that send mailbut in the cursor does not appears no one record, i written PRINT'-------' , and it seems no recordwhere is wrong here?the query returns 200 records here, but not in cursoralter PROCEDURE [dbo].MAIL (@date date) AS BEGIN declare @MonthlyTimeInterval int declare @WorkingDaysBeforeOpen int declare @WorkingDaysBeforeClose int ----valorizzazione parametri MAIL--------- select @MonthlyTimeInterval=MonthlyTimeInterval, @WorkingDaysBeforeOpen=WorkingDaysBeforeOpen, @WorkingDaysBeforeClose=WorkingDaysBeforeClose FROM ParameterData --------market in apertura------------- DECLARE @getMktOpen CURSOR declare @PeriodEND date declare @PeriodStart date declare @Market Varchar(300) declare @Status bit declare @MarketID int DECLARE @MarketPeriodID INT SET @getMktOpen = CURSOR FOR SELECT MAX(Period.[Desc]) AS PeriodEND, MIN(Period.[Desc]) AS PeriodStart, Market.[Desc] AS Market, MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID FROM MarketPeriod INNER JOIN Period ON MarketPeriod.PeriodID = Period.PeriodID INNER JOIN Market ON MarketPeriod.MarketID = Market.MarketID GROUP BY Market.[Desc], MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID -- HAVING (MarketPeriod.Status = 0) -- AND (MIN(Period.[Desc]) = CONVERT(CHAR(11),@date,111) ) OPE

Changing Mouse Cursor into Crossed Circle in ListBox-ScrollBar/ScrollViewer(on Drag and Drop).

  
Hi All,   I have a ListBox with 10 items, the automatic Scrollbar/ScrollViewer is appearing. If i'm dragging an item from the listbox and moving the mouse cursor on top scrollbar/scrollviewer the cusor have to change into CROSSED CIRCLE(means its not a location to drop). Can any one help to do the same, or please let me know if more info needed.   Thanks, Karthikeyan Manickam.

Advanced T-SQL - want to get rid of a large Cursor

  
Hi all, I am importing & processing large amounts of data (100MB+ xml files), and the biggest bottleneck is a step involving one huge cursor.  In this step, I have say 300,000 rows, that I need to split space-delimited values into their own individual rows (10 Million rows total after the 300k are fn_split). Presently I am loading them all into a Cursor, fetch next into a group of local vars, then combine fn_split with insert.  It takes 10-15 minutes to do. Meanwhile, my server has 24 processors (cores + hyperthreading), and only ONE is in use due to this cursor.  Talk about a waste of resources! I have tried many alternatives - @id=1 WHILE @id<max BEGIN select @values=cols fn_split Insert @id=@id+1 END, and they all seem to take 2-3 times as long to execute as the Cursor. (the While loop also uses less processing %age than the cursor!) There has to be a way to optimize this thing... I would love to find a way to engage all 24 processors at 40-60% to crank through this thing, it'd be done in 2-3 minutes.  Anybody have ideas? Thanks- Jason

Oracle stored proc optionally returning a ref cursor.

  
Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

Convert the Sql query to LINQ .. can anyone please help me I need it ASAP as no time to learn and im

  
 SELECT DISTINCT HP.PartNumber, HP.MIRevision, HPEXData.InstructionImage, EXAttr.PlacementImage from dbo.HardwareParts HP   INNER JOIN dbo.HardwarePartEXData HPEXData     ON HP.HardwarePartID = HPEXData.HardwarePartID   LEFT JOIN dbo.EXMIAttributes EXAttr    ON EXAttr.EXTypeID = HPEXData.EXTypeID   WHERE    HP.PartNumber = @PartNumber     AND REPLACE(SUBSTRING(HP.MIRevision, 1, 13),'.','') >= REPLACE(SUBSTRING(ISNULL(@PartRevision,''), 1, 13),'.','')    AND ISNULL(HP.ProjectID,'') = ISNULL(@SINumber,'')    AND (@ChassisIdentifier IS NULL OR HPEXData.EXMI_Type NOT IN ('Add In Card','Storage') OR EXAttr.ChassisIdentifier = @ChassisIdentifier)   END;anji

Oracle ref cursor or temp table?

  
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Scope Statement Errors - Please Help ASAP!

  
Hello, I have  script that validates a criteria and then performs calculation based on the criteria. In general this is what is does, If account within the range of 100000 and 399999 then YTD AMT + Beg Balance else YTD AMT  Here is the script: CREATE MEMBER CURRENTCUBE.[Measures].[test]  AS NULL, VISIBLE = 1  ; Scope([Measures].[Actual Amount]); Scope (  EXCEPT  ( [COA AHC].[Account].MEMBERS, {[COA AHC].[Account].[100000]:[COA AHC].[Account].[399999]})) ;   This = Sum(YTD([Period].[YP - Hierarchy]), [Measures].[Actual Amount]); End Scope ; END SCOPE; Scope   ( {[COA AHC].[Account].[100000]:[COA AHC].[Account].[399999]},[Measures].[Actual Amount]) ;   This = Sum(YTD([Period].[YP - Hierarchy]), [Measures].[Actual Amount]) + [Measures].[Actual Beg Bal] ; End Scope ; It throws the following errors: 1) Error 1 MdxScript(FINANCE) (14, 1) An arbitrary shape of the sets is not allowed in the current context.   2) Error 2 MdxScript(FINANCE) (19, 1) The END SCOPE statement does not match the opening SCOPE statement.  3) Error 3 The END SCOPE statement does not match the opening SCOPE statement.  Can anyone please help me ASAP to resolve this? I am not sure what am doing wrong. Thank you all in advance. Thanks, KJ

SQL 2005 Query Assistance needed ASAP

  
I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 ($225.00 charge minus $65.00 adjustment). SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], 0.0 AS [Debit], SUM(pvp.totalfee) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.Compa
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend