select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
from dbo.test1Users
When i do that i get this error
Incorrect syntax near the keyword 'from'.
I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors. The statement is:
SELECT @cmdLine = CASE @BackupType WHEN 1 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' END WHEN 2 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END WHEN 3 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END ELSE '' END
I need help nesting select statements. Here's what I'm trying to do:
select (select e.emp_name_lf as employee, e.emp_id from employee e, install_payroll_detail ipd where e.emp_id = ipd.emplno) e.emp_name_lf as username from employee e, install_payroll_master ipm where e.emp_id = ipm.entered_by
I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.
Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc
select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus, 'Result' = Case when Test.WebStatus = 'Rd' and FinalResult = 'true' then Case Case when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit end Case when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit end end when FinalResult = 'false' then Null else Test.WebStatus from Job inner join sample on Job.JobID = Sample.JobID inner join Test on Sample.SampleID = Test.SampleID left join Result on Test.TestID = Result.TestID
Is it possible to use nested case statements in the SQL for your dataset when you are using Reporting Services? I keep getting an error saying "Unable to parse expression" and my report won't run.
I know this is quite a complex statement, so at first I was worried that maybe I had brackets in the wrong places, but I've been through the code and made sure that every opening bracket has an equivalent closing bracket, and everything appears to be OK in that respect. So I'm thinking that maybe the structure of my nested case statements is incorrect? The inner case statement is necessary to calculate whether a transaction is due, overdue or not due. The outer case statement depends on the result of the inner case statement.
Basically, we only want the calculations following the "THEN" in the outer case statement to be executed if the result of the inner case statement is "not due" and Field2 is greater than zero. If either of those criteria aren't met, then it should go straight to the ELSE NULL END statement and stop.
Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.
Nesting them in the usual way doesn't seem to work as that only allows for one column.
My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)
My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.
Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.
FUNCTION GetFormula (@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200) AS BEGIN DECLARE @strExpression nvarchar(200)
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors: Accounting Computer Science Mathematics
and the user already had Mathematics in his/her profile I'd like it to display only:
Accounting Mathematics
Below is the layout of the tables, my attempt at the query, and then below that some example data.
Code Snippet
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Majors]( [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED ( [MajorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
SELECT MajorID FROM Majors majs WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0)) dbo.Majors MajorID Accounting Computer Science Mathematics
Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
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
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
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
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 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?
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
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.
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
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.
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
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
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.
-- 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.
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
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.
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
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
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.
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.
