Help with View / SP - 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 2008 Forums

Transact-SQL (2008)

Help with View / SP

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-18 : 21:40:25

Can someone tell me how to do this, I have a view:
create view BksByPC
as
select
item.product_code as Product_Code,
pc.description as Description,
sum(coitem.qty_ordered * coitem.price) as Orders,
sum((coitem.qty_ordered * coitem.price) * coitem.disc / 100) as Discounts
from coitem
inner join (
select co_num,order_date from co) as c on c.co_num = coitem.co_num
inner join item on item.item = coitem.item
inner join itemwhse on itemwhse.item = item.item
inner join (select distinct prodcode.description,prodcode.product_code from prodcode)as pc on pc.product_code = item.product_code
group by pc.description,item.product_code

I am trying to test using :
select * from BksByPC
where c.order_date > '12/01/09'

I get an error on where c.order_date > '12/01/09'

What is the correct way to do this?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-18 : 23:31:56

quote:
I get an error on where c.order_date > '12/01/09'

Can you kindly tell us what is the error message ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 23:59:11

might be problem with your date value interpretation by SQL due to locale settings. can you make where clause like below and try

c.order_date > '20090112'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 02:12:00

" might be problem with your date value interpretation by SQL due to locale settings"

Not sure (in this case). Each number <= 12

But its a lousy way to represent dates, as we both know. Not even a Century ....


select * from BksByPC
where c.order_date > '12/01/09'

You are referencing the table alias "C" which you have not defined. (Its defined in the VIEW's tables, but they are not in scope when you use the view. Moreover, your VIEW does not return [order_date] so this isn't going to work anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 02:23:41

OP has not posted the error so it may be something unrelated to date format issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 02:30:09

If that's the case I'll eat my hat - AND send you a JPG!

(But I will hapily agree with you that its a lousy, and ambiguous, way to represent a date)

This works for me without error


SET NOCOUNT ON
SET DATEFORMAT dmy
SELECT CONVERT(datetime, '12/01/09')
SET DATEFORMAT mdy
SELECT CONVERT(datetime, '12/01/09')
SET DATEFORMAT dym
SELECT CONVERT(datetime, '12/01/09')
SET DATEFORMAT myd
SELECT CONVERT(datetime, '12/01/09')
SET DATEFORMAT ydm
SELECT CONVERT(datetime, '12/01/09')
SET DATEFORMAT ymd
SELECT CONVERT(datetime, '12/01/09')
SET NOCOUNT OFF

but I get 6 different dates

Bad ... bad ... bad ....

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-19 : 07:58:16

Here is the error:
Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "c.order_date" could not be bound.

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-19 : 08:16:29

Here is the view that works like I want it to, but how do I have the dates be variables in the Stored Procedure.

create view BksByPC
as
select
item.product_code as Product_Code,
pc.description as Description,
sum(coitem.qty_ordered * coitem.price) as Orders,
sum((coitem.qty_ordered * coitem.price) * coitem.disc / 100) as Discounts
from coitem
inner join (
select co_num,order_date from co) as c on c.co_num = coitem.co_num
and c.order_date between '12/01/09' and '12/31/09'
inner join item on item.item = coitem.item
inner join itemwhse on itemwhse.item = item.item
inner join (select distinct prodcode.description,prodcode.product_code from prodcode)as pc on pc.product_code = item.product_code
group by pc.description,item.product_code

select * from BksByPC where ?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 09:31:24

quote:
Originally posted by baze7

Here is the view that works like I want it to, but how do I have the dates be variables in the Stored Procedure.

create view BksByPC
as
select
item.product_code as Product_Code,
pc.description as Description,
sum(coitem.qty_ordered * coitem.price) as Orders,
sum((coitem.qty_ordered * coitem.price) * coitem.disc / 100) as Discounts
from coitem
inner join (
select co_num,order_date from co) as c on c.co_num = coitem.co_num
and c.order_date between '12/01/09' and '12/31/09'
inner join item on item.item = coitem.item
inner join itemwhse on itemwhse.item = item.item
inner join (select distinct prodcode.description,prodcode.product_code from prodcode)as pc on pc.product_code = item.product_code
group by pc.description,item.product_code

select * from BksByPC where ?????

I bet this is not your full working code. i cant spot the end of derived table after inner join

did nt understand the statement in blue
do you mean you need to make view date driven?
also what procedure are you talking about?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-19 : 10:17:41

I am new to SQL so forgive me if my terms are not correct: Here is my full view code:
create view BksByPC
as
select
item.product_code as Product_Code,
pc.description as Description,
sum(coitem.qty_ordered * coitem.price) as Orders,
sum((coitem.qty_ordered * coitem.price) * coitem.disc / 100) as Discounts
from coitem
inner join (
select co_num,order_date from co) as c on c.co_num = coitem.co_num
inner join item on item.item = coitem.item
inner join itemwhse on itemwhse.item = item.item
inner join (select distinct prodcode.description,prodcode.product_code from prodcode)as pc on pc.product_code = item.product_code
group by pc.description,item.product_code

So then I want to create a stored procedure that has variables for c.order_date from the view:

create procedure BksByPC_SP
as

select * from BksByPC
where order_date between '12/01/09' and '12/31/09'

the last line is where I am confused on the correct way to program that. Eventually these are going to be variables. But I am just testing it now. Hope that helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-19 : 10:27:57

maybe this ?


select
	item.product_code as Product_Code,
	pc.description as Description,
	c.order_date,
	sum(coitem.qty_ordered * coitem.price) as Orders,
	sum((coitem.qty_ordered * coitem.price) * coitem.disc / 100) as Discounts
from 	coitem
	inner join 
	(
		select 	co_num, order_date 
		from 	co
	) as c 			on c.co_num = coitem.co_num
	inner join item 	on item.item = coitem.item
	inner join itemwhse 	on itemwhse.item = item.item
	inner join 
	(
		select 	distinct 
			prodcode.description,
			prodcode.product_code 
		from 	prodcode
	)as pc 			on pc.product_code = item.product_code
group by 
	pc.description,
	item.product_code,
	c.order_date

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:28:28

As I said earlier you cannot have order_date in there WHERE clause because it is not in the SELECT defined for the VIEW.

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-19 : 10:32:56

OK, that makes sense. But is there a way I can put that in the select statement, but still on group by my product code. I know if I just put it in the select liek I normally would, then my results are not like I want them. I only need the order date for a filter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:36:47

quote:
Originally posted by baze7

OK, that makes sense. But is there a way I can put that in the select statement, but still on group by my product code. I know if I just put it in the select liek I normally would, then my results are not like I want them. I only need the order date for a filter.

Nope you cant. the moment you group data on some fields, you need to apply some kind of aggregation on the other fields. If you can post some sample data and give us an idea of output you expect we may be able to suggest you an alternative

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:41:32

You could use a Stored Procedure, instead of a view.

That would take a parameter (for the date) and return the data grouped by product, as you want.

But you can't quite use it in the same way as a View - so may not be the right answer for you?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources