December 28, 2011 at 12:12 pm
Hello all,
I am getting error when I use order by clause in stored procedure. It is saying incorrect syntax near order. But when I execute the query it is giving the results.
How to use order by clause with stored procedure?
Thanks
December 28, 2011 at 12:18 pm
There is no difference in using order by in Stored proc or adhoc. Please post your SP script.
-Roy
December 28, 2011 at 12:19 pm
The usual:
SELECT columns
FROM tables/views/etc
WHERE criteria
ORDER BY columns
Can you post the stored procedure? If so, we can take a look at it and see if we can spot the error.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 12:46 pm
select cal.datevalue
,datename(DW,cal.datevalue) day_of_week
,isnull(flowon.sum_flow_on,0) flow_on
,isnull(flowend.sum_flow_end,0) flow_off
,isnull(active_cnt.total_active,0) tot_active
,isnull(stmt_date.stmt_cnt,0) statements
,isnull(chrgs.charges,0) current_charges
,isnull(pay.payments,0) current_payments
from ee_internal.dbo.Calendar cal
left join (select cast(calcs2.flow_start_date as date) flow_on_date,
sum(1) sum_flow_on
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_start_date is not null
group by calcs2.flow_start_date) flowon
on cal.datevalue = flowon.flow_on_date
left join (select cast(calcs2.flow_end_date as date) flow_end_date,
sum(1) sum_flow_end
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_end_date is not null
group by calcs2.flow_end_date) flowend
on cal.datevalue = flowend.flow_end_date
left join (select cal.datevalue active_date
,(select count(1)
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_start_date <= cal.datevalue and calcs2.flow_end_date is null
) total_active
from ee_internal.dbo.Calendar cal) active_cnt
on cal.datevalue = active_cnt.active_date
left join (select x.current_stmt_date,
sum(1) stmt_cnt
from (select distinct account_number,
current_stmt_date
from dbo.billing_charge) x
group by x.current_stmt_date ) stmt_date
on cal.datevalue = stmt_date.current_stmt_date
left join (select current_stmt_date,
sum(calc_price_amt + adjustment_amt + price_tax_amt) charges
from dbo.billing_payment
where TYPE_DESC != 'Level Pay Contract'
and type_desc != 'Transfer to Payment Contract'
and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Regulated',
'Cash - Revenue - Non Regulated',
'Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Non Regulated',
'Deposit Charge - Non Regulated')
and current_stmt_date is not null
group by current_stmt_date) chrgs
on cal.datevalue = chrgs.current_stmt_date
left join (select payment_Date
,sum(calc_price_amt + adjustment_amt + price_tax_amt) payments
from dbo.billing_payment
where type_desc = 'Payment'
and return_reason_Desc is null
group by payment_Date) pay
on cal.datevalue = pay.payment_Date
where cal.datevalue between '03-01-2011' and dateadd(d,40,getdate())
order by cal.datevalue desc
Thanks
December 28, 2011 at 12:50 pm
No syntax error in that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 1:37 pm
There may not be syntax errors, but there are lots of logical errors. For instance, the table clean_cust_account_lcd_cancel_switch is queried three separate times when it looks like it should be possible to rewrite it to query it only once. You're also using SUM() to do distinct counts.
select x.current_stmt_date,
sum(1) stmt_cnt
from (
select distinct account_number,
current_stmt_date
from dbo.billing_charge
) x
group by current_stmt_date
There is a much simpler way to do distinct counts.
select current_stmt_date,
COUNT(DISTINCT Account_Number) stmt_cnt
from dbo.billing_charge
group by x.current_stmt_date
This is precisely the reason that I really hate using SUMs to do COUNTs. If you use the proper aggregate, it's very easy to switch between distinct and non-distinct counts. If you use the improper aggregate, it's much harder and you're more likely to get muddled code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 28, 2011 at 1:39 pm
drew.allen (12/28/2011)
There may not be syntax errors, but there are lots of logical errors. For instance, the table clean_cust_account_lcd_cancel_switch is queried three separate times when it looks like it should be possible to rewrite it to query it only once. You're also using SUM() to do distinct counts.
select x.current_stmt_date,
sum(1) stmt_cnt
from (
select distinct account_number,
current_stmt_date
from dbo.billing_charge
) x
group by current_stmt_date
There is a much simpler way to do distinct counts.
select current_stmt_date,
COUNT(DISTINCT Account_Number) stmt_cnt
from dbo.billing_charge
group by x.current_stmt_date
This is precisely the reason that I really hate using SUMs to do COUNTs. If you use the proper aggregate, it's very easy to switch between distinct and non-distinct counts. If you use the improper aggregate, it's much harder and you're more likely to get muddled code.
Drew
Very true, but the lack of syntax error means there's a difference of some sort between either what was posted vs what generated the error, or the environment it's being tested in.
I'd prefer to see the query that gets a syntax error, and fix that first, and then worry about optimization and standardization next.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 1:50 pm
GSquared (12/28/2011)
I'd prefer to see the query that gets a syntax error, and fix that first, and then worry about optimization and standardization next.
I don't think it's that easy to separate the two. I think that the more convoluted your code is, the easier it is to introduce syntax errors that are difficult to find. Simplifying the code may also remove the source of the syntax error. If you know that you are going to rewrite a section of code anyhow, does it really make sense to spend time and effort finding a syntax error before doing the rewrite?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 28, 2011 at 1:52 pm
drew.allen (12/28/2011)
GSquared (12/28/2011)
I'd prefer to see the query that gets a syntax error, and fix that first, and then worry about optimization and standardization next.I don't think it's that easy to separate the two. I think that the more convoluted your code is, the easier it is to introduce syntax errors that are difficult to find. Simplifying the code may also remove the source of the syntax error. If you know that you are going to rewrite a section of code anyhow, does it really make sense to spend time and effort finding a syntax error before doing the rewrite?
Drew
Can't disagree with you on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 8:54 pm
srik.kotte (12/28/2011)
select cal.datevalue,datename(DW,cal.datevalue) day_of_week
,isnull(flowon.sum_flow_on,0) flow_on
,isnull(flowend.sum_flow_end,0) flow_off
,isnull(active_cnt.total_active,0) tot_active
,isnull(stmt_date.stmt_cnt,0) statements
,isnull(chrgs.charges,0) current_charges
,isnull(pay.payments,0) current_payments
from ee_internal.dbo.Calendar cal
left join (select cast(calcs2.flow_start_date as date) flow_on_date,
sum(1) sum_flow_on
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_start_date is not null
group by calcs2.flow_start_date) flowon
on cal.datevalue = flowon.flow_on_date
left join (select cast(calcs2.flow_end_date as date) flow_end_date,
sum(1) sum_flow_end
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_end_date is not null
group by calcs2.flow_end_date) flowend
on cal.datevalue = flowend.flow_end_date
left join (select cal.datevalue active_date
,(select count(1)
from dbo.clean_cust_account_lcd_cancel_switch calcs2
where calcs2.flow_start_date <= cal.datevalue and calcs2.flow_end_date is null
) total_active
from ee_internal.dbo.Calendar cal) active_cnt
on cal.datevalue = active_cnt.active_date
left join (select x.current_stmt_date,
sum(1) stmt_cnt
from (select distinct account_number,
current_stmt_date
from dbo.billing_charge) x
group by x.current_stmt_date ) stmt_date
on cal.datevalue = stmt_date.current_stmt_date
left join (select current_stmt_date,
sum(calc_price_amt + adjustment_amt + price_tax_amt) charges
from dbo.billing_payment
where TYPE_DESC != 'Level Pay Contract'
and type_desc != 'Transfer to Payment Contract'
and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Regulated',
'Cash - Revenue - Non Regulated',
'Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Non Regulated',
'Deposit Charge - Non Regulated')
and current_stmt_date is not null
group by current_stmt_date) chrgs
on cal.datevalue = chrgs.current_stmt_date
left join (select payment_Date
,sum(calc_price_amt + adjustment_amt + price_tax_amt) payments
from dbo.billing_payment
where type_desc = 'Payment'
and return_reason_Desc is null
group by payment_Date) pay
on cal.datevalue = pay.payment_Date
where cal.datevalue between '03-01-2011' and dateadd(d,40,getdate())
order by cal.datevalue desc
Thanks
You can actually read this code? Looks like one big run-on sentence to me. I'd have to reformat the code before I even tried to figure out what it was attempting to do and as I don't have an automated tool nor the time I'll leave this to others.
I'll be honest, the first time I looked at the code it looked to me as if you had two where clauses but it appears you are using numerous derived tables in this query. Personally, I think I'd rewrite it moving the derived tables to CTEs in an attempt to make the query cleaner and easier to read.
December 29, 2011 at 6:39 am
Formatted:
SELECT cal.datevalue,
DATENAME(DW, cal.datevalue) day_of_week,
ISNULL(flowon.sum_flow_on, 0) flow_on,
ISNULL(flowend.sum_flow_end, 0) flow_off,
ISNULL(active_cnt.total_active, 0) tot_active,
ISNULL(stmt_date.stmt_cnt, 0) statements,
ISNULL(chrgs.charges, 0) current_charges,
ISNULL(pay.payments, 0) current_payments
FROM ee_internal.dbo.Calendar cal
LEFT JOIN (SELECT CAST(calcs2.flow_start_date AS DATE) flow_on_date,
SUM(1) sum_flow_on
FROM dbo.clean_cust_account_lcd_cancel_switch calcs2
WHERE calcs2.flow_start_date IS NOT NULL
GROUP BY calcs2.flow_start_date) flowon
ON cal.datevalue = flowon.flow_on_date
LEFT JOIN (SELECT CAST(calcs2.flow_end_date AS DATE) flow_end_date,
SUM(1) sum_flow_end
FROM dbo.clean_cust_account_lcd_cancel_switch calcs2
WHERE calcs2.flow_end_date IS NOT NULL
GROUP BY calcs2.flow_end_date) flowend
ON cal.datevalue = flowend.flow_end_date
LEFT JOIN (SELECT cal.datevalue active_date,
(SELECT COUNT(1)
FROM dbo.clean_cust_account_lcd_cancel_switch calcs2
WHERE calcs2.flow_start_date <= cal.datevalue
AND calcs2.flow_end_date IS NULL) total_active
FROM ee_internal.dbo.Calendar cal) active_cnt
ON cal.datevalue = active_cnt.active_date
LEFT JOIN (SELECT x.current_stmt_date,
SUM(1) stmt_cnt
FROM (SELECT DISTINCT
account_number,
current_stmt_date
FROM dbo.billing_charge) x
GROUP BY x.current_stmt_date) stmt_date
ON cal.datevalue = stmt_date.current_stmt_date
LEFT JOIN (SELECT current_stmt_date,
SUM(calc_price_amt + adjustment_amt
+ price_tax_amt) charges
FROM dbo.billing_payment
WHERE TYPE_DESC != 'Level Pay Contract'
AND type_desc != 'Transfer to Payment Contract'
AND TRANSACTION_TYPE_DESC NOT IN (
'Reapplied Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Regulated',
'Cash - Revenue - Non Regulated',
'Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Non Regulated',
'Deposit Charge - Non Regulated')
AND current_stmt_date IS NOT NULL
GROUP BY current_stmt_date) chrgs
ON cal.datevalue = chrgs.current_stmt_date
LEFT JOIN (SELECT payment_Date,
SUM(calc_price_amt + adjustment_amt
+ price_tax_amt) payments
FROM dbo.billing_payment
WHERE type_desc = 'Payment'
AND return_reason_Desc IS NULL
GROUP BY payment_Date) pay
ON cal.datevalue = pay.payment_Date
WHERE cal.datevalue BETWEEN '03-01-2011'
AND DATEADD(d, 40, GETDATE())
ORDER BY cal.datevalue DESC ;
Even formatted, it's readable, but still poorly written, as already noted. And, IMHO more importantly, it doesn't generate a syntax error when I check it, which defeats the purpose it was posted for.
But the OP seems to have abandoned the conversation, so no point in further attention on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply