old sql2k5 folder: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

One more - SQL query

Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Hello group
My english is very poor.
I'm beginner in sql and I've problem?
How get from table only first and last record with idPerson.
example:
Tables in SQL
EventLog
idPerson LogTime IdKomp
1 9:00 1
2 9:10 2
2 9:15 4
3 10:00 1
4 11:00 3
2 13:00 2
1 15:00 3
KompTable
IdKomp KompName
1 uranus
2 jupiter
3 mars
4 saturn
PersonTable
IdPerson PersonName
1 Jon
2 Bob
3 Tom
Select ..... ('?) order by idPerson
result:
PersonName MinTime KompName MaxTime KompName
Jon 9:00 uranus 15:00 mars
Bob 9:10 jupiter 13:00 jupiter
Tom 10:00 uranus (null) (null)
...
How join this table, meybe create temporary table.
Please help.Here is one solution.
Create a in-line view / table, which has the values you are after. Called th
is E_List.
Then join the EventLog table to it (twice), to get the details.
Then it's joining the other tables off to get the dispalyable results.
You could create the in-line view as an actual view.
SELECT P.PersonName, E1.LogTime, K1.KompName, E2.LogTime, K2.KompName
FROM ( SELECT EventLog.idPerson, MIN(EventLog.logtime) AS min_time, MAX(Even
tLog.logtime) AS max_time
FROM EventLog
GROUP BY EventLog.idPerson) AS E_List
JOIN EventLog AS E1 ON (E_List.idPerson = E1.idPerson AND E_List.Min_Time =
E1.LogTime)
JOIN EventLog AS E2 ON (E_List.idPerson = E2.idPerson AND E_List.Max_Time =
E2.LogTime)
JOIN PersonTable P ON (P.idPerson = E1.idPerson)
JOIN KompTable K1 ON (E1.idKomp = K1.IdKomp)
JOIN KompTable K2 ON (E2.idKomp = K2.IdKomp)|||Thanks LUV SQL
This is simple and work very good.
Good for You
PawelR
LUV SQL wrote:
> Here is one solution.
> Create a in-line view / table, which has the values you are after. Called
this E_List.
> Then join the EventLog table to it (twice), to get the details.
> Then it's joining the other tables off to get the dispalyable results.
> You could create the in-line view as an actual view.
> SELECT P.PersonName, E1.LogTime, K1.KompName, E2.LogTime, K2.KompName
> FROM ( SELECT EventLog.idPerson, MIN(EventLog.logtime) AS min_time, MAX(Ev
entLog.logtime) AS max_time
> FROM EventLog
> GROUP BY EventLog.idPerson) AS E_List
> JOIN EventLog AS E1 ON (E_List.idPerson = E1.idPerson AND E_List.Min_T
ime = E1.LogTime)
> JOIN EventLog AS E2 ON (E_List.idPerson = E2.idPerson AND E_List.Max_T
ime = E2.LogTime)
> JOIN PersonTable P ON (P.idPerson = E1.idPerson)
> JOIN KompTable K1 ON (E1.idKomp = K1.IdKomp)
> JOIN KompTable K2 ON (E2.idKomp = K2.IdKomp)

One more - SQL query

One hell of a problem (SQL2K)

Good thought, I didn't think to check there.
This also happens when querying through ODBC connections. And query
analyzer. I don't even know where to begin.

Any more ideas?
And thanks so much for replying!
"Sue Hoegemeier" wrote:

> If you both are executing this through Query Analyzer, I'd
> start by making sure you have the same connection settings,
> properties on both PCs. From the menu in Query Analyzer,
> Tools -> Options.
> -Sue
> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
different object owners?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> So...we have a SQL server and we are using a query to build a basis for a
> report. We just recently noticed that depending on which machine you run the
> query from you get a different answer.
> Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> She gets like 6 returns and I get 189. What would casuse this? Where
> should I start looking?
> Here's the query:
>
> SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
> WHERE P.PROJECTID IN(
> SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG1 6',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142 ',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISH E',
> 'FHEDISHG')
> AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL
>
> UNION
> SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> FROM
> (SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
> WHERE ATTRVALUE IN
> (SELECT ATTRVALUE FROM ACTIONATTR
> WHERE ATTRNAME IN ('SERIAL')
> AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> GROUP BY ATTRVALUE
> HAVING COUNT(ATTRVALUE) >1)
> AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL)K
>
|||I'm using the same login with both.
Any other thoughts?
"Tibor Karaszi" wrote:

> Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
> different object owners?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Karl" <Karl@.discussions.microsoft.com> wrote in message
> news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
>
|||Any differences when you look at the two in syscacheobjects?
I would guess you would find two different bucket ids to
identify them - you might get some clues with the values
there.
Any differences when you look at the execution plans on each
PC?
Other than that, have you tried just first executing each
select that are being unioned to narrow it down some? Or are
both results different on the two machines where you can
reproduce this issue.
-Sue
On Tue, 3 Oct 2006 09:00:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue - good thoughts. This is happening all across out network. Some
>machines get 35 results and some get 189. We noticed because we switched
>from Crystal X to Crystal XI (they are on different servers).
>The connection settings in query analyzer are the same. All default.

>Good thought, I didn't think to check there.
>This also happens when querying through ODBC connections. And query
>analyzer. I don't even know where to begin.

>Any more ideas?
>And thanks so much for replying!
>"Sue Hoegemeier" wrote:
|||How do I find my specific query in syschacheobjects? There's 20,000 items in
there or so.........
And what do you mean by differences in the execution plans on each PC? I
guess I don't get what your asking here.
Thanks so much for you help so far!!
(waiting for the other user to get back to run the selects seperately)
"Sue Hoegemeier" wrote:

> Any differences when you look at the two in syscacheobjects?
> I would guess you would find two different bucket ids to
> identify them - you might get some clues with the values
> there.
> Any differences when you look at the execution plans on each
> PC?
> Other than that, have you tried just first executing each
> select that are being unioned to narrow it down some? Or are
> both results different on the two machines where you can
> reproduce this issue.
> -Sue
> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||1. Something like:
select *
from master..syscacheobjects
where sql like '<beginning of your sql statement> %'
2. Display the execution plan for the query on each PC. If
there is a difference, that might lead you to the issue.
In Query Analyzer, from the menu go to Query and select
Display Estimated Execution Plan.
-Sue
On Tue, 3 Oct 2006 12:40:02 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>How do I find my specific query in syschacheobjects? There's 20,000 items in
>there or so.........
>And what do you mean by differences in the execution plans on each PC? I
>guess I don't get what your asking here.
>Thanks so much for you help so far!!
>(waiting for the other user to get back to run the selects seperately)
>"Sue Hoegemeier" wrote:
|||1. -They both look good.........
2. The execution plan looks identical too.
Damn.......
"Sue Hoegemeier" wrote:

> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||We are getting different results in the select statements - I broke it down
into two queries and we are getting different results there too. My
co-worker is getting 39, and then 23 while I am getting 276 and then 158.
Thats "alittle bit" of a difference.

"Sue Hoegemeier" wrote:

> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>

One hell of a problem (SQL2K)

Good thought, I didn't think to check there.
This also happens when querying through ODBC connections. And query
analyzer. I don't even know where to begin.

Any more ideas?
And thanks so much for replying!
"Sue Hoegemeier" wrote:

> If you both are executing this through Query Analyzer, I'd
> start by making sure you have the same connection settings,
> properties on both PCs. From the menu in Query Analyzer,
> Tools -> Options.
> -Sue
> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>|||Are you using the same login? Perhaps the same tables aren't hit, because sa
me tables exists with
different object owners?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> So...we have a SQL server and we are using a query to build a basis for a
> report. We just recently noticed that depending on which machine you run
the
> query from you get a different answer.
> Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> She gets like 6 returns and I get 189. What would casuse this? Where
> should I start looking?
> Here's the query:
>
> SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL')
)
> ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
> WHERE P.PROJECTID IN(
> SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG14
2','ECWEG16'
,
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18'
,'ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG
','FHEDISHE',
> 'FHEDISHG')
> AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL
>
> UNION
> SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> FROM
> (SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
> WHERE ATTRVALUE IN
> (SELECT ATTRVALUE FROM ACTIONATTR
> WHERE ATTRNAME IN ('SERIAL')
> AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> GROUP BY ATTRVALUE
> HAVING COUNT(ATTRVALUE) >1)
> AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL)K
>|||I'm using the same login with both.
Any other thoughts?
"Tibor Karaszi" wrote:

> Are you using the same login? Perhaps the same tables aren't hit, because
same tables exists with
> different object owners?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Karl" <Karl@.discussions.microsoft.com> wrote in message
> news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
>|||Any differences when you look at the two in syscacheobjects?
I would guess you would find two different bucket ids to
identify them - you might get some clues with the values
there.
Any differences when you look at the execution plans on each
PC?
Other than that, have you tried just first executing each
select that are being unioned to narrow it down some? Or are
both results different on the two machines where you can
reproduce this issue.
-Sue
On Tue, 3 Oct 2006 09:00:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue - good thoughts. This is happening all across out network. Some
>machines get 35 results and some get 189. We noticed because we switched
>from Crystal X to Crystal XI (they are on different servers).
>The connection settings in query analyzer are the same. All default.

>Good thought, I didn't think to check there.
>This also happens when querying through ODBC connections. And query
>analyzer. I don't even know where to begin.

>Any more ideas?
>And thanks so much for replying!
>"Sue Hoegemeier" wrote:
>|||How do I find my specific query in syschacheobjects? There's 20,000 items i
n
there or so.........
And what do you mean by differences in the execution plans on each PC? I
guess I don't get what your asking here.
Thanks so much for you help so far!!
(waiting for the other user to get back to run the selects seperately)
"Sue Hoegemeier" wrote:

> Any differences when you look at the two in syscacheobjects?
> I would guess you would find two different bucket ids to
> identify them - you might get some clues with the values
> there.
> Any differences when you look at the execution plans on each
> PC?
> Other than that, have you tried just first executing each
> select that are being unioned to narrow it down some? Or are
> both results different on the two machines where you can
> reproduce this issue.
> -Sue
> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>|||1. Something like:
select *
from master..syscacheobjects
where sql like '<beginning of your sql statement> %'
2. Display the execution plan for the query on each PC. If
there is a difference, that might lead you to the issue.
In Query Analyzer, from the menu go to Query and select
Display Estimated Execution Plan.
-Sue
On Tue, 3 Oct 2006 12:40:02 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>How do I find my specific query in syschacheobjects? There's 20,000 items
in
>there or so.........
>And what do you mean by differences in the execution plans on each PC? I
>guess I don't get what your asking here.
>Thanks so much for you help so far!!
>(waiting for the other user to get back to run the selects seperately)
>"Sue Hoegemeier" wrote:
>|||1. -They both look good.........
2. The execution plan looks identical too.
Damn.......
"Sue Hoegemeier" wrote:

> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>|||We are getting different results in the select statements - I broke it down
into two queries and we are getting different results there too. My
co-worker is getting 39, and then 23 while I am getting 276 and then 158.
Thats "alittle bit" of a difference.

One hell of a problem (SQL2K)

So...we have a SQL server and we are using a query to build a basis for a
report. We just recently noticed that depending on which machine you run the
query from you get a different answer.
Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
She gets like 6 returns and I get 189. What would casuse this? Where
should I start looking?
Here's the query:
SELECT P.CREATEDDT, P.CREATEDBY,
'MEASURE' OBJECT_TYPE,
P.PROJECTID OBJECTID,
P.PROJECTID,
P.TITLE,
' ' OTHER,
'Missing Make Model or Serial' ERROR,
P.PMC,
TS.STATUS,
TS.TRANSACTIONID,
''OTHER_PROJ,''OTHER_TITLE
FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
LMI.INCENTIVEID = TS.INCENTIVEID
WHERE P.PROJECTID IN(
SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID
WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
'FHEDISHG')
AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
<>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
AND IMPORTEDDT IS NULL
UNION
SELECT *,
(SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID
WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
(SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID
INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
FROM
(SELECT P.CREATEDDT,P.CREATEDBY,
'MEASURE' TYPE,
ATT.MEASUREID,
P.PROJECTID,
P.TITLE,
ATTRVALUE OTHER,
'Duplicate Serial Numbers' ERROR,
P.PMC,
TS.STATUS,
TS.TRANSACTIONID
FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
P.PROJECTID = TS.PROJECTID
WHERE ATTRVALUE IN
(SELECT ATTRVALUE FROM ACTIONATTR
WHERE ATTRNAME IN ('SERIAL')
AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
GROUP BY ATTRVALUE
HAVING COUNT(ATTRVALUE) >1)
AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
<>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
AND IMPORTEDDT IS NULL)KIf you both are executing this through Query Analyzer, I'd
start by making sure you have the same connection settings,
properties on both PCs. From the menu in Query Analyzer,
Tools -> Options.
-Sue
On Mon, 2 Oct 2006 16:41:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
>So...we have a SQL server and we are using a query to build a basis for a
>report. We just recently noticed that depending on which machine you run the
>query from you get a different answer.
>Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
>Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
>She gets like 6 returns and I get 189. What would casuse this? Where
>should I start looking?
>Here's the query:
>
>SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
>FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
>ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
>WHERE P.PROJECTID IN(
>SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
>WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> 'FHEDISHG')
>AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
>AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>AND IMPORTEDDT IS NULL
>
>UNION
>SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
>FROM
>(SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
>FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
>ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
>WHERE ATTRVALUE IN
>(SELECT ATTRVALUE FROM ACTIONATTR
>WHERE ATTRNAME IN ('SERIAL')
>AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
>GROUP BY ATTRVALUE
>HAVING COUNT(ATTRVALUE) >1)
>AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>AND IMPORTEDDT IS NULL)K
>|||Sue - good thoughts. This is happening all across out network. Some
machines get 35 results and some get 189. We noticed because we switched
from Crystal X to Crystal XI (they are on different servers).
The connection settings in query analyzer are the same. All default. :(
Good thought, I didn't think to check there.
This also happens when querying through ODBC connections. And query
analyzer. I don't even know where to begin. :(
Any more ideas?
And thanks so much for replying!
"Sue Hoegemeier" wrote:
> If you both are executing this through Query Analyzer, I'd
> start by making sure you have the same connection settings,
> properties on both PCs. From the menu in Query Analyzer,
> Tools -> Options.
> -Sue
> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
> >So...we have a SQL server and we are using a query to build a basis for a
> >report. We just recently noticed that depending on which machine you run the
> >query from you get a different answer.
> >
> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >
> >She gets like 6 returns and I get 189. What would casuse this? Where
> >should I start looking?
> >
> >Here's the query:
> >
> >
> >SELECT P.CREATEDDT, P.CREATEDBY,
> > 'MEASURE' OBJECT_TYPE,
> > P.PROJECTID OBJECTID,
> > P.PROJECTID,
> > P.TITLE,
> > ' ' OTHER,
> > 'Missing Make Model or Serial' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID,
> > ''OTHER_PROJ,''OTHER_TITLE
> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> >ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > LMI.INCENTIVEID = TS.INCENTIVEID
> >WHERE P.PROJECTID IN(
> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > 'FHEDISHG')
> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >AND IMPORTEDDT IS NULL
> >
> >
> >
> >UNION
> >
> >SELECT *,
> >
> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >ATT.MEASUREID = ACS.MEASUREID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >ATT.MEASUREID = ACS.MEASUREID
> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> >FROM
> >(SELECT P.CREATEDDT,P.CREATEDBY,
> > 'MEASURE' TYPE,
> > ATT.MEASUREID,
> > P.PROJECTID,
> > P.TITLE,
> > ATTRVALUE OTHER,
> > 'Duplicate Serial Numbers' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID
> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> >ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > P.PROJECTID = TS.PROJECTID
> >WHERE ATTRVALUE IN
> >(SELECT ATTRVALUE FROM ACTIONATTR
> >WHERE ATTRNAME IN ('SERIAL')
> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> >GROUP BY ATTRVALUE
> >HAVING COUNT(ATTRVALUE) >1)
> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >AND IMPORTEDDT IS NULL)K
> >
>|||Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
different object owners?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> So...we have a SQL server and we are using a query to build a basis for a
> report. We just recently noticed that depending on which machine you run the
> query from you get a different answer.
> Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> She gets like 6 returns and I get 189. What would casuse this? Where
> should I start looking?
> Here's the query:
>
> SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
> WHERE P.PROJECTID IN(
> SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> 'FHEDISHG')
> AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL
>
> UNION
> SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> FROM
> (SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
> WHERE ATTRVALUE IN
> (SELECT ATTRVALUE FROM ACTIONATTR
> WHERE ATTRNAME IN ('SERIAL')
> AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> GROUP BY ATTRVALUE
> HAVING COUNT(ATTRVALUE) >1)
> AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL)K
>|||I'm using the same login with both.
Any other thoughts?
"Tibor Karaszi" wrote:
> Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
> different object owners?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Karl" <Karl@.discussions.microsoft.com> wrote in message
> news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> > So...we have a SQL server and we are using a query to build a basis for a
> > report. We just recently noticed that depending on which machine you run the
> > query from you get a different answer.
> >
> > Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> > Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >
> > She gets like 6 returns and I get 189. What would casuse this? Where
> > should I start looking?
> >
> > Here's the query:
> >
> >
> > SELECT P.CREATEDDT, P.CREATEDBY,
> > 'MEASURE' OBJECT_TYPE,
> > P.PROJECTID OBJECTID,
> > P.PROJECTID,
> > P.TITLE,
> > ' ' OTHER,
> > 'Missing Make Model or Serial' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID,
> > ''OTHER_PROJ,''OTHER_TITLE
> > FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> > ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > LMI.INCENTIVEID = TS.INCENTIVEID
> > WHERE P.PROJECTID IN(
> > SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > 'FHEDISHG')
> > AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> > AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> > <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> > AND IMPORTEDDT IS NULL
> >
> >
> >
> > UNION
> >
> > SELECT *,
> >
> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> > FROM
> > (SELECT P.CREATEDDT,P.CREATEDBY,
> > 'MEASURE' TYPE,
> > ATT.MEASUREID,
> > P.PROJECTID,
> > P.TITLE,
> > ATTRVALUE OTHER,
> > 'Duplicate Serial Numbers' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID
> > FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> > ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > P.PROJECTID = TS.PROJECTID
> > WHERE ATTRVALUE IN
> > (SELECT ATTRVALUE FROM ACTIONATTR
> > WHERE ATTRNAME IN ('SERIAL')
> > AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> > GROUP BY ATTRVALUE
> > HAVING COUNT(ATTRVALUE) >1)
> > AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> > <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> > AND IMPORTEDDT IS NULL)K
> >
> >
>|||Any differences when you look at the two in syscacheobjects?
I would guess you would find two different bucket ids to
identify them - you might get some clues with the values
there.
Any differences when you look at the execution plans on each
PC?
Other than that, have you tried just first executing each
select that are being unioned to narrow it down some? Or are
both results different on the two machines where you can
reproduce this issue.
-Sue
On Tue, 3 Oct 2006 09:00:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
>Sue - good thoughts. This is happening all across out network. Some
>machines get 35 results and some get 189. We noticed because we switched
>from Crystal X to Crystal XI (they are on different servers).
>The connection settings in query analyzer are the same. All default. :(
>Good thought, I didn't think to check there.
>This also happens when querying through ODBC connections. And query
>analyzer. I don't even know where to begin. :(
>Any more ideas?
>And thanks so much for replying!
>"Sue Hoegemeier" wrote:
>> If you both are executing this through Query Analyzer, I'd
>> start by making sure you have the same connection settings,
>> properties on both PCs. From the menu in Query Analyzer,
>> Tools -> Options.
>> -Sue
>> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
>> <Karl@.discussions.microsoft.com> wrote:
>> >So...we have a SQL server and we are using a query to build a basis for a
>> >report. We just recently noticed that depending on which machine you run the
>> >query from you get a different answer.
>> >
>> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
>> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
>> >
>> >She gets like 6 returns and I get 189. What would casuse this? Where
>> >should I start looking?
>> >
>> >Here's the query:
>> >
>> >
>> >SELECT P.CREATEDDT, P.CREATEDBY,
>> > 'MEASURE' OBJECT_TYPE,
>> > P.PROJECTID OBJECTID,
>> > P.PROJECTID,
>> > P.TITLE,
>> > ' ' OTHER,
>> > 'Missing Make Model or Serial' ERROR,
>> > P.PMC,
>> > TS.STATUS,
>> > TS.TRANSACTIONID,
>> > ''OTHER_PROJ,''OTHER_TITLE
>> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
>> >ATT INNER JOIN ACTIONSUMMARY ACS ON
>> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
>> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> > LMI.INCENTIVEID = TS.INCENTIVEID
>> >WHERE P.PROJECTID IN(
>> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> > ATT.MEASUREID = ACS.MEASUREID
>> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
>> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> > 'FHEDISHG')
>> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
>> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
>> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
>> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >AND IMPORTEDDT IS NULL
>> >
>> >
>> >
>> >UNION
>> >
>> >SELECT *,
>> >
>> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >ATT.MEASUREID = ACS.MEASUREID
>> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
>> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >ATT.MEASUREID = ACS.MEASUREID
>> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
>> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
>> >FROM
>> >(SELECT P.CREATEDDT,P.CREATEDBY,
>> > 'MEASURE' TYPE,
>> > ATT.MEASUREID,
>> > P.PROJECTID,
>> > P.TITLE,
>> > ATTRVALUE OTHER,
>> > 'Duplicate Serial Numbers' ERROR,
>> > P.PMC,
>> > TS.STATUS,
>> > TS.TRANSACTIONID
>> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
>> >ACTIONSUMMARY ACS ON
>> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> > P.PROJECTID = TS.PROJECTID
>> >WHERE ATTRVALUE IN
>> >(SELECT ATTRVALUE FROM ACTIONATTR
>> >WHERE ATTRNAME IN ('SERIAL')
>> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
>> >GROUP BY ATTRVALUE
>> >HAVING COUNT(ATTRVALUE) >1)
>> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
>> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >AND IMPORTEDDT IS NULL)K
>> >
>>|||How do I find my specific query in syschacheobjects? There's 20,000 items in
there or so.........
And what do you mean by differences in the execution plans on each PC? I
guess I don't get what your asking here.
Thanks so much for you help so far!!
(waiting for the other user to get back to run the selects seperately)
"Sue Hoegemeier" wrote:
> Any differences when you look at the two in syscacheobjects?
> I would guess you would find two different bucket ids to
> identify them - you might get some clues with the values
> there.
> Any differences when you look at the execution plans on each
> PC?
> Other than that, have you tried just first executing each
> select that are being unioned to narrow it down some? Or are
> both results different on the two machines where you can
> reproduce this issue.
> -Sue
> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
> >Sue - good thoughts. This is happening all across out network. Some
> >machines get 35 results and some get 189. We noticed because we switched
> >from Crystal X to Crystal XI (they are on different servers).
> >
> >The connection settings in query analyzer are the same. All default. :(
> >Good thought, I didn't think to check there.
> >
> >This also happens when querying through ODBC connections. And query
> >analyzer. I don't even know where to begin. :(
> >
> >Any more ideas?
> >
> >And thanks so much for replying!
> >
> >"Sue Hoegemeier" wrote:
> >
> >> If you both are executing this through Query Analyzer, I'd
> >> start by making sure you have the same connection settings,
> >> properties on both PCs. From the menu in Query Analyzer,
> >> Tools -> Options.
> >>
> >> -Sue
> >>
> >> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> >> <Karl@.discussions.microsoft.com> wrote:
> >>
> >> >So...we have a SQL server and we are using a query to build a basis for a
> >> >report. We just recently noticed that depending on which machine you run the
> >> >query from you get a different answer.
> >> >
> >> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> >> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >> >
> >> >She gets like 6 returns and I get 189. What would casuse this? Where
> >> >should I start looking?
> >> >
> >> >Here's the query:
> >> >
> >> >
> >> >SELECT P.CREATEDDT, P.CREATEDBY,
> >> > 'MEASURE' OBJECT_TYPE,
> >> > P.PROJECTID OBJECTID,
> >> > P.PROJECTID,
> >> > P.TITLE,
> >> > ' ' OTHER,
> >> > 'Missing Make Model or Serial' ERROR,
> >> > P.PMC,
> >> > TS.STATUS,
> >> > TS.TRANSACTIONID,
> >> > ''OTHER_PROJ,''OTHER_TITLE
> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> >> >ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> >> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> > LMI.INCENTIVEID = TS.INCENTIVEID
> >> >WHERE P.PROJECTID IN(
> >> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> > ATT.MEASUREID = ACS.MEASUREID
> >> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> > 'FHEDISHG')
> >> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> >> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> >> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> >> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >AND IMPORTEDDT IS NULL
> >> >
> >> >
> >> >
> >> >UNION
> >> >
> >> >SELECT *,
> >> >
> >> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >ATT.MEASUREID = ACS.MEASUREID
> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> >> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >ATT.MEASUREID = ACS.MEASUREID
> >> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> >> >FROM
> >> >(SELECT P.CREATEDDT,P.CREATEDBY,
> >> > 'MEASURE' TYPE,
> >> > ATT.MEASUREID,
> >> > P.PROJECTID,
> >> > P.TITLE,
> >> > ATTRVALUE OTHER,
> >> > 'Duplicate Serial Numbers' ERROR,
> >> > P.PMC,
> >> > TS.STATUS,
> >> > TS.TRANSACTIONID
> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> >> >ACTIONSUMMARY ACS ON
> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> > P.PROJECTID = TS.PROJECTID
> >> >WHERE ATTRVALUE IN
> >> >(SELECT ATTRVALUE FROM ACTIONATTR
> >> >WHERE ATTRNAME IN ('SERIAL')
> >> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> >> >GROUP BY ATTRVALUE
> >> >HAVING COUNT(ATTRVALUE) >1)
> >> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> >> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >AND IMPORTEDDT IS NULL)K
> >> >
> >>
> >>
>|||1. Something like:
select *
from master..syscacheobjects
where sql like '<beginning of your sql statement> %'
2. Display the execution plan for the query on each PC. If
there is a difference, that might lead you to the issue.
In Query Analyzer, from the menu go to Query and select
Display Estimated Execution Plan.
-Sue
On Tue, 3 Oct 2006 12:40:02 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
>How do I find my specific query in syschacheobjects? There's 20,000 items in
>there or so.........
>And what do you mean by differences in the execution plans on each PC? I
>guess I don't get what your asking here.
>Thanks so much for you help so far!!
>(waiting for the other user to get back to run the selects seperately)
>"Sue Hoegemeier" wrote:
>> Any differences when you look at the two in syscacheobjects?
>> I would guess you would find two different bucket ids to
>> identify them - you might get some clues with the values
>> there.
>> Any differences when you look at the execution plans on each
>> PC?
>> Other than that, have you tried just first executing each
>> select that are being unioned to narrow it down some? Or are
>> both results different on the two machines where you can
>> reproduce this issue.
>> -Sue
>> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
>> <Karl@.discussions.microsoft.com> wrote:
>> >Sue - good thoughts. This is happening all across out network. Some
>> >machines get 35 results and some get 189. We noticed because we switched
>> >from Crystal X to Crystal XI (they are on different servers).
>> >
>> >The connection settings in query analyzer are the same. All default. :(
>> >Good thought, I didn't think to check there.
>> >
>> >This also happens when querying through ODBC connections. And query
>> >analyzer. I don't even know where to begin. :(
>> >
>> >Any more ideas?
>> >
>> >And thanks so much for replying!
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> If you both are executing this through Query Analyzer, I'd
>> >> start by making sure you have the same connection settings,
>> >> properties on both PCs. From the menu in Query Analyzer,
>> >> Tools -> Options.
>> >>
>> >> -Sue
>> >>
>> >> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
>> >> <Karl@.discussions.microsoft.com> wrote:
>> >>
>> >> >So...we have a SQL server and we are using a query to build a basis for a
>> >> >report. We just recently noticed that depending on which machine you run the
>> >> >query from you get a different answer.
>> >> >
>> >> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
>> >> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
>> >> >
>> >> >She gets like 6 returns and I get 189. What would casuse this? Where
>> >> >should I start looking?
>> >> >
>> >> >Here's the query:
>> >> >
>> >> >
>> >> >SELECT P.CREATEDDT, P.CREATEDBY,
>> >> > 'MEASURE' OBJECT_TYPE,
>> >> > P.PROJECTID OBJECTID,
>> >> > P.PROJECTID,
>> >> > P.TITLE,
>> >> > ' ' OTHER,
>> >> > 'Missing Make Model or Serial' ERROR,
>> >> > P.PMC,
>> >> > TS.STATUS,
>> >> > TS.TRANSACTIONID,
>> >> > ''OTHER_PROJ,''OTHER_TITLE
>> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
>> >> >ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
>> >> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> >> > LMI.INCENTIVEID = TS.INCENTIVEID
>> >> >WHERE P.PROJECTID IN(
>> >> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> > ATT.MEASUREID = ACS.MEASUREID
>> >> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
>> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> >> > 'FHEDISHG')
>> >> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
>> >> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
>> >> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
>> >> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >> >AND IMPORTEDDT IS NULL
>> >> >
>> >> >
>> >> >
>> >> >UNION
>> >> >
>> >> >SELECT *,
>> >> >
>> >> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >ATT.MEASUREID = ACS.MEASUREID
>> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
>> >> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >ATT.MEASUREID = ACS.MEASUREID
>> >> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
>> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
>> >> >FROM
>> >> >(SELECT P.CREATEDDT,P.CREATEDBY,
>> >> > 'MEASURE' TYPE,
>> >> > ATT.MEASUREID,
>> >> > P.PROJECTID,
>> >> > P.TITLE,
>> >> > ATTRVALUE OTHER,
>> >> > 'Duplicate Serial Numbers' ERROR,
>> >> > P.PMC,
>> >> > TS.STATUS,
>> >> > TS.TRANSACTIONID
>> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
>> >> >ACTIONSUMMARY ACS ON
>> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> >> > P.PROJECTID = TS.PROJECTID
>> >> >WHERE ATTRVALUE IN
>> >> >(SELECT ATTRVALUE FROM ACTIONATTR
>> >> >WHERE ATTRNAME IN ('SERIAL')
>> >> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
>> >> >GROUP BY ATTRVALUE
>> >> >HAVING COUNT(ATTRVALUE) >1)
>> >> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
>> >> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >> >AND IMPORTEDDT IS NULL)K
>> >> >
>> >>
>> >>
>>|||1. -They both look good.........
2. The execution plan looks identical too.
Damn.......
"Sue Hoegemeier" wrote:
> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
> >How do I find my specific query in syschacheobjects? There's 20,000 items in
> >there or so.........
> >
> >And what do you mean by differences in the execution plans on each PC? I
> >guess I don't get what your asking here.
> >
> >Thanks so much for you help so far!!
> >
> >(waiting for the other user to get back to run the selects seperately)
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Any differences when you look at the two in syscacheobjects?
> >> I would guess you would find two different bucket ids to
> >> identify them - you might get some clues with the values
> >> there.
> >> Any differences when you look at the execution plans on each
> >> PC?
> >>
> >> Other than that, have you tried just first executing each
> >> select that are being unioned to narrow it down some? Or are
> >> both results different on the two machines where you can
> >> reproduce this issue.
> >>
> >> -Sue
> >>
> >> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> >> <Karl@.discussions.microsoft.com> wrote:
> >>
> >> >Sue - good thoughts. This is happening all across out network. Some
> >> >machines get 35 results and some get 189. We noticed because we switched
> >> >from Crystal X to Crystal XI (they are on different servers).
> >> >
> >> >The connection settings in query analyzer are the same. All default. :(
> >> >Good thought, I didn't think to check there.
> >> >
> >> >This also happens when querying through ODBC connections. And query
> >> >analyzer. I don't even know where to begin. :(
> >> >
> >> >Any more ideas?
> >> >
> >> >And thanks so much for replying!
> >> >
> >> >"Sue Hoegemeier" wrote:
> >> >
> >> >> If you both are executing this through Query Analyzer, I'd
> >> >> start by making sure you have the same connection settings,
> >> >> properties on both PCs. From the menu in Query Analyzer,
> >> >> Tools -> Options.
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> >> >> <Karl@.discussions.microsoft.com> wrote:
> >> >>
> >> >> >So...we have a SQL server and we are using a query to build a basis for a
> >> >> >report. We just recently noticed that depending on which machine you run the
> >> >> >query from you get a different answer.
> >> >> >
> >> >> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> >> >> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >> >> >
> >> >> >She gets like 6 returns and I get 189. What would casuse this? Where
> >> >> >should I start looking?
> >> >> >
> >> >> >Here's the query:
> >> >> >
> >> >> >
> >> >> >SELECT P.CREATEDDT, P.CREATEDBY,
> >> >> > 'MEASURE' OBJECT_TYPE,
> >> >> > P.PROJECTID OBJECTID,
> >> >> > P.PROJECTID,
> >> >> > P.TITLE,
> >> >> > ' ' OTHER,
> >> >> > 'Missing Make Model or Serial' ERROR,
> >> >> > P.PMC,
> >> >> > TS.STATUS,
> >> >> > TS.TRANSACTIONID,
> >> >> > ''OTHER_PROJ,''OTHER_TITLE
> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> >> >> >ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> >> >> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> >> > LMI.INCENTIVEID = TS.INCENTIVEID
> >> >> >WHERE P.PROJECTID IN(
> >> >> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID
> >> >> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> >> > 'FHEDISHG')
> >> >> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> >> >> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> >> >> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> >> >> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >> >AND IMPORTEDDT IS NULL
> >> >> >
> >> >> >
> >> >> >
> >> >> >UNION
> >> >> >
> >> >> >SELECT *,
> >> >> >
> >> >> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> >ATT.MEASUREID = ACS.MEASUREID
> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> >> >> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> >ATT.MEASUREID = ACS.MEASUREID
> >> >> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> >> >> >FROM
> >> >> >(SELECT P.CREATEDDT,P.CREATEDBY,
> >> >> > 'MEASURE' TYPE,
> >> >> > ATT.MEASUREID,
> >> >> > P.PROJECTID,
> >> >> > P.TITLE,
> >> >> > ATTRVALUE OTHER,
> >> >> > 'Duplicate Serial Numbers' ERROR,
> >> >> > P.PMC,
> >> >> > TS.STATUS,
> >> >> > TS.TRANSACTIONID
> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> >> >> >ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> >> > P.PROJECTID = TS.PROJECTID
> >> >> >WHERE ATTRVALUE IN
> >> >> >(SELECT ATTRVALUE FROM ACTIONATTR
> >> >> >WHERE ATTRNAME IN ('SERIAL')
> >> >> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> >> >> >GROUP BY ATTRVALUE
> >> >> >HAVING COUNT(ATTRVALUE) >1)
> >> >> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> >> >> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >> >AND IMPORTEDDT IS NULL)K
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||We are getting different results in the select statements - I broke it down
into two queries and we are getting different results there too. My
co-worker is getting 39, and then 23 while I am getting 276 and then 158.
Thats "alittle bit" of a difference. :(
"Sue Hoegemeier" wrote:
> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
> >How do I find my specific query in syschacheobjects? There's 20,000 items in
> >there or so.........
> >
> >And what do you mean by differences in the execution plans on each PC? I
> >guess I don't get what your asking here.
> >
> >Thanks so much for you help so far!!
> >
> >(waiting for the other user to get back to run the selects seperately)
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Any differences when you look at the two in syscacheobjects?
> >> I would guess you would find two different bucket ids to
> >> identify them - you might get some clues with the values
> >> there.
> >> Any differences when you look at the execution plans on each
> >> PC?
> >>
> >> Other than that, have you tried just first executing each
> >> select that are being unioned to narrow it down some? Or are
> >> both results different on the two machines where you can
> >> reproduce this issue.
> >>
> >> -Sue
> >>
> >> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> >> <Karl@.discussions.microsoft.com> wrote:
> >>
> >> >Sue - good thoughts. This is happening all across out network. Some
> >> >machines get 35 results and some get 189. We noticed because we switched
> >> >from Crystal X to Crystal XI (they are on different servers).
> >> >
> >> >The connection settings in query analyzer are the same. All default. :(
> >> >Good thought, I didn't think to check there.
> >> >
> >> >This also happens when querying through ODBC connections. And query
> >> >analyzer. I don't even know where to begin. :(
> >> >
> >> >Any more ideas?
> >> >
> >> >And thanks so much for replying!
> >> >
> >> >"Sue Hoegemeier" wrote:
> >> >
> >> >> If you both are executing this through Query Analyzer, I'd
> >> >> start by making sure you have the same connection settings,
> >> >> properties on both PCs. From the menu in Query Analyzer,
> >> >> Tools -> Options.
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> >> >> <Karl@.discussions.microsoft.com> wrote:
> >> >>
> >> >> >So...we have a SQL server and we are using a query to build a basis for a
> >> >> >report. We just recently noticed that depending on which machine you run the
> >> >> >query from you get a different answer.
> >> >> >
> >> >> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> >> >> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >> >> >
> >> >> >She gets like 6 returns and I get 189. What would casuse this? Where
> >> >> >should I start looking?
> >> >> >
> >> >> >Here's the query:
> >> >> >
> >> >> >
> >> >> >SELECT P.CREATEDDT, P.CREATEDBY,
> >> >> > 'MEASURE' OBJECT_TYPE,
> >> >> > P.PROJECTID OBJECTID,
> >> >> > P.PROJECTID,
> >> >> > P.TITLE,
> >> >> > ' ' OTHER,
> >> >> > 'Missing Make Model or Serial' ERROR,
> >> >> > P.PMC,
> >> >> > TS.STATUS,
> >> >> > TS.TRANSACTIONID,
> >> >> > ''OTHER_PROJ,''OTHER_TITLE
> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> >> >> >ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> >> >> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> >> > LMI.INCENTIVEID = TS.INCENTIVEID
> >> >> >WHERE P.PROJECTID IN(
> >> >> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID
> >> >> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> >> > 'FHEDISHG')
> >> >> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> >> >> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> >> >> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> >> >> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >> >AND IMPORTEDDT IS NULL
> >> >> >
> >> >> >
> >> >> >
> >> >> >UNION
> >> >> >
> >> >> >SELECT *,
> >> >> >
> >> >> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> >ATT.MEASUREID = ACS.MEASUREID
> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> >> >> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> >> >> >ATT.MEASUREID = ACS.MEASUREID
> >> >> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> >> >> >FROM
> >> >> >(SELECT P.CREATEDDT,P.CREATEDBY,
> >> >> > 'MEASURE' TYPE,
> >> >> > ATT.MEASUREID,
> >> >> > P.PROJECTID,
> >> >> > P.TITLE,
> >> >> > ATTRVALUE OTHER,
> >> >> > 'Duplicate Serial Numbers' ERROR,
> >> >> > P.PMC,
> >> >> > TS.STATUS,
> >> >> > TS.TRANSACTIONID
> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> >> >> >ACTIONSUMMARY ACS ON
> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> >> >> > P.PROJECTID = TS.PROJECTID
> >> >> >WHERE ATTRVALUE IN
> >> >> >(SELECT ATTRVALUE FROM ACTIONATTR
> >> >> >WHERE ATTRNAME IN ('SERIAL')
> >> >> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> >> >> >GROUP BY ATTRVALUE
> >> >> >HAVING COUNT(ATTRVALUE) >1)
> >> >> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> >> >> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> >> >> >AND IMPORTEDDT IS NULL)K
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||Your probably going to have to walk through one of those to
determine where the issue is and then go from there. Once
you narrow it down to a particular table, join or where
condition, it will probably be easier to solve.
-Sue
On Tue, 3 Oct 2006 15:00:02 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
>We are getting different results in the select statements - I broke it down
>into two queries and we are getting different results there too. My
>co-worker is getting 39, and then 23 while I am getting 276 and then 158.
>Thats "alittle bit" of a difference. :(
>"Sue Hoegemeier" wrote:
>> 1. Something like:
>> select *
>> from master..syscacheobjects
>> where sql like '<beginning of your sql statement> %'
>> 2. Display the execution plan for the query on each PC. If
>> there is a difference, that might lead you to the issue.
>> In Query Analyzer, from the menu go to Query and select
>> Display Estimated Execution Plan.
>> -Sue
>> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
>> <Karl@.discussions.microsoft.com> wrote:
>> >How do I find my specific query in syschacheobjects? There's 20,000 items in
>> >there or so.........
>> >
>> >And what do you mean by differences in the execution plans on each PC? I
>> >guess I don't get what your asking here.
>> >
>> >Thanks so much for you help so far!!
>> >
>> >(waiting for the other user to get back to run the selects seperately)
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Any differences when you look at the two in syscacheobjects?
>> >> I would guess you would find two different bucket ids to
>> >> identify them - you might get some clues with the values
>> >> there.
>> >> Any differences when you look at the execution plans on each
>> >> PC?
>> >>
>> >> Other than that, have you tried just first executing each
>> >> select that are being unioned to narrow it down some? Or are
>> >> both results different on the two machines where you can
>> >> reproduce this issue.
>> >>
>> >> -Sue
>> >>
>> >> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
>> >> <Karl@.discussions.microsoft.com> wrote:
>> >>
>> >> >Sue - good thoughts. This is happening all across out network. Some
>> >> >machines get 35 results and some get 189. We noticed because we switched
>> >> >from Crystal X to Crystal XI (they are on different servers).
>> >> >
>> >> >The connection settings in query analyzer are the same. All default. :(
>> >> >Good thought, I didn't think to check there.
>> >> >
>> >> >This also happens when querying through ODBC connections. And query
>> >> >analyzer. I don't even know where to begin. :(
>> >> >
>> >> >Any more ideas?
>> >> >
>> >> >And thanks so much for replying!
>> >> >
>> >> >"Sue Hoegemeier" wrote:
>> >> >
>> >> >> If you both are executing this through Query Analyzer, I'd
>> >> >> start by making sure you have the same connection settings,
>> >> >> properties on both PCs. From the menu in Query Analyzer,
>> >> >> Tools -> Options.
>> >> >>
>> >> >> -Sue
>> >> >>
>> >> >> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
>> >> >> <Karl@.discussions.microsoft.com> wrote:
>> >> >>
>> >> >> >So...we have a SQL server and we are using a query to build a basis for a
>> >> >> >report. We just recently noticed that depending on which machine you run the
>> >> >> >query from you get a different answer.
>> >> >> >
>> >> >> >Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
>> >> >> >Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
>> >> >> >
>> >> >> >She gets like 6 returns and I get 189. What would casuse this? Where
>> >> >> >should I start looking?
>> >> >> >
>> >> >> >Here's the query:
>> >> >> >
>> >> >> >
>> >> >> >SELECT P.CREATEDDT, P.CREATEDBY,
>> >> >> > 'MEASURE' OBJECT_TYPE,
>> >> >> > P.PROJECTID OBJECTID,
>> >> >> > P.PROJECTID,
>> >> >> > P.TITLE,
>> >> >> > ' ' OTHER,
>> >> >> > 'Missing Make Model or Serial' ERROR,
>> >> >> > P.PMC,
>> >> >> > TS.STATUS,
>> >> >> > TS.TRANSACTIONID,
>> >> >> > ''OTHER_PROJ,''OTHER_TITLE
>> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
>> >> >> >ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
>> >> >> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> >> >> > LMI.INCENTIVEID = TS.INCENTIVEID
>> >> >> >WHERE P.PROJECTID IN(
>> >> >> >SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >> > ATT.MEASUREID = ACS.MEASUREID
>> >> >> >WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
>> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> >> >> > 'FHEDISHG')
>> >> >> >AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
>> >> >> >AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
>> >> >> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
>> >> >> ><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >> >> >AND IMPORTEDDT IS NULL
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >UNION
>> >> >> >
>> >> >> >SELECT *,
>> >> >> >
>> >> >> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >> >ATT.MEASUREID = ACS.MEASUREID
>> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
>> >> >> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>> >> >> >ATT.MEASUREID = ACS.MEASUREID
>> >> >> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
>> >> >> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
>> >> >> >FROM
>> >> >> >(SELECT P.CREATEDDT,P.CREATEDBY,
>> >> >> > 'MEASURE' TYPE,
>> >> >> > ATT.MEASUREID,
>> >> >> > P.PROJECTID,
>> >> >> > P.TITLE,
>> >> >> > ATTRVALUE OTHER,
>> >> >> > 'Duplicate Serial Numbers' ERROR,
>> >> >> > P.PMC,
>> >> >> > TS.STATUS,
>> >> >> > TS.TRANSACTIONID
>> >> >> >FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
>> >> >> >ACTIONSUMMARY ACS ON
>> >> >> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>> >> >> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>> >> >> > P.PROJECTID = TS.PROJECTID
>> >> >> >WHERE ATTRVALUE IN
>> >> >> >(SELECT ATTRVALUE FROM ACTIONATTR
>> >> >> >WHERE ATTRNAME IN ('SERIAL')
>> >> >> >AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
>> >> >> >GROUP BY ATTRVALUE
>> >> >> >HAVING COUNT(ATTRVALUE) >1)
>> >> >> >AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
>> >> >> ><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>> >> >> >AND IMPORTEDDT IS NULL)K
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Shot in the dark...
- Drop all indexes and recreate them
or
- Create tables with the same columns as the tables involved, but with
different table names. Export data out of the tables involved and load into
the new tables. Rewrite the query to use the new tables and see if there is
any different results.
Also, if I recall, there is a utility on the MDAC page that checks the
version of all MDAC components. See if anything strange is reported.
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> So...we have a SQL server and we are using a query to build a basis for a
> report. We just recently noticed that depending on which machine you run
> the
> query from you get a different answer.
> Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> She gets like 6 returns and I get 189. What would casuse this? Where
> should I start looking?
> Here's the query:
>
> SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN
> ('MAKE','MODEL','SERIAL'))
> ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
> WHERE P.PROJECTID IN(
> SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ACS.CATEGORY IN
> ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> 'FHEDISHG')
> AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL
>
> UNION
> SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> FROM
> (SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
> WHERE ATTRVALUE IN
> (SELECT ATTRVALUE FROM ACTIONATTR
> WHERE ATTRNAME IN ('SERIAL')
> AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> GROUP BY ATTRVALUE
> HAVING COUNT(ATTRVALUE) >1)
> AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL)K
>|||I'll have to try the indexes.........good thought. Its HUGE tables
something could be outta whack...
I downloaded CCcheck from the MS website and the MDACs are the same.
"JJ" wrote:
> Shot in the dark...
> - Drop all indexes and recreate them
> or
> - Create tables with the same columns as the tables involved, but with
> different table names. Export data out of the tables involved and load into
> the new tables. Rewrite the query to use the new tables and see if there is
> any different results.
> Also, if I recall, there is a utility on the MDAC page that checks the
> version of all MDAC components. See if anything strange is reported.
>
> "Karl" <Karl@.discussions.microsoft.com> wrote in message
> news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> > So...we have a SQL server and we are using a query to build a basis for a
> > report. We just recently noticed that depending on which machine you run
> > the
> > query from you get a different answer.
> >
> > Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> > Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> >
> > She gets like 6 returns and I get 189. What would casuse this? Where
> > should I start looking?
> >
> > Here's the query:
> >
> >
> > SELECT P.CREATEDDT, P.CREATEDBY,
> > 'MEASURE' OBJECT_TYPE,
> > P.PROJECTID OBJECTID,
> > P.PROJECTID,
> > P.TITLE,
> > ' ' OTHER,
> > 'Missing Make Model or Serial' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID,
> > ''OTHER_PROJ,''OTHER_TITLE
> > FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN
> > ('MAKE','MODEL','SERIAL'))
> > ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> > ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > LMI.INCENTIVEID = TS.INCENTIVEID
> > WHERE P.PROJECTID IN(
> > SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > WHERE ACS.CATEGORY IN
> > ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > 'FHEDISHG')
> > AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> > AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> > AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> > <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> > AND IMPORTEDDT IS NULL
> >
> >
> >
> > UNION
> >
> > SELECT *,
> >
> > (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> > (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID
> > INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> > WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> > FROM
> > (SELECT P.CREATEDDT,P.CREATEDBY,
> > 'MEASURE' TYPE,
> > ATT.MEASUREID,
> > P.PROJECTID,
> > P.TITLE,
> > ATTRVALUE OTHER,
> > 'Duplicate Serial Numbers' ERROR,
> > P.PMC,
> > TS.STATUS,
> > TS.TRANSACTIONID
> > FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> > ACTIONSUMMARY ACS ON
> > ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> > ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> > P.PROJECTID = TS.PROJECTID
> > WHERE ATTRVALUE IN
> > (SELECT ATTRVALUE FROM ACTIONATTR
> > WHERE ATTRNAME IN ('SERIAL')
> > AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> > GROUP BY ATTRVALUE
> > HAVING COUNT(ATTRVALUE) >1)
> > AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> > <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> > AND IMPORTEDDT IS NULL)K
> >
> >
>
>sql

Friday, March 30, 2012

One more - SQL query

One more - SQL query

One hell of a problem (SQL2K)

One hell of a problem (SQL2K)

One hell of a problem (SQL2K)

old sql2k5 folder

Blog Archive

About Me