Max in select clause

I have the following code......
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM
account_t a
LEFT OUTER JOIN price_t p
ON a.ser_id = p.ser_id
GROUP BY
a.acct_nbr ,p.pric_uval_amt
Its purpose is to return an account number from one table, and from the
corresponding price table the latest unit price date and unit price.
Basically the price table
keeps a list of all products associated with an account. Each account can
have multiple products. The price table keeps regularly updated prices for
these products and whatever the date that theprice was updated. What I want
to do is to be able to get the latest (max) unit price date and the
corresponding unit price value.
The code above gives me the latest unit price date (if I take out the
,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
the prices at all previous dates.
What I tend to get is this......
acct_nbr unit price
unit_price_date
----
0001 90
05/05/2005
0001 98
05/06/2005
0001 91
05/07/2005
0002 43
05/05/2005
0002 45
05/06/2005
When what I want is this.........
acct_nbr unit price
unit_price_date
----
0001 91
05/07/2005
0002 45
05/06/2005
Any idea's ?Hi
Is this the query that you are looking for:
SELECT account_t.acct_nbr, account_t.[unit price], derived.Unit_Price_Date
FROM account_t
INNER JOIN (
SELECT a.acct_nbr,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM account_t a
LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id
GROUP BY a.acct_nbr
) derived
ON derived.Unit_Price_Date = account_t.Unit_Price_Date AND
derived.acct_nbr = account_t.acct_nbr
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Try,
Try,
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,p.pric_asof_dt AS 'Unit_Price_Date'
FROM
account_t as a
LEFT OUTER JOIN
price_t as p
ON a.ser_id = p.ser_id
where
p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
p1.ser_id = a.ser_id)
AMB
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Thanks everyone for helping.
Alejandro, used your solution worked well. Thanks a lot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B65F1A34-7B16-46BB-B371-6143E989ACC5@.microsoft.com...
> Try,
> Try,
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,p.pric_asof_dt AS 'Unit_Price_Date'
> FROM
> account_t as a
> LEFT OUTER JOIN
> price_t as p
> ON a.ser_id = p.ser_id
> where
> p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
> p1.ser_id = a.ser_id)
>
> AMB
>
> "quiglepops" wrote:
>
can
for
want
get

max date select stmt problem

Hello Everybody,

I have a problem, with select stmt:

SELECT TOP 15 *
FROM oaVIEW_MainData AS TOP_VIEW,
oaLanguageData_TAB AS RwQualifierJoin with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin.text_id = c_cfgRegPoint
AND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)
FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB AS
RwQualifierJoin1 with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin1.text_id = c_cfgRegPoint
AND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)
order by c_dateTime desc

Please consider:
- top doesn't metter, if I will use one or 10000 result is always the
same.
- oaVIEW_MainData, is a view on major big table, holding lot of records
joinden with small table containing configuration data, over left outer
join; both tables are with nolock option,
- quersy supose to return last record from major table/view, in given
time, additionaly, with other where conditions (like in this case with
text),
- on major table, are indexes which one is on id field (not used in
this query at all), which is a pk clustered, and other is on dateEvt
(c_dateTime) which is a desc index with fill level 90%
- table has also other indexes, on three different fields, one of
theses is dsmIdent,

Now, if I'm using max(id) works very fast, and ok for me, but the
problem is, I should not use id, because might be, that the records
will be written in the table with random order, so the only one saying
which is newest, will be dateEvt.

Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
to get result. What is much more funny, server is totaly busy with this
query, and it's procesor jumps on 100%.

Now, because the query is builded dynamicly, by a user selections,
that's why we decided on such a parser ... problem is, it is not
working :(

Can I change index on dateEvt somehow, to sped this up?
Maybe construct query somehow different, to get this over max() date?

Please help

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

- oaVIEW_MainData, is a view on major big table, holding lot of records
joinden with small table containing configuration data, over left outer
join; both tables are with nolock option,

NOLOCK in a view? That's about criminal in my opinion.

Quote:

Originally Posted by

- on major table, are indexes which one is on id field (not used in
this query at all), which is a pk clustered, and other is on dateEvt
(c_dateTime) which is a desc index with fill level 90%
- table has also other indexes, on three different fields, one of
theses is dsmIdent,
>
Now, if I'm using max(id) works very fast, and ok for me, but the
problem is, I should not use id, because might be, that the records
will be written in the table with random order, so the only one saying
which is newest, will be dateEvt.
>
Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
to get result. What is much more funny, server is totaly busy with this
query, and it's procesor jumps on 100%.

Well, the easy fix would be to make the index on dateEvt() clustered
rather than the index on id. That may of course have repercussions
elsewhere.

The query looks funny to me, as it repeats the entire outer query in
the subquery. Somehome I feel that that should not be necessary. But
to say for sure I would need to know the view definition and the
definition of the underlying tables, including their key and check
constraints.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Max date from three different columns

I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
EricAre all these columns in the one table? If so, you want to unpivot:
select
max (case x.seq
when 1 then Col1
when 2 then Col2
when 3 then Col3
end)
from
(
select 1 union all
select 2 union all
select 3
) x (seq)
cross join
MyTable
If these are across 3 tables, try:
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
Eric|||> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
Eeks, is this supposed to be a cross join? How are these three tables
related? Did you mean to query against columns in three different tables,
or against three different columns in the same table? Did you want the MAX
date from any of the three columns in ALL the rows of the table, or the
greater of the three column values in every row in the table?
Can you provide decent requirements so that we don't have to ask 40
questions to figure out what you're talking about? Please see
http://www.aspfaq.com/5006|||select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
BEAUTIFUL!!!1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> select
> max (case x.seq
> when 1 then Col1
> when 2 then Col2
> when 3 then Col3
> end)
> from
> (
> select 1 union all
> select 2 union all
> select 3
> ) x (seq)
> cross join
> MyTable
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||What is it that you're really trying to achieve? We're seeing cross joins
over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
of sample data + desired results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||Well, cross joins certainly aren't the answer. If you can't produce a
simplified DDL then we can't be of much help.
Help us help you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:u9HCy3mXGHA.1200@.TK2MSFTNGP03.phx.gbl...
I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||I would use a case expression:
case
when b.date1<=a.date1 and c.date1<=a.date1 then a.date1
when a.date1<=b.date1 and c.date1<=b.date1 then b.date1
when b.date1<=c.date1 and a.date1<=c.date1 then c.date1
end
BTW, as a rule of thumb, use UNION ALL, not UNION whenever possible -
usually performs better.|||CREATE TABLE C1(F DATETIME)
INSERT INTO C1 SELECT '2005-04-05'
CREATE TABLE C2(F DATETIME)
INSERT INTO C2 SELECT '2004-02-01'
CREATE TABLE C3(F DATETIME)
INSERT INTO C3 SELECT '2006-4-13'
SELECT MAX(X.F) AS F FROM
(
SELECT MAX(C1.F) AS F FROM C1
UNION ALL
SELECT MAX(C2.F) AS F FROM C2
UNION ALL
SELECT MAX(C3.F) AS F FROM C3
) X
DROP TABLE C1
DROP TABLE C2
DROP TABLE C3
"Eric Stott" wrote:

> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>
>

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
Stefan
Stefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan
|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Max + 1 records in a table....

New bie, I'm using max+1 in select query to get the maximum no of rowz in a
table. Thru this i'm generating client ID. For this i used to insert atleast
one row in a table while quering . Now i have to generate 1 as for first
inserting row. then subsequent rows as max+1.
--
Mahe~SELECT IsNull(max(clientId),0)+1
FROM ...
But WHY dont you use IDENTITY instead?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"mahesh" <mfcmahesh@.hotmail.com> wrote in message
news:uXaHWl8GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> New bie, I'm using max+1 in select query to get the maximum no of rowz in
> a
> table. Thru this i'm generating client ID. For this i used to insert
> atleast
> one row in a table while quering . Now i have to generate 1 as for first
> inserting row. then subsequent rows as max+1.
> --
> Mahe~
>|||Identity ?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:utC2c38GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> SELECT IsNull(max(clientId),0)+1
> FROM ...
> But WHY dont you use IDENTITY instead?
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "mahesh" <mfcmahesh@.hotmail.com> wrote in message
> news:uXaHWl8GFHA.3244@.TK2MSFTNGP09.phx.gbl...
in
>|||http://msdn.microsoft.com/library/d...br />
3iex.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"mahesh" <mfcmahesh@.hotmail.com> wrote in message
news:eMSnjB9GFHA.3440@.TK2MSFTNGP10.phx.gbl...
> Identity ?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:utC2c38GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> in
>|||Thkz Thomaz, if u dont mind,where i can find more example for sql server
related concepts and queries..
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:#adBTH9GFHA.4060@.TK2MSFTNGP14.phx.gbl...
>
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
ia-iz_3iex.asp
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "mahesh" <mfcmahesh@.hotmail.com> wrote in message
> news:eMSnjB9GFHA.3440@.TK2MSFTNGP10.phx.gbl...
rowz
>|||Roji posted a link to Books Online, which should also be available on
your PC if you have SQL Server installed. Books Online is the first
place to look for any SQL Server related info.
More resources here:
http://www.microsoft.com/sql/commun...communities.asp
http://www.aspfaq.com/2423
David Portas
SQL Server MVP
--

Matrix Subtotal is not summing

Hi,
I got a matrix, and a subtotal column (R-click on the column field, and
select "subtotal"), but at runtime the column is showing the value of the
first column, Matrix Subtotal is not summing.
Why is that.. ?
help please.
the same is when i make a subtotal for rows...I solved using SUM() in the data field of the matrix.
Tx.
"JuanCG_Col" wrote:
> Hi,
> I got a matrix, and a subtotal column (R-click on the column field, and
> select "subtotal"), but at runtime the column is showing the value of the
> first column, Matrix Subtotal is not summing.
> Why is that.. ?
> help please.
> the same is when i make a subtotal for rows...

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.