CASE and FROM - 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 2000 Forums

SQL Server Development (2000)

CASE and FROM

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 11:58:01

I am screwing up the syntax again. I think Tara helped me out on the last one like this but “search” turns up null.

Need to add from into a case statement.

UPDATE MasterPCS.dbo.PalletCount_Sub
Set
Pal_type = Case When MasterPCS.dbo.Job_Number_Master_List.our_customer = 'ccc' FROM MasterPCS.dbo.Job_Number_Master_List INNER JOIN MasterPCS.dbo.Job_number_Sub_list INNER JOIN MasterPCS.dbo.PalletCount_Sub ON MasterPCS.dbo.Job_number_Sub_list.Job_Number_Sub_Link = MasterPCS.dbo.PalletCount_Sub.Job_Number_Sub_Link ON MasterPCS.dbo.Job_Number_Master_List.Job_Number_Link = MasterPCS.dbo.Job_number_Sub_list.Job_Number_Link then
'Stock'
Else 'case'
End
WHERE (MasterPCS.dbo.Job_number_Sub_list.Sub_job_number = @SubjobNumber)

Please help.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 12:46:43

I didn't think this would work but syntactically I think:


UPDATE MasterPCS.dbo.PalletCount_Sub x
	SET Pal_type = 
		CASE WHEN (SELECT a.our_customer
			FROM MasterPCS.dbo.Job_Number_Master_List a 
			INNER JOIN MasterPCS.dbo.Job_number_Sub_list b 
			ON a.Job_Number_Link = b.Job_Number_Link
			INNER JOIN MasterPCS.dbo.PalletCount_Sub c 
			ON b.Job_Number_Sub_Link = c.Job_Number_Sub_Link  ) = 'ccc'
			THEN 'Stock' 
			ELSE 'case' 
		END 
WHERE x.Sub_job_number = @SubjobNumber

And why don't you use labels?

I'll see if I can make this work with some Northwind tables....

but , first, LUNCH!

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 12:53:22

I had to fix your joins cuz they bothered me. Does this work?:

UPDATE PalletCount_Sub
Set Pal_type = Case When jnml.our_customer = 'ccc'
then 'Stock'
Else 'case'
End
FROM Job_Number_Master_List jnml
INNER JOIN Job_number_Sub_list jnsl ON jnml.Job_Number_Link = jnsl.Job_Number_Link
INNER JOIN PalletCount_Sub ps ON jnsl.Job_Number_Sub_Link = ps.Job_Number_Sub_Link
WHERE jnsl.Sub_job_number = @SubjobNumber

Tara

Edited by - tduggan on 07/23/2003 12:54:43

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 13:05:17

jeeeeese I need to get some sleep I can't beieve I put the FROM stament There.

Thanks Brett

Thanks Tara

I gotta get this done before that Dang .mdb Dies for good.

Well only a hundered or so S.P. and 36 Apps to go.

Labels? "We no need no stinking Labels"

Labels? In what Context Brett?

Jim
Users <> Logic

Edited by - jiml on 07/23/2003 13:08:28

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:07:06

Don't know how much this helps..but the following works


SELECT CASE WHEN (SELECT SUM(OrderID) FROM Orders) = 0 THEN 'No Customers' ELSE 'We are in business' END
, a.*
FROM [Order Details] a

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:48:04

quote:

Labels? "We no need no stinking Labels"

Labels? In what Context Brett?

Labels< aliases...not sure, I think alias

So you can become a good DBA and be LAZY

Instead of typing the fully qualified name just use a short alias

SELECT * FROM mytable00 a INNER JOIN myTable99 b ON a.key = b.key

Instead of:


ON MasterPCS.dbo.Job_Number_Master_List.Job_Number_Link =
   MasterPCS.dbo.Job_number_Sub_list.Job_Number_Link

painful

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 13:55:17

Jim, see my solution for labels. I labeled Job_Number_Master_List as jnml that way I could say jnml.Job_Number_Link instead of Job_Number_Master_List.Job_Number_Link. I used other labels as well.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 13:56:08

I use the term aliases instead of labels.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 14:33:13

Will this work even when crossing or combineing db's ?

Where do you resove the Label?

Jim
Users <> Logic

Edited by - jiml on 07/23/2003 14:35:00

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 14:41:01

Yes it even works when you use different databases in a query, such as:

SELECT a.Column4, b.ColumnA
FROM Database1.Table1 a
INNER JOIN Database2.dbo.Table2 b ON a.Column1 = b.Column1

I don't understand your second question. Please explain.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 14:49:51

How does the S.P. Know what Db to use?

I.E. masterpcs.dbo.Job_Number_Master_List = jnml

also This S.P. do not reside in the Masterpcs database it is in another hence "masterpcs.dbo" does this make a diff?

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 14:59:41

quote:

How does the S.P. Know what Db to use?

I.E. masterpcs.dbo.Job_Number_Master_List = jnml

It knows because I told it. In the code, I put jnml right after masterpcs.dbo.Job_Number_Master_List which told it jnml will now mean masterpcs.dbo.Job_Number_Master_List. So anywhere you would have put masterpcs.dbo.Job_Number_Master_List, you can now just put jnml.

quote:

also This S.P. do not reside in the Masterpcs database it is in another hence "masterpcs.dbo" does this make a diff?

An alias doesn't care. It only cares about the definition of the alias which from above is masterpcs.dbo.Job_Number_Master_List. That's all it cares about.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 15:21:57

Thanks This will come in handy.

Truth be told I am "LAZY" I do not type all the repitition. I generaly start all my S.P.s using a acesses GUI interface it does most of the work for me. (note create selects first then change to updates or inserts)

Course there is a limit to what a GUI can do and you have to watch it because sometimes its a little to helpfull.

For someone who is used to Access it shure speeds up the process.

If I was anywere as good as you two I probubly would not use it.

I know Tara hates Access, But she can code faster than I can Point and click.

I Bow before you Tara

Princess.

Jim
Users <> Logic

Edited by - jiml on 07/23/2003 15:24:36

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 15:28:17

But you will only get better at this if you stop using Access. I'm not joking either. When you first start out, you probably use Enterprise Manager a lot. And you wonder why people would want to type out everything in Query Analyzer. But then you start using Query Analyzer a little here and there. Then you start remembering what the syntax is for the various commands that you use day in and day out. Then you can't believe that you ever used Enterprise Manager (although you still use it for some things). You just have to break the habit of using the GUI tools. Start now and you'll be much happier and a lot smarter with T-SQL.

You might be slow at first when you start using Query Analyzer, but after just a little while, you'll speed up and get better.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 15:29:02

quote:

I generaly start all my S.P.s using a acesses GUI interface it does most of the work for me. (note create selects first then change to updates or inserts)

Course there is a limit to what a GUI can do and you have to watch it because sometimes its a little to helpfull.

Damn strait...but limits is not the least of your worries...

Access can sure make a mess of something REALLY simple...

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 15:40:42

Both of you are right And I am using it less and less. mostly because you cannot do cast or multiple updates or inserts with GUI
So I end up codeing in the SQL View. But I do use it to help me create my table joins.

I wish I would Have had you two when this project started. I would not be cleaning up this huge mess.

Then I could blame it on the Teacher but I am Self Taught LOL.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 15:43:20

quote:

So I end up codeing in the SQL View.

What is SQL View? Is that inside Enterprise Manager? If so, use Query Analyzer instead.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 15:53:17

Access SQL View is pure SQL text like notebook. no color code. no nothin. But it displays in SQL what was selected in the GUI.

Also I can pull up the associated S.P. directly from the FORM I am reworking right in the properties and thats a godsend with all the re-nameing I am doing.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 15:55:37

quote:

Access SQL View is pure SQL text like notebook. no color code. no nothin. But it displays in SQL what was selected in the GUI.

Also I can pull up the associated S.P. directly from the FORM I am reworking right in the properties and thats a godsend with all the re-nameing I am doing.

Query Analyzer does have color coding, and it also allows you to pull the stored procedure code from the left pane into the right pane.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-23 : 16:42:25

I do use QA especially when I want to use the “print” function on parts of My S.P.
“Print” does not work in access.

However I have had a couple of Very complex S.P. that worked fine in QA but not when called from Access or VB front end. Oh well nothings perfect.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 16:49:00

I hope that you remove the PRINT statements before it goes into production. PRINT statements are useful for debugging purposes but are a waste, typically, in production.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources