IF statement troubles - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

IF statement troubles

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 13:40:38

I have just about figured everything out except for 2 things.

First sometimes there will not be a ‘177’ record. How can I structure my IF statement so I can bypass the code. Other wise it creates a hdr and a blank detail record? Or is there another conditional verb I can use that might be better?

Second I can’t get my cnt to show up in my hdr records. The print statement is not even showing them. What am I doing wrong?

Any ideas?
Thanx in advance,

Here's my code:
DECLARE @Cnt int
DECLARE @Sys varchar (4);

DECLARE @vwSys varchar (4), @vwPrin varchar (4)
/* Define Cursor */
/*Works like an ARRAY*//*holds current record */
Declare vwCursor CURSOR FOR
SELECT Sys, Prin FROM dbo.vwPrin
ORder by Sys, Prin
open vwCursor
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print '1st FETCH '
Print @vwSys
Print @vwPrin
SET @Sys = @vwSys

WHILE @@Fetch_Status = 0 --34
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP(1) '9', @Cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, Terminal_Id, Op_Code) --45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '020' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
Print @cnt
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, Clerk_Code, Memo_Text) --45
SELECT Account_Number, Transaction_Code, Clerk_Code, Memo_Text
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '115' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
[b]IF (SELECT Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin FROM dbo.tblTrans)[/b]
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, SubTrans_2, Terminal_Id, Op_Code) --45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_Id, Op_Code
FROM dbo.tblTrans --55
WHERE dbo.tblTrans.Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
/*Continue FETCH Command until EOF */
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print ' 2nd FETCH '
Print @cnt
END
--Print @vwSys
--Print @vwPrin
END
CLOSE vwCursor
DEALLOCATE vwCursor
END

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 13:53:48

DUH, I found the problem with the IF statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:04:58

Also initialize with SET @Cnt = 0 (or 1).

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:13:51

Something like this

DECLARE @Cnt INT,
	@vwSys VARCHAR(4),
	@vwPrin VARCHAR(4)

SET	@cnt = 1

DECLARE	vwCursor CURSOR FOR	SELECT		Sys,
						Prin
				FROM		dbo.vwPrin 
				ORDER BY	Sys,
						Prin

OPEN	vwCursor

FETCH NEXT
FROM		vwCursor
INTO		@vwSys,
		@vwPrin

WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT		tblOutput
				(
					Batch_Type,
					Batch_Num,
					Sys,
					Prin,
					Terminal_id,
					Op_Code
				)
		SELECT TOP 1	'9',
				@Cnt,
				Sys,
				Prin,
				Term_Id,
				Op_Code
		FROM		dbo.tblTrans
		WHERE		Sys = @vwSys
				AND Prin = @vwPrin 

		INSERT		tblOutput
				(
					Account_Num,
					Tran_Code,
					SubTrans,
					Terminal_Id,
					Op_Code
				)
		SELECT		Account_Number,
				Transaction_Code,
				SubTrans,
				Term_id,
				Op_Code
		FROM		dbo.tblTrans
		WHERE		dbo.tblTrans.Transaction_Code = '020'
				AND Sys = @vwSys
				AND Prin = @vwPrin

		SET	@Cnt = @Cnt + 1

		INSERT		tblOutput
				(
					Batch_Type,
					Batch_Num,
					Sys,
					Prin,
					Terminal_id,
					Op_Code
				)
		SELECT TOP 1	'9',
				@cnt,
				Sys,
				Prin,
				Term_Id,
				Op_Code
		FROM		dbo.tblTrans 
		WHERE		Sys = @vwSys
				AND Prin = @vwPrin

		INSERT		tblOutput
				(
					Account_Num,
					Tran_Code,
					Clerk_Code,
					Memo_Text
				)
		SELECT		Account_Number,
				Transaction_Code,
				Clerk_Code,
				Memo_Text
		FROM		dbo.tblTrans
		WHERE		dbo.tblTrans.Transaction_Code = '115'
				AND Sys = @vwSys
				AND Prin = @vwPrin

		SET	@Cnt = @Cnt + 1

		IF EXISTS (SELECT * FROM dbo.tblTrans WHERE Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin) 
			BEGIN
				INSERT		tblOutput
						(
							Batch_Type,
							Batch_Num,
							Sys,
							Prin,
							Terminal_id,
							Op_Code
						)
				SELECT TOP 1	'9',
						@cnt,
						Sys,
						Prin,
						Term_Id,
						Op_Code
				FROM		dbo.tblTrans 
				WHERE		Sys = @vwSys
						AND Prin = @vwPrin

				INSERT		tblOutput
						(
							Account_Num,
							Tran_Code,
							SubTrans,
							SubTrans_2,
							Terminal_Id,
							Op_Code
						)
				SELECT		Account_Number,
						Transaction_Code,
						SubTrans,
						SubTrans2,
						Term_Id,
						Op_Code
				FROM		dbo.tblTrans --55
				WHERE		dbo.tblTrans.Transaction_Code = '177'
						AND Sys = @vwSys
						AND Prin = @vwPrin 

				SET	@Cnt = @Cnt + 1
			END

		FETCH NEXT
		FROM		vwCursor
		INTO		@vwSys,
				@vwPrin
	END

E 12°55'05.25"
N 56°04'39.16"

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-29 : 09:03:11

Thanx so much Peso for all your help. The initialization of @Cnt was the answer, I’m kicking out batch numbers right and left. However my IF statement is still not working, the statement never test ‘true’ so now I’m not getting any ‘177’ recs. When I comment the IF statement out I get ‘177’ recs where they shouldn’t be (and fortunately where they should).

I changed my IF statement to select all (*), but it didn’t seem to help. Any ideas? This is my last bump in the road.

Thanx again,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 09:06:13

What's wrong with this?

IF EXISTS (SELECT * FROM dbo.tblTrans WHERE Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin)

Change it accordingly.

E 12°55'05.25"
N 56°04'39.16"

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-29 : 10:32:34

What's wrong with this?

Beats me?? I agree with you logically it should work. But it doesn’t.
Our IF statements are identical, to be sure I just pasted yours in ran ti again and got the same output. All of the '177' recs are missing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 11:02:02

The IF above checks to see if there is at least one record already present with Transaction_Code = 177 in the tblTrans table.
Are you sure you want to check for the existence in the tblTrans table?
Maybe you want to check for existence in the tblOutput table?

E 12°55'05.25"
N 56°04'39.16"

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-29 : 12:49:29

I only want to write a hdr if there are detail records to be written. The detail records are on the tblTrans table, I ck’d to make sure. I also commented out the IF statement and it writes the ‘177’ recs where they should be written and where they shouldn’t.

The IF statement is obviously not a Boolean statement. I’ll have to create a convoluted way of getting a Boolean statement.

Thank you so much for hanging in there with me. I'll keep you posted (as if you care (smile)).

Thanx again,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources