Procedure select help [REVISED]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Procedure select help [REVISED]

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-22 : 10:50:24

Hey Gang,
here's another little problem, easy for some of you I'm sure. I have this procedure:

CREATE PROCEDURE WARR_HIST_MOVE
 AS
--DECLARE OUR VARIABLES
DECLARE
@RTKT T_DOC_NO,
@ORDATE T_DAT,
@ROPVEND T_VEND_NO,
@PVEND T_VEND_NO,
@ROPAMT T_MONEY,
@PAMT T_MONEY,
@PCREDIT T_FLG,
@PDEN T_FLG,
@PINVCHK T_DOC_NO,
@PRCVDATE T_DAT,
@ROLVEND T_VEND_NO,
@LVEND T_VEND_NO,
@ROLAMT T_MONEY,
@LAMT T_MONEY,
@LCREDIT T_FLG,
@LDEN T_FLG,
@LINVCHK T_DOC_NO,
@LRCVDATE T_DAT,
@POST T_BOOL,
@ROPVEND2 T_VEND_NO,
@PVEND2 T_VEND_NO,
@ROPAMT2 T_MONEY,
@PAMT2 T_MONEY,
@PCREDIT2 T_FLG,
@PDEN2 T_FLG,
@PINVCHK2 T_DOC_NO,
@PRCVDATE2 T_DAT

--CREATE A LOOP TABLE TO WORK WITH
DECLARE @loop TABLE(
loop_id INT IDENTITY(1,1) PRIMARY KEY,
TKT_NO VARCHAR(15),
ORIG_RQST_DATE DATETIME,
ORIG_PARTS_VEND VARCHAR(15),
ACT_PARTS_VEND VARCHAR(15),
ORIG_PARTS_AMT DECIMAL(15,2),
ACT_PARTS_AMT DECIMAL(15,2),
PARTS_ARE_CREDIT VARCHAR(1),
PARTS_CLAIM_DENIED VARCHAR(1),
PARTS_INV_CHK_NO VARCHAR(55),
DATE_PARTS_RCVD DATETIME,
ORIG_LABOR_VEND VARCHAR(15),
ACT_LABOR_VEND VARCHAR(15),
ORIG_LABOR_AMT DECIMAL(15,2),
ACT_LABOR_AMT DECIMAL(15,2),
LABOR_IS_CREDIT VARCHAR(1),
LABOR_CLAIM_DENIED VARCHAR(1),
LABOR_INV_CHK_NO VARCHAR(55),
DATE_LABOR_RCVD DATETIME,
ORIG_PARTS_VEND2 VARCHAR(15),
ACT_PARTS_VEND2 VARCHAR(15),
ORIG_PARTS_AMT2 DECIMAL(15,2),
ACT_PARTS_AMT2 DECIMAL(15,2),
PARTS_ARE_CREDIT2 VARCHAR(1),
PARTS_CLAIM_DENIED2 VARCHAR(1),
PARTS_INV_CHK_NO2 VARCHAR(55),
DATE_PARTS_RCVD2 DATETIME)
INSERT @loop(
TKT_NO,ORIG_RQST_DATE,ORIG_PARTS_VEND,ACT_PARTS_VEND,ORIG_PARTS_AMT,ACT_PARTS_AMT,
PARTS_ARE_CREDIT,PARTS_CLAIM_DENIED,PARTS_INV_CHK_NO,DATE_PARTS_RCVD,ORIG_LABOR_VEND,
ACT_LABOR_VEND,ORIG_LABOR_AMT,ACT_LABOR_AMT,LABOR_IS_CREDIT,LABOR_CLAIM_DENIED,
LABOR_INV_CHK_NO,DATE_LABOR_RCVD,ORIG_PARTS_VEND2,ACT_PARTS_VEND2,ORIG_PARTS_AMT2,ACT_PARTS_AMT2,
PARTS_ARE_CREDIT2,PARTS_CLAIM_DENIED2,PARTS_INV_CHK_NO2,DATE_PARTS_RCVD2)

--SELECT OUR RECORDS TO WORK WITH IN LOOP TABLE
SELECT
WARR_RQST_DATA.TKT_NO,ORIG_RQST_DATE,ORIG_PARTS_VEND, ACT_PARTS_VEND,ORIG_PARTS_AMT,ACT_PARTS_AMT,
PARTS_ARE_CREDIT,PARTS_CLAIM_DENIED,PARTS_INV_CHK_NO,DATE_PARTS_RCVD,ORIG_LABOR_VEND,
ACT_LABOR_VEND,ORIG_LABOR_AMT,ACT_LABOR_AMT,LABOR_IS_CREDIT,LABOR_CLAIM_DENIED,LABOR_INV_CHK_NO,
DATE_LABOR_RCVD,ORIG_PARTS_VEND2, ACT_PARTS_VEND2,ORIG_PARTS_AMT2,ACT_PARTS_AMT2,
PARTS_ARE_CREDIT2,PARTS_CLAIM_DENIED2,PARTS_INV_CHK_NO2,DATE_PARTS_RCVD2
FROM WARR_RQST_DATA, WARR_PARTS_RCVD, WARR_LABOR_RCVD
WHERE WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
AND WARR_RQST_DATA.TKT_NO=WARR_LABOR_RCVD.TKT_NO
AND WARR_RQST_DATA.PARTS_POSTED ='Y'
AND WARR_RQST_DATA.LABOR_POSTED='Y'
AND WARR_RQST_DATA.PARTS_POSTED2='Y'
--CREATE A LOOP FOR THE ABOVE TABLE
DECLARE
@counter INT,
@max INT
SELECT
@counter = 1,
@max = (SELECT MAX(loop_id) FROM @loop)
WHILE @counter <=@max
--START DOING OUR WORK
BEGIN
SELECT
@RTKT=L.TKT_NO,
@ORDATE =L.ORIG_RQST_DATE,
@ROPVEND=L.ORIG_PARTS_VEND,
@PVEND=L.ACT_PARTS_VEND,
@ROPAMT=L.ORIG_PARTS_AMT,
@PAMT=L.ACT_PARTS_AMT,
@PCREDIT =L.PARTS_ARE_CREDIT,
@PDEN=L.PARTS_CLAIM_DENIED,
@PINVCHK=L.PARTS_INV_CHK_NO,
@PRCVDATE=L.DATE_PARTS_RCVD,
@ROLVEND=L.ORIG_LABOR_VEND,
@LVEND=L.ACT_LABOR_VEND,
@ROLAMT =L.ORIG_LABOR_AMT,
@LAMT=L.ACT_LABOR_AMT,
@LCREDIT=L.LABOR_IS_CREDIT,
@LDEN=L.LABOR_CLAIM_DENIED,
@LINVCHK =L.LABOR_INV_CHK_NO,
@LRCVDATE=L.DATE_LABOR_RCVD,
@ROPVEND2=L.ORIG_PARTS_VEND2,
@PVEND2=L.ACT_PARTS_VEND2,
@ROPAMT2=L.ORIG_PARTS_AMT2,
@PAMT2=L.ACT_PARTS_AMT2,
@PCREDIT2 =L.PARTS_ARE_CREDIT2,
@PDEN2=L.PARTS_CLAIM_DENIED2,
@PINVCHK2=L.PARTS_INV_CHK_NO2,
@PRCVDATE2=L.DATE_PARTS_RCVD2
FROM
@loop L
WHERE L.loop_id = @counter

--UPDATE THE HISTORY TABLE WITH OUR POSTED RECORDS
INSERT INTO
WARR_HIST
(TKT_NO,ORIG_RQST_DATE,ORIG_PARTS_VEND, ACT_PARTS_VEND,ORIG_PARTS_AMT,ACT_PARTS_AMT,
PARTS_ARE_CREDIT,PARTS_CLAIM_DENIED,PARTS_INV_CHK_NO,DATE_PARTS_RCVD,ORIG_LABOR_VEND,
ACT_LABOR_VEND,ORIG_LABOR_AMT,ACT_LABOR_AMT,LABOR_IS_CREDIT,LABOR_CLAIM_DENIED,LABOR_INV_CHK_NO,
DATE_LABOR_RCVD,ORIG_PARTS_VEND2, ACT_PARTS_VEND2,ORIG_PARTS_AMT2,ACT_PARTS_AMT2,
PARTS_ARE_CREDIT2,PARTS_CLAIM_DENIED2,PARTS_INV_CHK_NO2,DATE_PARTS_RCVD2)
VALUES
(@RTKT,@ORDATE,@ROPVEND,@PVEND,@ROPAMT,@PAMT,@PCREDIT,@PDEN,@PINVCHK,@PRCVDATE,
@ROLVEND,@LVEND,@ROLAMT,@LAMT,@LCREDIT,@LDEN,@LINVCHK,@LRCVDATE,@ROPVEND2,@PVEND2,
@ROPAMT2,@PAMT2,@PCREDIT2,@PDEN2,@PINVCHK2,@PRCVDATE2)
--DELETE THE POSTED PARTS RECORDS FROM THE CURRENT TABLES
DELETE FROM
WARR_PARTS_RCVD
WHERE TKT_NO=@RTKT
--DELETE THE POSTED LABOR RECORDS FROM THE CURRENT TABLES
DELETE FROM
WARR_LABOR_RCVD
WHERE TKT_NO=@RTKT
--DELETE THE POSTED REQUEST RECORDS FROM THE CURRENT TABLES
DELETE FROM
WARR_RQST_DATA
WHERE TKT_NO=@RTKT
SELECT @counter = @counter + 1
END
GO

The problem is in the WHERE clause. When we create a warranty request, it may be for just parts(1 or 2 vendors, hence PARTS_RECVD and PARTS_RECVD2), or just labor, or a combination of them both. The present setup will only select the entries that have PARTS, PARTS2, and LABOR entries. How can I set this up so that it:
A) checks to see what the request is for (parts,labor, both)
B) select the entries that have been recieved, and
C) don't select the ones that still hav a non-posted entry?
Any ideas?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...
1/26/05
Turns out this proc is executed with 3 other procs. This whole thing needs a rewrite, I will post it if I need help.
Thanks!
Andy

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-22 : 18:46:09

Here is a little more info that may help. When a request is entered into WARR_RQST_DATA, if there is no request for something the appropriate orig_XXX_vend will be NULL (ie.: if a request is entered for labor and parts1 but no parts2 then orig_labor_vend will not be null and orig_parts_vend will not be null but orig_parts_vend2 WILL be null). Could this be used for filtering? Or perhaps adding other columns to flag what data a request actually is waiting for?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-22 : 19:27:06

did you know you can say

insert into DestinationTable (a,b,c) <--a,b,c are columns
select a,b,c
from SourceTable
where ....

to copy rows all at once from one table to another? no need for table variables and loops ....

- Jeff

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-23 : 11:41:13

Hey Jeff,
Yeah, I knew that. This proc is an 'as is' deal that was here when I came on board. It has been used for a while, all along skipping records due to the screwed up WHERE clause. I was thinking that maybe this should be re-written, but for the sake of simplicity thought I would ask if anyone knew of a quick fix for this. Any suggestions as to how to go about it? It seems to me that we need something like:

WHERE (WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
AND WARR_RQST_DATA.TKT_NO=WARR_LABOR_RCVD.TKT_NO
AND WARR_RQST_DATA.ORIG_PARTS_VEND2 IS NULL
AND WARR_RQST_DATA.PARTS_POSTED ='Y'
AND WARR_RQST_DATA.LABOR_POSTED='Y')
OR (WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
AND WARR_RQST_DATA.TKT_NO=WARR_LABOR_RCVD.TKT_NO
AND WARR_RQST_DATA.ORIG_PARTS_VEND2 IS NOT NULL
AND WARR_RQST_DATA.PARTS_POSTED ='Y'
AND WARR_RQST_DATA.LABOR_POSTED='Y'
AND WARR_RQST_DATA.PARTS2_POSTED='Y')
OR (WARR_RQST_DATA.TKT_NO=WARR_LABOR_RCVD.TKT_NO
AND WARR_RQST_DATA.ORIG_PARTS_VEND IS NULL
AND WARR_RQST_DATA.ORIG_PARTS_VEND2 IS NULL
AND WARR_RQST_DATA.LABOR_POSTED='Y')
OR (WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
AND WARR_RQST_DATA.ORIG_LABOR_VEND IS NULL
AND WARR_RQST_DATA.ORIG_PARTS_VEND2 IS NULL
AND WARR_RQST_DATA.PARTS_POSTED='Y')
OR (WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
AND WARR_RQST_DATA.ORIG_LABOR_VEND IS NULL
AND WARR_RQST_DATA.ORIG_PARTS_VEND2 IS NOT NULL
AND WARR_RQST_DATA.PARTS_POSTED='Y'
AND WARR_RQST_DATA.PARTS_POSTED2='Y')

Damn! What a mouth full this is.. Seems to me that this is what it needs to say,although I'm not 100% positive.There has GOT to be a better way to do it.
Help!
Man am I Tired-of shoveling-snow

andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources