Database

Re: SQL Guru challenge :) Or just a simple query question

16-Feb-05 3:07

Thanks for at least looking. I know there is a SQL Guru out there just waiting to help unlearned and ignorant programmers

I have two tables which are as indicated:
Table1
IDNO : RefID
01 : 100
02 : 200
03 : 300

Table2
IDNO : RefID : Code : SmallDateTimeStamp
01 : 100 : a : 1/1/2005 1:01:00
02 : 100 : b : 1/1/2005 1:02:00
03 : 100 : c : 1/2/2005 2:03:00
04 : 100 : d : 1/2/2005 3:00:00
05 : 100 : f : 1/3/2005 1:00:00
06 : 200 : a : 1/1/2005 4:55:00
07 : 200 : c : 1/1/2005 4:59:00
08 : 200 : f : 1/1/2005 5:02:00
09 : 300 : a : 1/2/2005 1:00:00
10 : 300 : c : 1/2/2005 2:00:00
11 : 300 : d : 1/2/2005 3:00:00
12 : 300 : f : 1/2/2005 4:00:00

My problem is that I need to select the IDNOs from Table1 where the same RefID in Table2 has a last record of “f” and a second to last record of “c”
EDIT: New task, I now assume all record in table1 have a last record of 'f' so all I need to get now is where table2 has a 2nd to last record of 'c'

Currently I'm getting all the rows from table1 and looping through them in code to do another query on table2. While this solution works it is slow and I believe there is another way to do this better and faster.

Thanks for any help you can offer.
-Richard

Michael Potter16-Feb-05 3:24

Michael Potter

16-Feb-05 3:24

Use correlated subqueries:

SELECT IDNO
FROM Table1 
WHERE
    (SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId) = 'f' AND
    (SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId AND 
     Code < (SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId)) = 'c'

Re: SQL Guru challenge :) Or just a simple query question

Richard Parsons16-Feb-05 6:50

16-Feb-05 6:50

Close but I didn't give you enough data I think. The codes will not be in order like I had them (e.g. a,b,c,d,...) they will be all random. My task is to make sure the last record in table2 is 'f' and the 2nd to last is 'c'.

I also added a post that contains a sql script to make table1 & table2 and fill them with test data.

Again I do apreciate you trying to help me with this
-Richard

Test data script

Richard Parsons16-Feb-05 6:51

EDIT: SQL Guru challenge :) Or just a simple query question

16-Feb-05 6:51

CREATE TABLE [Table2] (
[IDNO] [int] NULL ,
[RefID] [int] NULL ,
[Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dt] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [Table1] (
[IDNO] [int] NULL ,
[RefID] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO Table1 (IDNO, RefID) VALUES (1, 100)
INSERT INTO Table1 (IDNO, RefID) VALUES (2, 200)
INSERT INTO Table1 (IDNO, RefID) VALUES (3, 300)
INSERT INTO Table1 (IDNO, RefID) VALUES (4, 400)
INSERT INTO Table1 (IDNO, RefID) VALUES (5, 500)

INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (1, 100,'a', '1/1/2005 1:01:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (2, 100,'b', '1/1/2005 1:02:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (3, 100,'c', '1/2/2005 2:03:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (4, 100,'d', '1/2/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (5, 100,'f', '1/3/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (6, 200,'a', '1/1/2005 4:55:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (7, 200,'c', '1/1/2005 4:59:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (8, 200,'f', '1/1/2005 5:02:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (9, 300,'a', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (10,300,'c', '1/1/2005 2:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (11,300,'d', '1/1/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (12,300,'f', '1/1/2005 4:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (13,400,'a', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (14,400,'c', '1/1/2005 2:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (15,400,'e', '1/1/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (16,400,'ff', '1/1/2005 4:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (17,500,'c', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (18,500,'f', '1/1/2005 2:00:00 AM')

Richard Parsons16-Feb-05 7:27

Re: EDIT: SQL Guru challenge :) Or just a simple query question

16-Feb-05 7:27

The task changed. I don't care if the last record is 'f' anymore. I now only care if the second to last record is 'c'.

Why the change? Because as I started working on this problem I came to the conclusion that all the items in table1 will always have a last record of 'f'.

--Here is what I have so far.
--If you test against the test data it almost works.
--My only issue now is that I'm seeing 500 which is wrong
--because there are only 2 records not 3.
--See I'm trying Smile | :)

select * from table1
where refid in
(
select top 1 refid from
( select top 3 * from table2 where refid = table1.refid order by dt desc
) as tt where tt.code = 'c' order by dt
)

Thanks go out to Michael Potter who reminded me about correlated subqueries.
-Richard

Michael Potter16-Feb-05 8:49

Michael Potter

16-Feb-05 8:49

I think you are mis-stating your criteria. 'c' is the 2nd to a last record using RefId 500. It is not the 2nd to last in RefId 100, 300, 400. Your code dumps all RefIds. Here is some code that reports if 'c' is the 2nd to last record (by the DateTime order that you didn't specify in the first message).

SELECT *
FROM Table1 
WHERE  
    (SELECT COUNT(*)
     FROM Table2
     WHERE RefId = Table1.refId AND
           dt > (SELECT dt
                 FROM Table2
                 WHERE RefId = Table1.refId AND
                       Code = 'c')) = 1

If you are truely looking for the 3rd to last then just change the '1' to '2'. If you want to make sure that 'c' can not be the first than add that criteria also.

Re: EDIT: SQL Guru challenge :) Or just a simple query question

Richard Parsons16-Feb-05 11:34

Re: SQL Guru challenge :) Or just a simple query question

16-Feb-05 11:34

Absolutely brilliant!!! I knew there had to be a better way to do it. Thank you very much for your assistance. I’ll attempt to implement this solution tomorrow. Hopefully my page will load much quicker now.

Again thank you. And I’m sorry if my initial remarks where not exactly what I was attempting to get. D'Oh! | :doh:

It’s almost like I became a manager or something for a minute... ahhhh... I’m better now Smile | :)

-Richard

NassosReyzidis10-Mar-05 1:11

NassosReyzidis

10-Mar-05 1:11

i'm not an SQL guru but, you can create a View with this two tables and join the RefID of Table1 with the RefID of table2 this will give you (with the correct WHERE clause) what you want
hope this help you.

GanDad

prob in sp

amalatsliit15-Feb-05 16:46

15-Feb-05 16:46

i have created sp for insert and edit data in the table called
EPG_tblResourceMaster.

when i run my sp....
exec dbo.USP_AddEditResourceMaster '001','r002','veh','m001','s002','1000.0' it execute sucess fully.

but when i run the select * from dbo.EPG_tblResourceMaster before run the sp...and after again try to run my sp it gives error like....

Server: Msg 50000, Level 16, State 1, Procedure USP_AddEditResourceMaster, Line 76
Rowcount 988982068 inserting into Region

how can i solve this prob......

Thanks

Amal

Rob Graham16-Feb-05 15:59

Rob Graham

16-Feb-05 15:59

Without seeing the code for the sp, I wouldn't have a clue.

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

amalatsliit16-Feb-05 19:33

16-Feb-05 19:33

Actually nice though..... Big Grin | :-D

any way if u need my sp code here it is.but i guess thr is no prob in sp.any way it's upto u chk it and give me solution. D'Oh! | :doh:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*AMAL FERNANDO ON 7 FEB 2005 */

ALTER PROC [USP_AEDMasterCode]
(@szCompCode VARCHAR(5),
@szMasterCode VARCHAR(5),
@szDesc VARCHAR(20),
@szCategory VARCHAR(10),
@szCategoryID VARCHAR(2),
@opType VARCHAR(6),
@type VARCHAR(6) OUTPUT,
@errDesc VARCHAR(20) OUTPUT)
AS

DECLARE @@dtLastLogin DATETIME
DECLARE @error INT
DECLARE @rowcount INT
DECLARE @errorMsg NVARCHAR(512)
DECLARE @spName SYSNAME

SELECT @error = @@error,
@rowcount = @@rowcount,
@spName = Object_Name(@@ProcID)

SET NOCOUNT ON
BEGIN TRAN

IF @opType = 'DELALL'
BEGIN
DELETE FROM [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
SET @type = 'DELALL'
END
ELSE IF @opType = 'DEL'
BEGIN
DELETE FROM [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID
SET @type = 'DEL'
END
ELSE
IF EXISTS(SELECT * FROM EPG_tblMasterCodes
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID)
BEGIN
UPDATE [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
SET [MC_szCompCode] = @szCompCode,
[MC_szMasterCode] = @szMasterCode,
[MC_szDesc] = @szDesc,
[MC_szCategory] = @szCategory,
[MC_szCategoryID] = @szCategoryID
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID
SET @type = 'UPDATE'
END
ELSE
BEGIN
INSERT INTO EPG_tblMasterCodes
(MC_szCompCode,
MC_szMasterCode,
MC_szDesc,
MC_szCategory,
MC_szCategoryID)
VALUES(@szCompCode,
@szMasterCode,
@szDesc,
@szCategory,
@szCategoryID)
SET @type = 'SAVE'
END

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR('Error %d inserting into Region', 10, 1,@spName, @error,@errorMsg)
SET @errDesc = @error
RETURN(@error)
END
ELSE IF @rowcount <> 1
BEGIN
ROLLBACK TRAN
RAISERROR('Rowcount %d inserting into Region', 10, 1,@spName, @rowcount,@errorMsg)
WITH SETERROR
SET @errDesc = @@error
RETURN(@@error)
END
ELSE
BEGIN
COMMIT TRAN
RETURN(0)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks Confused | :confused:

Mike Dimmick17-Feb-05 1:31

Mike Dimmick

17-Feb-05 1:31

Your code sets a local variable @rowcount - different from @@ROWCOUNT - to whatever the last row count was when the procedure was called - i.e. how many rows were affected/returned by the previous statement executed on that connection.

If you need to check the row count or error code, you should use a SET statement just before you need to check it.

Stability. What an interesting concept. -- Chris Maunder

amalatsliit17-Feb-05 16:01

Casting error when passing a strongly typed dataset from a Webservice

17-Feb-05 16:01

Dear mike thanks for your reply.any way i 'm new to sql server 2000 and i want follow best practises.so can u give me guide sp sample which handle all the eroors and return values.

Thanks

dazzler215-Feb-05 16:41

dazzler2

15-Feb-05 16:41

The scenario is that i have a webservice returning a strongly typed dataset to a client app with ostensibly the same typed dataset. Unfortunately they are not recognised as being of the same type and get the error message "cannot convert type 'CAClient.WSCA.AdminStaff to CAClient.AdminStaff'. CAClient is my client. WSCA is the reference to my webservice.

Calling Data from Webservice on The client:
currentUserInformation.myAdminStaff=(AdminStaff) m_WsData.GetAdminStaff(myTicket,currentUserInformation.userGroupId);
webservice:
[WebMethod]
AdminStaff typedDataSet = new AdminStaff();
Param = new SqlParameter("@id",Id);
string [] tableList= {typedDataSet.Tables[0].TableName};

SqlHelper.FillDataset(dbConn, CommandType.StoredProcedure," Confused | :confused:

GetStaff",typedDataSet,tableList, Param );
return typedDataSet;

dazzler

Re: Casting error when passing a strongly typed dataset from a Webservice

dazzler215-Feb-05 20:47

dazzler2

15-Feb-05 20:47

Im replying to my own cry for help as i have managed to resolve it. I think this is a problem that only newbies like myself are likely to encounter. It stems entirely from a lack of understanding. What i had done was to create a Typed Dataset in a Webservice and then duplicate it entirely on my client. I then stupidly assumed that i could assign the typed dataset returned by a call to the webservice to the one created in my client. These are obviously recognised as two distinct types even though structurally they are the same.

The way to resolve this issue is to create a reference to the webservices typed dataset in the client and not to duplicate it in the client. (sensible really)

cheers

dazzler

dazzler

prob with sp return value

amalatsliit15-Feb-05 15:55

Re: prob with sp return value

15-Feb-05 15:55

Dear guy's i'm using Enterprise library 2005 for my asp.net project.in there i want read return value of my Stored procedure.my how can i edit my code..

Database db=DatabaseFactory.CreateDatabase();
string strsqlcmd="USP_AddEditResourceMaster";
DBCommandWrapper dbcmdwr=db.GetStoredProcCommandWrapper(strsqlcmd);
dbcmdwr.AddInParameter("@szCompCode",DbType.String, Resource.EPGRScompcode);
dbcmdwr.AddInParameter("@szResourceID",DbType.String,
Resource.EPGRSresID);
dbcmdwr.AddInParameter("@szResName",DbType.String, Resource.EPGRSresName);
dbcmdwr.AddInParameter("@szResType",DbType.String,
Resource.EPGRSresType);
dbcmdwr.AddInParameter("@szResMesureType",DbType.String, Resource.EPGRSresMType);
dbcmdwr.AddInParameter("@dResMinCost",DbType.Double,
Resource.EPGRSMincost);
dbcmdwr.AddInParameter("@dResMaxCost",DbType.Double,Resource.EPGRSMaxcost)db.ExecuteNonQuery(dbcmdwr);

Thanks

Scott Serl16-Feb-05 8:52

Scott Serl

16-Feb-05 8:52

Use dbcmdwr.AddParameter.
The 4th parameter is 'direction', and you use ParameterDirection.ReturnValue for the value. After the stored procedure is run, just get the value like you would any other output parameter:
dbcmdwr.GetParameterValue("@MyRetValParameter")

I don't use return values in my stored procedures because not all db support return values, so I code my stored procedures to use output parameters for all values returned. I think this is mentioned in the documentation for the enterise library.

Re: prob with sp return value

amalatsliit16-Feb-05 15:17