Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL query
alter proc [dbo].[SpCateProducts](@cate_id int) 
as 
begin 
with 
tbl1 as 
(
select sb.subcate_name, 
sum(p.qty) 
from subcategory sb join product p 
on p.subcate_id = sb.subcate_id 
where sb.cate_id = @cate_id 
group by sb.subcate_name
) 
with tbl2 as
(
select top 1 pname from product join subcategory 
on product.subcate_id= 
(
select top 1 subcate_id 
from subcategory 
where cate_id=3 
order by NEWID() 
)
) 
 
select * from tbl1 join tbl2 end
 

But it shows some errors and doesnt executing......
1st errors in 5th line that (no column is specified in for column tbl1)
2nd in with keyword
3rd in tbl1
4th in end
 
plz tell me soon abt these errors ..............
Posted 14-Oct-12 6:33am
Comments
Wes Aday at 14-Oct-12 11:45am
   
And what have you declared tbl1 to be?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is a nonsense in t-sql. See my answer at your other question: how can i bind two tbls (returning by 2 select queries)[^].
 
By the way, how can you expect somebody to fix your code, without giving proper information: what you want as result (not that you want somebody to fix your code, we can figure this out by our own), what is your input (the table schemas and relation), what is the error message ("some error" means nothing)...
Please remember, that we can not see your screen, or read your mind. We can help you only, if you provide us the information needed...
  Permalink  
Comments
Maciej Los at 14-Oct-12 13:07pm
   
Agree, +5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
Error 1 is occurred because you didn't specify alias name for sum(p.qty) in the first
CTE.
Error 2, you didn't put ';' at the end of the first CTE.
Error 3, I am not able to tell the reason because you didn' provide any info of it.
Error 4, in the last statement, replace 'Join' with ','.
 
Above all syntactical errors. One more thing, CTE result can be persisted for its immediate select query only. So you need to convert your query like this..
 
alter proc [dbo].[SpCateProducts](@cate_id int) 
as 
begin 
with 
tbl1 as 
(
select sb.subcate_name, 
sum(p.qty) Qty
.............
) 
, tbl2 as
(
select top 1 pname from product join subcategory 
on product.subcate_id= 
(
select top 1 subcate_id 
from subcategory Join tbl1 ON
<your condition="">
--------- 
)
) 
 
select * from tbl2
 </your>
 
Please provide inputs and output of your require so that we can give our best solutions.
 
Thank you.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 15 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100