Compute By Clause problem in SQL 2000

I am having a problem with this compute by clause

The statement is a simple select

Select col1, col2 from table1
where <where clause>
order by col1
Compute Sum(col2) by col1

I want the display to show details and group totals.

The error message I keep getting is
Server: Msg 410, Level 16, State 2, Line 1
COMPUTE clause #1 'BY' expression #1 is not in the order by list.

I have previously used the compute clause so I know it should work !!

Ay help will be appreciated - Thank you.This is working for pubs - I do not see the difference.

SELECT type, price
FROM titles
ORDER BY type
COMPUTE SUM(price) BY type|||I saw this very same example and did my query and it doesn't work. I don't have the pubs database so I created a titles table and inserted some data and then ran this same query and that too gave me the same error !!!

Originally posted by snail
This is working for pubs - I do not see the difference.

SELECT type, price
FROM titles
ORDER BY type
COMPUTE SUM(price) BY type|||Originally posted by CSC
I saw this very same example and did my query and it doesn't work. I don't have the pubs database so I created a titles table and inserted some data and then ran this same query and that too gave me the same error !!!

I am afraid you have a problem with your sql server - is it microsoft 2000?|||Yes it is Microsoft Sql Server 2000. This is really strange !!

Originally posted by snail
I am afraid you have a problem with your sql server - is it microsoft 2000?

COMPUTE and alias

I have a query which produces a set of summaries using the compute clause.
This is what I want but all the columns in the compute result set have the
column name "sum". I have tried an alias e.g.
compute sum(cola) as cola_alias
but this throws an error. The wierd thing is all the columns have a name of
sum (they are all sums). I thought column names were supposed to be unique.
I can reference them in code by their ordinal position but it's not really
ideal. I guess I am doing something wrong but I am unsure what. Regards,
Chris."Chris" <nospam@.nospam.com> wrote in message
news:OyrOySozHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I have a query which produces a set of summaries using the compute clause.
>This is what I want but all the columns in the compute result set have the
>column name "sum". I have tried an alias e.g.
> compute sum(cola) as cola_alias
> but this throws an error. The wierd thing is all the columns have a name
> of sum (they are all sums). I thought column names were supposed to be
> unique. I can reference them in code by their ordinal position but it's
> not really ideal. I guess I am doing something wrong but I am unsure what.
> Regards, Chris.
>
COMPUTE is deprecated. Use CUBE or ROLLUP instead. They are more powerful
and easier to use.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

COMPUTE and alias

I have a query which produces a set of summaries using the compute clause.
This is what I want but all the columns in the compute result set have the
column name "sum". I have tried an alias e.g.
compute sum(cola) as cola_alias
but this throws an error. The wierd thing is all the columns have a name of
sum (they are all sums). I thought column names were supposed to be unique.
I can reference them in code by their ordinal position but it's not really
ideal. I guess I am doing something wrong but I am unsure what. Regards,
Chris."Chris" <nospam@.nospam.com> wrote in message
news:OyrOySozHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I have a query which produces a set of summaries using the compute clause.
>This is what I want but all the columns in the compute result set have the
>column name "sum". I have tried an alias e.g.
> compute sum(cola) as cola_alias
> but this throws an error. The wierd thing is all the columns have a name
> of sum (they are all sums). I thought column names were supposed to be
> unique. I can reference them in code by their ordinal position but it's
> not really ideal. I guess I am doing something wrong but I am unsure what.
> Regards, Chris.
>
COMPUTE is deprecated. Use CUBE or ROLLUP instead. They are more powerful
and easier to use.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sqlsql

Complex where clause?

Hi,

I have a stored procedure with a few parameters. One of them is @.ProviderParam and it defaults to Null. If a value is passed into that parameter when the sp is called, I need to include a check for the provider in the where clause, like this:

Where <some other stuff> AND Provider = @.ProviderParam

But if nothing is passed into that parameter (and it defaults to Null), I need to do nothing with provider in the where clause. The where clause would look like this:

Where <some other stuff>

I believe the solution is to use a CASE statement in the where clause somehow, but I'm not sure how to proceed. Can anyone please help?

Thanks.

One way is to do something like:

AND ( @.ProviderParam is null or
Provider = @.ProviderParam)

If your PROVIDER column is a non-null column you also might be able to use:

AND Provider = ISNULL (@.ProviderParam, Provider)

Again, the column must be a NOT NULL column or this filter will not work correctly whenever both @.ProviderParam and Provider are null.

|||

If I understand your issue correctly, you wish to optionally provide a value for the @.ProviderParam, use it in the WHERE clause if it is available, otherwise if it is NULL, ignore @.ProviderParam. If so, this may work for you:

AND Provider = coalesce( @.ProviderParam, Provider )

As Kent indicated, Provider MUST be a NOT NULL column for this approach to work properly.

|||The advantage of coalesce is that it will also work with DB2 whereas ISNULL will not.|||

Great stuff. Thank you, both.

Provider is a null column. DB2 is not a factor. Kent's 1st solution is really elegant. It doesn't involve a function call, which is efficient, and it's so simple. Just too much elegance for me to pass by! :)

Thanks, again, for these insights.

|||

Just a note to say that the other advantage of COALESCE is that it can take an arbitrary number of arguments and returns the first one that is not null.

SET A_Value = COALESCE(First_Choice, Second_Choice, Desperate_Choice, Default_Value)

This can be useful if you have something like 3 different names you could use before giving up and using 'UNKNOWN'.

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) =
CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) = convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) = convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) => convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) => convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) = CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> > All,
> > I have a messy query with alot of where clause. here is the original
> >
> >
> > select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> >
> > tbl_1,
> > tbl_2,
> > #TempTbl
> >
> > where tbl_2.ticketnum = tbl_1.ticketnum
> > and #TempTbl.productserial = tbl_1.productserial
> > and #TempTbl.issuercode = tbl_1.issuercode
> > and #TempTbl.productcode = tbl_1.productcode
> > and tbl_2.price = #TempTbl.price
> > and convert(varchar(10),tbl_1.setldate,101) => > convert(varchar(10),#TempTbl.settlement,101)
> > and tbl_1.acctnum = #TempTbl.accountNumber
> > and #TempTbl.rownum = @.ctr
> > and tbl_2.trademethod = 'T'
> > and tbl_1.TradeType = 'S'
> > and tbl_1.status <> 'C'
> > and tbl_2.timestamp >= @.tbl_2From
> > and tbl_2.timestamp <= @.tbl_2To
> > and substring(tbl_1.misccode,8,1) = 1
> > and (substring(tbl_1.misccode,13,1)= '0' or
> > substring(tbl_1.misccode,13,1)
> > is null)
> >
> >
> > I have altered the query to jjoin on the first 2 where's like this
> >
> > select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> >
> > tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> > join #TempTbl as c on c.productserial = a.productserial
> >
> >
> > where #TempTbl.issuercode = tbl_1.issuercode
> > and #TempTbl.productcode = tbl_1.productcode
> > and tbl_2.price = #TempTbl.price
> > and convert(varchar(10),tbl_1.setldate,101) => > convert(varchar(10),#TempTbl.settlement,101)
> > and tbl_1.acctnum = #TempTbl.accountNumber
> > and #TempTbl.rownum = @.ctr
> > and tbl_2.trademethod = 'T'
> > and tbl_1.TradeType = 'S'
> > and tbl_1.status <> 'C'
> > and tbl_2.timestamp >= @.tbl_2From
> > and tbl_2.timestamp <= @.tbl_2To
> > and substring(tbl_1.misccode,8,1) = 1
> > and (substring(tbl_1.misccode,13,1)= '0' or
> > substring(tbl_1.misccode,13,1)
> > is null)
> >
> >
> >
> > In this example I still have a ton of where clause that says
> >
> > " where a.col x = b.col x
> > and
> > a.col y = b.col y"
> >
> > I think that there has to be a more efficient way of dealing with this!
> >
> > Please help!
> >
> > D
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D
"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA
|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) =
CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas

"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>