IF CONDITION - 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

Old Forums

CLOSED - General SQL Server

IF CONDITION

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-13 : 09:42:38

Help me!!! Here's my code.

ALTER PROCEDURE SPE_ITEMUSED
(@ROW INT) AS

DECLARE @ORDERQTY AS INT
DECLARE @LN_NO AS INT

SELECT @ORDERQTY=QTY,@LN_NO=LN_NO FROM V_SPE_PLAN_DTL WHERE MOHEADERKEY=@ROW
IF @LN_NO>NULL
BEGIN

SELECT SUBSTRING(COMPONENT,(CHARINDEX(']',COMPONENT)+1),5)AS COMPONENT,SEQN,STARTDATE,COMPLETEDATE,ITEMNUMBER,LN_NO,(@ORDERQTY/QTY) AS QTY,T_ID,MO_STATUS FROM V_SPE_PLAN_DTL
WHERE MOHEADERKEY=@ROW
END
ELSE
SELECT COMPONENT,SEQN,STARTDATE,COMPLETEDATE,ITEMNUMBER,LN_NO,QTY='14',T_ID,MO_STATUS FROM V_SPE_PLAN_DTL
WHERE MOHEADERKEY=@ROW

Basically, I want to check to see if the LN_NO is null, if it is - then I want to run my first statement. The only difference between the two is the little equation for Qty.

jhermiz

3564 Posts

Posted - 2003-10-13 : 09:46:16

Use IsNull not > NULL..you cannot use an operator against a null value.

jhermiz

3564 Posts

Posted - 2003-10-13 : 09:47:37

Here's some info from books online:

Result Types
Boolean

Return Code Values
If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE.

Remarks
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-13 : 09:53:30

Sorry - that was just a last minute thing and didn't switch it back. My code does say @LN_NO=Null.

jhermiz

3564 Posts

Posted - 2003-10-13 : 10:24:37

huh?

Use Is Null

Not = Null

Can you post your code..or do you have a solution?

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-13 : 10:45:32

I think maybe I can't do what I need to with this statement (well duh!). Maybe I should be using cursors? I still to research as I am a cursor newbie.

What I really want to do is get the value of @ORDERQTY where LN_NO is not null for each row. Then I want to use that value in subsequent rows where LN_NO is null, until I hit a row where (again) LN_NO is not null. At that point, a new variable would be assigned to @ORDERQTY.

Does that make sense? Man! my brain hurts. If anyone understands that gibberish, please give me a clue which is the best way to preceed.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-13 : 10:50:31

Your thinking sequentially...

You need to think in set based terms...

You need to make your process do everything at once...

What is it, overall, that needs to get done...

ie...I start here with data that looks like this...

at the end...it has to look like this...

If you provide DDL, sample data, and what the results look like, it'll be easier to help..

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-13 : 11:10:38

OK - deep breath . . .

LN_NO QTY/HRS
1 100
NULL .333
NULL .042
NULL .055

2 475
NULL .987
NULL .456

So, I want to take the 100 (@ORDERQTY) where LN_NO is not null, then I want to multiple .333*100, .042*100, etc., until I get to LN_NO 2. Then @ORDERQTY would become 475. So, then .987*475.

THANKS so much for any help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-13 : 12:55:25

Girlnet, it would help if you provided your table structure in form of CREATE TABLE statement and also sample data in the form of INSERT INTO statements. You'll find that we can answer your question rather quickly if we have all of the information that is needed in order to work on it. When you don't provide that information, we have to type this stuff out on our boxes, which is not something that we typically want to do.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-13 : 15:26:42

Got anything else in there to "tie" the group together?

The order of data in a relational database is irrelevant.

Even if you used a cursor (not that I'm recommending that), you could get different results everytime you run if you don't order by something...

Any other columns we could use?

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-14 : 07:49:30

Thanks Brett,

Here's my select statement:

SELECT ROW_ID, T_ID, STARTDATE, COMPLETEDATE, QTY, COMPONENT, ITEMNUMBER, LN_NO, MO_STATUS, MOHEADERKEY, SEQN FROM SPE_PLAN_DTL ORDER BY ROW_ID DESC, T_ID

T_ID is the primary and ROW_ID is what keep the group together.

Example:

LN_NO|QTY/HRS|T_ID|ROW_ID
1| 100 | 1 |22178
NULL|.333|2|22178
NULL .042|3|22178
NULL .055|4|22178

2| 475|5|22179
NULL|.987|6|22179
NULL|.456 |7|22179

Is that better?

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-14 : 08:32:55

Without cursing, a query to provide the totals you need would be

SELECT ROWID, LN_NO, QTY

FROM V_SPE_PLAN

WHERE LN_NO IS NOT NULL

The above query returns a recordset of the groups and the LN_NO. Use this recordset in a join to get the results you want

SELECT A.ROWID, A.LN_NO, A.QTY * B.QTY AS MyProduct

FROM V_SPE_PLAN B

INNER JOIN (

SELECT ROWID, LN_NO, QTY

FROM V_SPE_PLAN

WHERE LN_NO IS NOT NULL

) B ON A.ROWID = B.ROWID -- This'll match up the NULL and NOT NULL in a single row

WHERE B.LN_NO IS NULL

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-14 : 09:11:18

Sam...you've got 2 aliases labeled as "B"...which is suppose to be "A"..

I'm guessing the first table reference

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-14 : 09:29:10

Brett, if I gave all the answers, it would take away the enjoyment of life's challenge.

I have no idea what I was doing here.

OK. The first grouping is A. NO! B! I meant B!

That would mean

SELECT A.ROWID, A.LN_NO, A.QTY * B.QTY AS MyProduct

FROM V_SPE_PLAN B

INNER JOIN (

SELECT ROWID, LN_NO, QTY

FROM V_SPE_PLAN

WHERE LN_NO IS NOT NULL

) A ON A.ROWID = B.ROWID -- This'll match up the NULL and NOT NULL in a single row

WHERE B.LN_NO IS NULL

Thanks Brett.

I'm not sure this query is the ticket. Depends on the return recordset Girlnet wants.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-14 : 09:50:33

And whats a "GirlNet"....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-14 : 10:40:17

OK...sample stuff...

cut and past this in QA and see if it works for you...


USE Northwind
GO

CREATE TABLE V_SPE_PLAN_DTL ( 					-- It's really a view right?
				  LN_NO 	int
				, [QTY/HRS] 	decimal(9,3)	-- Bad choic for a column name
				, T_ID 		int
				, ROW_ID 	int		-- Is this Oracle?
				)
GO

INSERT INTO V_SPE_PLAN_DTL (LN_NO, [QTY/HRS], T_ID, ROW_ID)
SELECT 1	,100 	,1	,22178 UNION ALL
SELECT NULL	,.333	,2	,22178 UNION ALL
SELECT NULL	,.042	,3	,22178 UNION ALL
SELECT NULL	,.055	,4	,22178 UNION ALL

SELECT 2	,475	,5	,22179 UNION ALL
SELECT NULL	,.987	,6	,22179 UNION ALL
SELECT NULL	,.456	,7	,22179
GO

SELECT A.ROW_ID, B.AMT * A.Multiplier AS Result
  FROM		( SELECT ROW_ID, SUM([QTY/HRS]) AS Multiplier 
		    FROM V_SPE_PLAN_DTL 
		   WHERE LN_NO IS NOT NULL 
		GROUP BY ROW_ID
		) AS A
LEFT JOIN 
		( SELECT ROW_ID, [QTY/HRS] AS AMT
		    FROM V_SPE_PLAN_DTL 
		   WHERE LN_NO IS  NULL 
		) AS B
       ON A.ROW_ID = B.ROW_ID


Or did you want the following...not sure


SELECT A.ROW_ID, SUM(B.AMT * A.Multiplier) AS Result
  FROM		( SELECT ROW_ID, SUM([QTY/HRS]) AS Multiplier 
		    FROM V_SPE_PLAN_DTL 
		   WHERE LN_NO IS NOT NULL 
		GROUP BY ROW_ID
		) AS A
LEFT JOIN 
		( SELECT ROW_ID, [QTY/HRS] AS AMT
		    FROM V_SPE_PLAN_DTL 
		   WHERE LN_NO IS  NULL 
		) AS B
       ON A.ROW_ID = B.ROW_ID
GROUP BY A.ROW_ID

Brett

8-)

EDIT: Well...looking at it now...it's what Sam did...sort of, I just took in to count that you might have multiple multipliers...

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-14 : 10:55:13

Thanks guys! You're both awesome. I working with the code right now and it looks like only my LN_NO is not null are returned in the rs. I still want to monkey around with it. I have looked at X002548's post, but it's next.

I just wanted to say thanks again for you help.

Girlnet? It's a female that specializes in network design who, for the last few years, dabbles in ASP/SQL. Lastest version is ASP.NET with SQL2000 & sp_procs (can you tell?). I have to confess, most of earlier stuff (Classic ASP) was writen with SQL 7 commands right in the page (gasp!). But as you can see, I'm changing my way!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-14 : 11:10:38

quote:
Originally posted by Girlnet

I have looked at X002548's post, but it's next.

Just cut and paste in to query analyzer...it should just run...

quote:

Girlnet? It's a female that specializes in network design who, for the last few years, dabbles in ASP/SQL. Lastest version is ASP.NET with SQL2000 & sp_procs (can you tell?). I have to confess, most of earlier stuff (Classic ASP) was writen with SQL 7 commands right in the page (gasp!). But as you can see, I'm changing my way!

Cool...Tara and Sara could use the company...where has sara been?

Brett

8-)

Lavos
Posting Yak Master

200 Posts

Posted - 2003-10-14 : 18:16:42

Patience Brett,

She'll be coming around the mountain when she comes.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-15 : 07:48:45

Hey Guys,

The code is working awesome! You actually got me to finally understand joins. Usually, I cheat. I do have one small thing though.

Here's the actual code:

SELECT B.LN_NO,A.ROW_ID,B.T_ID,B.AMT * A.QTY AS QTY,B.COMPONENT,A.SEQN,B.STARTDATE,B.COMPLETEDATE,A.ITEMNUMBER,B.MO_STATUS
FROM ( SELECT LN_NO, ROW_ID, T_ID, QTY, ITEMNUMBER, MOHEADERKEY, SEQN
FROM SPE_PLAN_DTL
WHERE LN_NO IS NOT NULL
GROUP BY LN_NO,ROW_ID,T_ID,QTY, ITEMNUMBER, MOHEADERKEY, SEQN
) AS A
LEFT JOIN
( SELECT LN_NO,ROW_ID,T_ID,QTY AS AMT,COMPONENT,MO_STATUS,STARTDATE,COMPLETEDATE
FROM SPE_PLAN_DTL
WHERE LN_NO IS NULL
) AS B
ON B.ROW_ID = A.ROW_ID WHERE MOHEADERKEY=@ROW ORDER BY A.ROW_ID DESC, B.T_ID

It works perfect except it eliminates my rs where LN_NO is not null to my datagrid.

SELECT 1 ,100 ,1 ,22178 UNION ALL (eliminating)
SELECT NULL ,.333 ,2 ,22178 UNION ALL
SELECT NULL ,.042 ,3 ,22178 UNION ALL
SELECT NULL ,.055 ,4 ,22178 UNION ALL

SELECT 2 ,475 ,5 ,22179 UNION ALL (eliminating)
SELECT NULL ,.987 ,6 ,22179 UNION ALL
SELECT NULL ,.456 ,7 ,22179

What am I missing?

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-15 : 08:23:58

Could you post a couple of rows from the recordset you want and what the query is actually returning?

Does this change help?


SELECT A.LN_NO,A.ROW_ID,B.T_ID,B.AMT * A.QTY AS QTY,
       B.COMPONENT,A.SEQN,B.STARTDATE,B.COMPLETEDATE,A.ITEMNUMBER,B.MO_STATUS
FROM (
  SELECT LN_NO, ROW_ID, T_ID, QTY, ITEMNUMBER, MOHEADERKEY, SEQN
    FROM SPE_PLAN_DTL
    WHERE LN_NO IS NOT NULL
    GROUP BY LN_NO,ROW_ID,T_ID,QTY, ITEMNUMBER, MOHEADERKEY, SEQN
) AS A  -- A is the set that is "NOT NULL"
LEFT JOIN
( 
  SELECT LN_NO,ROW_ID,T_ID,QTY AS AMT,COMPONENT,MO_STATUS,STARTDATE,COMPLETEDATE
    FROM SPE_PLAN_DTL
    WHERE LN_NO IS NULL
) AS B  -- B is the set that "IS NULL"
ON B.ROW_ID = A.ROW_ID WHERE MOHEADERKEY=@ROW ORDER BY A.ROW_ID DESC, B.T_ID

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources