Query longer in SP: return

Showing posts with label return. Show all posts

Friday, March 30, 2012

query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
******************************************
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
******************************************
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
--
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ******************************************
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ******************************************
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item will
not be the destinations but the number of events for each data item. Guess I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> > Hi I have 3 tables and am wondering if there is a way to do this. I would
> > like a qerry that returns data for a data item but will return only the #
> > of
> > records for the event and not the destination. For example if the query
> > returns data for Data ID 4
> > I would want to see as the results.
> > Data name event destination
> > report recieved office
> > report intransit frontdesk
> >
> > Even though there are 3 destinations I would like to only list the first 2
> > and base the number of records returned on the number of events. Thanks.
> >
> > table1 event
> > ******************************************
> > *pri key Count * foriegn key Data ID * event *
> > * 1 * 3 * arrived
> > *
> >
> > * 2 * 4 * recieved
> > *
> >
> > * 3 * 4 * intransit
> > *
> >
> > ******************************************
> > table2 data item
> > *****************************
> > * Pri key Data ID * Data name *
> > * 3 * email *
> >
> > * 4 * report *
> >
> > *****************************
> > table3- destination-note no prim key
> > **********************************
> > * Data ID * destination *
> > * 4 * office *
> >
> > * 4 * frontdesk *
> > * 4 * mailroom *
> >
> > **********************************
> >
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>> Hi Paul
>> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
>> and example data in a usable format, also posting the expected output
>> from
>> the data provided would be helpful e.g.
>> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
>> INSERT INTO [event] ( [Count], [Data ID], [event] )
>> SELECT 1, 3, 'arrived'
>> UNION ALL SELECT 2, 4, 'recieved'
>> UNION ALL SELECT 3, 4, 'intransit'
>> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
>> INSERT INTO [data item] ( [Data ID], [Data name] )
>> SELECT 3, 'email'
>> UNION ALL SELECT 4, 'report'
>>
>> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
>> INSERT INTO destination ( [Data ID], destination )
>> SELECT 4, 'office'
>> UNION ALL SELECT 4, 'frontdesk'
>> UNION ALL SELECT 4, 'mailroom'
>>
>> There is no way to easily distinguish your destinations e.g
>> SELECT d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> report intransit mailroom
>> report recieved office
>> report recieved mailroom
>> report recieved frontdesk
>> (6 row(s) affected)
>>
>> Limiting this to top 2
>> SELECT TOP 2 d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> (2 row(s) affected)
>> You could give destination alphabetical rank such as
>>
>> SELECT f.[Data ID], f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
>> b.destination )+1 AS RANK
>> FROM destination f
>> This may then allow you to pick different destinations by joining to the
>> event count (you may have to rank these to get similar number ranges.
>> SELECT TOP 2 d.[Data name], e.[Event], g.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN ( SELECT f.[Data ID],
>> f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
>> b.destination )+1
>> AS RANK
>> FROM destination f
>> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
>> ORDER BY e.[Count] DESC
>> Data name Event destination
>> -- -- --
>> report intransit frontdesk
>> report recieved mailroom
>> (2 row(s) affected)
>> John
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>> > Hi I have 3 tables and am wondering if there is a way to do this. I
>> > would
>> > like a qerry that returns data for a data item but will return only the
>> > #
>> > of
>> > records for the event and not the destination. For example if the
>> > query
>> > returns data for Data ID 4
>> > I would want to see as the results.
>> > Data name event destination
>> > report recieved office
>> > report intransit frontdesk
>> >
>> > Even though there are 3 destinations I would like to only list the
>> > first 2
>> > and base the number of records returned on the number of events.
>> > Thanks.
>> >
>> > table1 event
>> > ******************************************
>> > *pri key Count * foriegn key Data ID * event *
>> > * 1 * 3 * arrived
>> > *
>> >
>> > * 2 * 4 * recieved
>> > *
>> >
>> > * 3 * 4 * intransit
>> > *
>> >
>> > ******************************************
>> > table2 data item
>> > *****************************
>> > * Pri key Data ID * Data name *
>> > * 3 * email *
>> >
>> > * 4 * report *
>> >
>> > *****************************
>> > table3- destination-note no prim key
>> > **********************************
>> > * Data ID * destination *
>> > * 4 * office *
>> >
>> > * 4 * frontdesk *
>> > * 4 * mailroom *
>> >
>> > **********************************
>> >
>> >
>> >
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> > hi thanks for the response. Does seem quite useful to use DDL, will do
> > this
> > in the future. Unfortunately I will net be able to use a constant for the
> > #
> > of records returned as the number of records returned for each data item
> > will
> > not be the destinations but the number of events for each data item.
> > Guess I
> > will probably use 2 queries the first one getting the # of events for each
> > data item and the second returning the data name, event and destination
> > and
> > using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> > again,
> > Paul.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> >> Hi Paul
> >>
> >> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> >> and example data in a usable format, also posting the expected output
> >> from
> >> the data provided would be helpful e.g.
> >> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> >>
> >> INSERT INTO [event] ( [Count], [Data ID], [event] )
> >>
> >> SELECT 1, 3, 'arrived'
> >>
> >> UNION ALL SELECT 2, 4, 'recieved'
> >>
> >> UNION ALL SELECT 3, 4, 'intransit'
> >>
> >> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> >>
> >> INSERT INTO [data item] ( [Data ID], [Data name] )
> >>
> >> SELECT 3, 'email'
> >>
> >> UNION ALL SELECT 4, 'report'
> >>
> >>
> >> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> >>
> >> INSERT INTO destination ( [Data ID], destination )
> >>
> >> SELECT 4, 'office'
> >>
> >> UNION ALL SELECT 4, 'frontdesk'
> >>
> >> UNION ALL SELECT 4, 'mailroom'
> >>
> >>
> >> There is no way to easily distinguish your destinations e.g
> >>
> >> SELECT d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> report intransit mailroom
> >>
> >> report recieved office
> >>
> >> report recieved mailroom
> >>
> >> report recieved frontdesk
> >>
> >> (6 row(s) affected)
> >>
> >>
> >>
> >> Limiting this to top 2
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> (2 row(s) affected)
> >>
> >> You could give destination alphabetical rank such as
> >>
> >>
> >>
> >> SELECT f.[Data ID], f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> >> b.destination )+1 AS RANK
> >>
> >> FROM destination f
> >>
> >> This may then allow you to pick different destinations by joining to the
> >> event count (you may have to rank these to get similar number ranges.
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN ( SELECT f.[Data ID],
> >>
> >> f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
> >> b.destination )+1
> >> AS RANK
> >>
> >> FROM destination f
> >>
> >> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit frontdesk
> >>
> >> report recieved mailroom
> >>
> >> (2 row(s) affected)
> >>
> >> John
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> >> > Hi I have 3 tables and am wondering if there is a way to do this. I
> >> > would
> >> > like a qerry that returns data for a data item but will return only the
> >> > #
> >> > of
> >> > records for the event and not the destination. For example if the
> >> > query
> >> > returns data for Data ID 4
> >> > I would want to see as the results.
> >> > Data name event destination
> >> > report recieved office
> >> > report intransit frontdesk
> >> >
> >> > Even though there are 3 destinations I would like to only list the
> >> > first 2
> >> > and base the number of records returned on the number of events.
> >> > Thanks.
> >> >
> >> > table1 event
> >> > ******************************************
> >> > *pri key Count * foriegn key Data ID * event *
> >> > * 1 * 3 * arrived
> >> > *
> >> >
> >> > * 2 * 4 * recieved
> >> > *
> >> >
> >> > * 3 * 4 * intransit
> >> > *
> >> >
> >> > ******************************************
> >> > table2 data item
> >> > *****************************
> >> > * Pri key Data ID * Data name *
> >> > * 3 * email *
> >> >
> >> > * 4 * report *
> >> >
> >> > *****************************
> >> > table3- destination-note no prim key
> >> > **********************************
> >> > * Data ID * destination *
> >> > * 4 * office *
> >> >
> >> > * 4 * frontdesk *
> >> > * 4 * mailroom *
> >> >
> >> > **********************************
> >> >
> >> >
> >> >
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

query question

Monday, March 26, 2012

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Friday, March 23, 2012

Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Wednesday, March 21, 2012

Query Performance (am I in the ms right forum for performance Q's

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_idThis query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
> I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
> and I get drastically improved performance. a11 is well indexed. A new index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>

Query Performance

Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?

select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc

Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...

Sincerely,
-Lawrence

You could write it like below:

select min(num)

from A

where num > (select min(num) from A)

-- or

select max(num) from (select top 1 num from A order by num) X

But your TOP query should perform fine if you have an index on Num column. Can you compare above queries with yours and see if there is any difference? You can compare the execution plan in query analyzer.

Tuesday, March 20, 2012

Query Performance

Hello SQL Champs,
From the query below, I am using 2 TOP functions to return the desired row.
I am wondering if someone can shed some light on how to AVOID using 2 TOP
statements and combine into just one select query?
select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num de
sc
Truly Appreciate your help as this performance issue has been bugging in my
head for quite some time...
Sincerely,
-LawrenceHi,
You can find yours answer here:
http://www.geocities.com/tomaszborawski/downloads.html
Just download SQL Tips Book
"Lawrence" wrote:

> Hello SQL Champs,
> From the query below, I am using 2 TOP functions to return the desired row
.
> I am wondering if someone can shed some light on how to AVOID using 2 TOP
> statements and combine into just one select query?
> select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num
desc
> Truly Appreciate your help as this performance issue has been bugging in m
y
> head for quite some time...
> Sincerely,
> -Lawrence|||Does this give you a performance issue? I've never personally run into
this problem of selecting the second row of a result set, but it looks
like the execution for such a query shouldn't be that bad. The
alternatives that immediately come to mind seem, at first thought, to
be significantly less efficient:
SELECT * FROM A WHERE EXISTS (SELECT * FROM A WHERE Num < A.Num HAVING
COUNT(*) = 1)
or
SELECT TOP 1 * FROM A WHERE Num > (SELECT MIN(Num) FROM A) ORDER BY Num|||try this.
select x.Num from A x, A y
where x.Num >= y.Num
group by x.Num
having count(*) = 2|||Well, make sure that column Num is indexed. Then the query should have
no performance problems whatsoever.
If this is not your real query, and your real query is something like
this
select top 1 * from (
select top 22111000 Num from A order by Num
) X
order by Num desc
then you have a problem, especially if Num is a wide column. Then you
might want to consider implementing a different paging mechanism.
Gert-Jan
Lawrence wrote:
> Hello SQL Champs,
> From the query below, I am using 2 TOP functions to return the desired row
.
> I am wondering if someone can shed some light on how to AVOID using 2 TOP
> statements and combine into just one select query?
> select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num
desc
> Truly Appreciate your help as this performance issue has been bugging in m
y
> head for quite some time...
> Sincerely,
> -Lawrence|||Thanks!
This works, but I think this works under the condition that the data is
pre-ordered. If data is not pre-ordered, count(*)=2 would not provide you
with the correct result. Multiple entries of x.Num would also skew the
count(*). Using Top seems more reliable.
I do greatly appreciate your help!
-Lawrence
"Omnibuzz" wrote:

> try this.
> select x.Num from A x, A y
> where x.Num >= y.Num
> group by x.Num
> having count(*) = 2

Query Performance

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_idMarc,
reconstruct you query, not using a11.Tr_sub_type_id in (430, 433, 3530), but
use a union (all), each with its own a11.Tr_sub_type_id = condition.
Quentin
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:19F88AA4-D9D6-4B1C-BAAD-A8DE2259F182@.microsoft.com...
>I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
>
> I get a massive bookmark in the execution plans when I run the below SQL
> to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>

Wednesday, March 7, 2012

Query on 2 tables

Hello all,
If I write this query :

SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes

the return is good.

But If I the query is :

SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes

I have an error "Invalid column name.

The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table.
How to make a good query with an aliased column without error ?

Thanks for reply.
Regards.
PAB.Try changing this:
& " " &
to this:
& ' ' &|||Also the & should be replaced with +

hth|||Also the
&
should be replaced with
+.

hth|||Hello,
Thanks all. I try that solutions.
PAB.

Friday, March 30, 2012

query question

query question

Monday, March 26, 2012

Query problem

Friday, March 23, 2012

Query plan re-use on views?

Wednesday, March 21, 2012

Query Performance (am I in the ms right forum for performance Q's

Query Performance

Tuesday, March 20, 2012

Query Performance

Query Performance

Wednesday, March 7, 2012

Query on 2 tables

Query longer in SP

Blog Archive

About Me