Originally I thought this was going to be a DISTINCT issue, but I don't
think that is the case.
I have 3 tables..
Apps
ID AppName
1 ApplicationOne
Tests
ID TestCase
1 Run Overnight
TestAttributes
ID AppID TestID Type OS
ProjectID
1 1 1 Stress Windows XP
1
2 1 1 Quick Check Windows XP 1
3 1 1 Larger Check Windows XP 1
If I do..
SELECT DISTINCT Tests.TestCase, Apps.AppName
FROM Tests
LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
WHERE (TestAttributes.ProjectID = '1')
ORDER BY TestCase
I will get
Run Overnight ApplicationOne
This is what i need to display in a grid, however I will need to know all 3
IDs for the 3 seperate types.
Is it possible in a query to get something like
Run Overnight ApplicationOne
1
2
3
Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
With more data it would be seomthing like
Run Overnight ApplicationOne
1
2
3
Run Daytime ApplicaitonTwo
4
5
Run Whenever ApplicationThree
6
7
8
9
Basically I want to display one distinct test case, but still get all the
IDs for the no distinct ones. I could obviously do this client side, but
the performance may not be the best.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
There is no such thig as a "magical universal" id number in an RDBMS.
Did you mean something like this?
CREATE TABLE Applications
(app_id INTEGER NOT NULL PRIMARY KEY,
app_name CHAR(20) NOT NULL);
In a relational model, an entity is the sum of its attributes. So why
did you split the attributes into another table? That violates rules
about mixing data and metadata
Why did you use multiple names for the same data element
(TestCases.AppID = Apps.ID)? See how vague that makes the sentence " I
will need to know all 3 IDs for the 3 separate types." Which id do you
mean? How can you build a data dictioanry with this mess?
CREATE TABLE Tests
(test_nbr INTEGER NOT NULL PRIMARY KEY,
app_id INTEGER NOT NULL
REFERENCES Applications(app_id),
test_type CHAR(10) NOT NULL,
os CHAR(10) NOT NULL,
project_id INTEGER NOT NULL);
Ther is no table named TestCases, so your sample query makees no sense.
Can we try again?|||In a stored procedure, you can browse your tests case with a CURSOR and for
each one store several lines in a temporary table :
- the test case & app.name
- one line for each test attribute id
Build the temp table by an IDENTITY column, and you'll can do a
select ... order by Id_field.
JN.
"Lucas Graf" <lgraf2000@.comcast.net> a crit dans le message de news:
OFSIfSCIFHA.3428@.TK2MSFTNGP10.phx.gbl...
> Originally I thought this was going to be a DISTINCT issue, but I don't
> think that is the case.
> I have 3 tables..
> Apps
> ID AppName
> 1 ApplicationOne
> Tests
> ID TestCase
> 1 Run Overnight
> TestAttributes
> ID AppID TestID Type OS ProjectID
> 1 1 1 Stress Windows XP 1
> 2 1 1 Quick Check Windows XP 1
> 3 1 1 Larger Check Windows XP
> 1
> If I do..
> SELECT DISTINCT Tests.TestCase, Apps.AppName
> FROM Tests
> LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
> WHERE (TestAttributes.ProjectID = '1')
> ORDER BY TestCase
> I will get
> Run Overnight ApplicationOne
> This is what i need to display in a grid, however I will need to know all
> 3 IDs for the 3 seperate types.
> Is it possible in a query to get something like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
> With more data it would be seomthing like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Run Daytime ApplicaitonTwo
> 4
> 5
> Run Whenever ApplicationThree
> 6
> 7
> 8
> 9
> Basically I want to display one distinct test case, but still get all the
> IDs for the no distinct ones. I could obviously do this client side, but
> the performance may not be the best.
>|||More clear depiction of what I am asking.
CREATE TABLE Apps(
app_id smallint
IDENTITY(1,1)
PRIMARY KEY,
app_name varchar(50) NOT NULL
)
CREATE TABLE Tests(
test_id smallint
IDENTITY(1,1)
PRIMARY KEY,
testcase varchar(50) NOT NULL
)
CREATE TABLE TestAttributes(
attribute_id smallint
IDENTITY(1,1)
PRIMARY KEY,
attr_appid smallint NOT NULL,
attr_testid smallint NOT NULL,
attr_type varchar(50) NOT NULL,
attr_OS varchar(50) NOT NULL,
attr_projid smallint NOT NULL
)
go
INSERT Apps values('Application One')
INSERT Tests values('Run Overnight')
INSERT TestAttributes values('1','1',' Stress','Windows XP','1')
INSERT TestAttributes values('1','1','Quick Check','Windows XP','1')
INSERT TestAttributes values('1','1','Longer Check','Windows XP','1')
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
Gives me
testcase app_name
Run Overnight Application One
But i will also need to know all 3 of the attribute_id's that correspond to
that test case
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name,attribute_id
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
I get
testcase app_name attribute_id
Run Overnight Application One 1
Run Overnight Application One 2
Run Overnight Application One 3
I don't want to show alll 3, however I need to know all 3 attribute_id's
So ideallly would like a query to give me something like
testcase app_name
Run Overnight Application One
attribute_id
1
attribute_id
2
attribute_id
3
Showing posts with label distinct. Show all posts
Showing posts with label distinct. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
query problem --with a view
I have a View which does what I need it too-
select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case
when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatus
From department d
Left join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)
Left join (Select Count(*) cnt, deptid from employeeeval join employee
on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)
Brings back data as follows:
Cast - Cast - 00999 - 09246 - reviews still in progress
CMP- Copper Metal Products - 03315 - 09246 - reviews are complete
CNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progress
CRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progress
So I did the following:
select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatus
from vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
Now this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:
Cast - Atkins, Carl - 09935 - Chris Burke - Not Started
Cast - Bridges, James - 09929 - Chris Burke - In Progress
CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not Started
CNS - Mitchell, Bill - 06200 - Kahle Rummer - In Progress
Now what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.
Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progress
Cast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progress
CMP - NULL - NULL - Fred Grove - NULL - reviews are complete
CNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progress
CNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progress
CRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progress
CRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progress
MicroMll - NULL - NULL - Tim Cross - NULL - reviews are complete
I hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this:
Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept
Dept ID- Supervisor- Reviews Complete
No data shown since COMPELTE
Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept
I just had the same problem. I am also having a problem with a left outer join. Reading your post led me to try testing with and without a view. I created two simple tables and did the LO join. That worked fine. Creating a select * view based on the first table then using that view in the LO join as the left table gave the erroneous result again. It is something to do with the fact that there is a view in there. I'll post again if I solve it.
Regards,
Raymond.
|||Got it,
I stuck this in the top of the code for the view:
SELECT TOP 100 PERCENT *
This was instead of SELECT *. Replacing SELECT * with select colA, colB also worked.
I got the idea from the MS site:http://support.microsoft.com/kb/321541
Below is a snip of some of the text from the site. Putting DISTINCT in the select clause did not work BTW. Didn't try GROUP BY.
Cheers,
Raymond.
------snip-----
You can reproduce the behavior when all the following conditions exist:
? You use a query that contains an outer join, and you use a view (directly or indirectly) on the inner side of the outer join.
? The view contains an expression in its SELECT list that references one or more base table columns. Also, if all base table column values are NULL, the expression returns non-NULL results.
? The view is a simple view that does not contain a DISTINCT, TOP, or GROUP BY aggregate.
------snip-----
sqlWednesday, March 21, 2012
Query performance
The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have index. Is there any alternative available for the above which can increase the performance ?
Please advice,
Thanks,
Smitha
select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have index. Is there any alternative available for the above which can increase the performance ?
Please advice,
Thanks,
Smitha
The first thing you need to do is get rid of the "distinct" keyword - that alone will slow things down considerably. If you have duplicate rows in your table then you should remove them. If the number of occurances of a duplicate row is important to your application, then add that as a column, and eliminate the duplicates.
The other thing it'd seem to me to help, although I haven't tested it, would be to replace your WHERE EXISTS clause with an outer join with test3 on d.col3=a.col3. I don't know your data though, so I'm not sure of the effect of that change.
|||How slow is 'very slow'...?
How much data is there involved in the tables?
What does the query plan look like?
Which table or tables shows the largest amount of data that's been worked on?
For an alternative, you need to provide some sample data along with the desired result and a brief explanation of what the query is supposed to do.
/Kenneth
|||The other thing you would have to get rid of is the a.*, retrieve only the columns you really need.Jose Luis
|||
What does the query plan look like?
First are you sure you need to do a DISTINCT?
See these links for some overview
http://www.sql-server-performance.com/transact_sql.asp
http://www.sql-server-performance.com/nb_select_distinct.asp
sqlMonday, March 12, 2012
Query or Parameter issue
I am using the following query for my dataset:
SELECT DISTINCT
vwProtocol.ProtocolNumber,
vwProtocol.HSCNumber,
PIName = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
TotalAccrual = (SELECT Count(PatientID)
FROM tb_PatientEnrollment
WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
tb_Protocol.ProtocolID,
Reg = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
RegPhone = (SELECT CASE Len(Telephone)
WHEN 7 THEN stuff(Telephone, 4, 0, '-')
WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
ELSE Telephone
END
FROM tb_Membership
WHERE tb_Membership.MemberID = tb_Protocol.RegulatoryMemberID),
tb_Protocol.Title,
IRBExpiration = vwProtocol.HSCExpiration,
vwProtocol.Status
FROM
tb_Protocol
RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
WHERE
vwProtocol.HSCExpiration between @.begindate
and @.enddate
AND
ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
I am using another dataset to populate the @.reg parameter. When I run this
in preview it works fine. When I deploy it, no matter what I do I get an
error:
This report requires a default or user-defined value for the report
parameter 'reg'. To run or subscribe to this report, you must provide a
parameter value. (rsReportParameterValueNotSet) Get Online Help
I have not a clue what to do next or what could possibly be going wrong. I
have another report on that same server that works just fine with a drop down
so I don't think it is the setup of my report server.
Any ideas, place to look, *anything* would be most appreciated!!!
Thanks,
EvaIn the report designer, under Report menu, look under parameters. You
should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
property in the left column, you should be able to set the default value.
This should fix the problem.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>I am using the following query for my dataset:
> SELECT DISTINCT
> vwProtocol.ProtocolNumber,
> vwProtocol.HSCNumber,
> PIName => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> TotalAccrual => (SELECT Count(PatientID)
> FROM tb_PatientEnrollment
> WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> tb_Protocol.ProtocolID,
> Reg => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> RegPhone => (SELECT CASE Len(Telephone)
> WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> ELSE Telephone
> END
> FROM tb_Membership
> WHERE tb_Membership.MemberID => tb_Protocol.RegulatoryMemberID),
> tb_Protocol.Title,
> IRBExpiration = vwProtocol.HSCExpiration,
> vwProtocol.Status
> FROM
> tb_Protocol
> RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> WHERE
> vwProtocol.HSCExpiration between @.begindate
> and @.enddate
> AND
> ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> I am using another dataset to populate the @.reg parameter. When I run
> this
> in preview it works fine. When I deploy it, no matter what I do I get an
> error:
> This report requires a default or user-defined value for the report
> parameter 'reg'. To run or subscribe to this report, you must provide a
> parameter value. (rsReportParameterValueNotSet) Get Online Help
> I have not a clue what to do next or what could possibly be going wrong.
> I
> have another report on that same server that works just fine with a drop
> down
> so I don't think it is the setup of my report server.
> Any ideas, place to look, *anything* would be most appreciated!!!
> Thanks,
> Eva|||Thanks for the reply!
When I set a default I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot read the next data row for the data set Upcoming.
(rsErrorReadingNextDataRow) Get Online Help
Syntax error converting datetime from character string.
However, if I remove the reg parameter entirely the 2 datetime parameters
work as expected.
Any other ideas?
"goodman93" wrote:
> In the report designer, under Report menu, look under parameters. You
> should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
> property in the left column, you should be able to set the default value.
> This should fix the problem.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >I am using the following query for my dataset:
> >
> > SELECT DISTINCT
> > vwProtocol.ProtocolNumber,
> > vwProtocol.HSCNumber,
> > PIName => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> > TotalAccrual => > (SELECT Count(PatientID)
> > FROM tb_PatientEnrollment
> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> > tb_Protocol.ProtocolID,
> > Reg => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> > RegPhone => > (SELECT CASE Len(Telephone)
> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> > ELSE Telephone
> > END
> > FROM tb_Membership
> > WHERE tb_Membership.MemberID => > tb_Protocol.RegulatoryMemberID),
> > tb_Protocol.Title,
> > IRBExpiration = vwProtocol.HSCExpiration,
> > vwProtocol.Status
> > FROM
> > tb_Protocol
> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> > WHERE
> > vwProtocol.HSCExpiration between @.begindate
> > and @.enddate
> > AND
> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >
> > I am using another dataset to populate the @.reg parameter. When I run
> > this
> > in preview it works fine. When I deploy it, no matter what I do I get an
> > error:
> > This report requires a default or user-defined value for the report
> > parameter 'reg'. To run or subscribe to this report, you must provide a
> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >
> > I have not a clue what to do next or what could possibly be going wrong.
> > I
> > have another report on that same server that works just fine with a drop
> > down
> > so I don't think it is the setup of my report server.
> >
> > Any ideas, place to look, *anything* would be most appreciated!!!
> >
> > Thanks,
> > Eva
>
>|||Most likely this comes from passing in a string to compare to
vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
your query for the begindate and "CAST(@.endate as datetime)" for the
enddate. You will probably also need to do a CAST(@.reg as integer) for that
to work do--depending on the data type of RegulatoryMemberID.
Hope this helped.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> Thanks for the reply!
> When I set a default I get the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot read the next data row for the data set Upcoming.
> (rsErrorReadingNextDataRow) Get Online Help
> Syntax error converting datetime from character string.
> However, if I remove the reg parameter entirely the 2 datetime parameters
> work as expected.
> Any other ideas?
> "goodman93" wrote:
>> In the report designer, under Report menu, look under parameters. You
>> should see a list for @.begindate, @.enddate and @.reg. If you choose the
>> @.Reg
>> property in the left column, you should be able to set the default value.
>> This should fix the problem.
>> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
>> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>> >I am using the following query for my dataset:
>> >
>> > SELECT DISTINCT
>> > vwProtocol.ProtocolNumber,
>> > vwProtocol.HSCNumber,
>> > PIName =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
>> > TotalAccrual =>> > (SELECT Count(PatientID)
>> > FROM tb_PatientEnrollment
>> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
>> > tb_Protocol.ProtocolID,
>> > Reg =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
>> > RegPhone =>> > (SELECT CASE Len(Telephone)
>> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
>> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
>> > ELSE Telephone
>> > END
>> > FROM tb_Membership
>> > WHERE tb_Membership.MemberID =>> > tb_Protocol.RegulatoryMemberID),
>> > tb_Protocol.Title,
>> > IRBExpiration = vwProtocol.HSCExpiration,
>> > vwProtocol.Status
>> > FROM
>> > tb_Protocol
>> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
>> > WHERE
>> > vwProtocol.HSCExpiration between @.begindate
>> > and @.enddate
>> > AND
>> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
>> >
>> > I am using another dataset to populate the @.reg parameter. When I run
>> > this
>> > in preview it works fine. When I deploy it, no matter what I do I get
>> > an
>> > error:
>> > This report requires a default or user-defined value for the report
>> > parameter 'reg'. To run or subscribe to this report, you must provide a
>> > parameter value. (rsReportParameterValueNotSet) Get Online Help
>> >
>> > I have not a clue what to do next or what could possibly be going
>> > wrong.
>> > I
>> > have another report on that same server that works just fine with a
>> > drop
>> > down
>> > so I don't think it is the setup of my report server.
>> >
>> > Any ideas, place to look, *anything* would be most appreciated!!!
>> >
>> > Thanks,
>> > Eva
>>|||What have you got your date params datatypes set as.
I tend to pass them as strings in mm/dd/yyy format, and it works fine for
evaluating against date datatypes.
"goodman93" wrote:
> Most likely this comes from passing in a string to compare to
> vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
> your query for the begindate and "CAST(@.endate as datetime)" for the
> enddate. You will probably also need to do a CAST(@.reg as integer) for that
> to work do--depending on the data type of RegulatoryMemberID.
> Hope this helped.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> > Thanks for the reply!
> >
> > When I set a default I get the following error:
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot read the next data row for the data set Upcoming.
> > (rsErrorReadingNextDataRow) Get Online Help
> > Syntax error converting datetime from character string.
> >
> > However, if I remove the reg parameter entirely the 2 datetime parameters
> > work as expected.
> >
> > Any other ideas?
> >
> > "goodman93" wrote:
> >
> >> In the report designer, under Report menu, look under parameters. You
> >> should see a list for @.begindate, @.enddate and @.reg. If you choose the
> >> @.Reg
> >> property in the left column, you should be able to set the default value.
> >> This should fix the problem.
> >>
> >> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> >> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >> >I am using the following query for my dataset:
> >> >
> >> > SELECT DISTINCT
> >> > vwProtocol.ProtocolNumber,
> >> > vwProtocol.HSCNumber,
> >> > PIName => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> >> > TotalAccrual => >> > (SELECT Count(PatientID)
> >> > FROM tb_PatientEnrollment
> >> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> >> > tb_Protocol.ProtocolID,
> >> > Reg => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> >> > RegPhone => >> > (SELECT CASE Len(Telephone)
> >> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> >> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> >> > ELSE Telephone
> >> > END
> >> > FROM tb_Membership
> >> > WHERE tb_Membership.MemberID => >> > tb_Protocol.RegulatoryMemberID),
> >> > tb_Protocol.Title,
> >> > IRBExpiration = vwProtocol.HSCExpiration,
> >> > vwProtocol.Status
> >> > FROM
> >> > tb_Protocol
> >> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> >> > WHERE
> >> > vwProtocol.HSCExpiration between @.begindate
> >> > and @.enddate
> >> > AND
> >> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >> >
> >> > I am using another dataset to populate the @.reg parameter. When I run
> >> > this
> >> > in preview it works fine. When I deploy it, no matter what I do I get
> >> > an
> >> > error:
> >> > This report requires a default or user-defined value for the report
> >> > parameter 'reg'. To run or subscribe to this report, you must provide a
> >> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >> >
> >> > I have not a clue what to do next or what could possibly be going
> >> > wrong.
> >> > I
> >> > have another report on that same server that works just fine with a
> >> > drop
> >> > down
> >> > so I don't think it is the setup of my report server.
> >> >
> >> > Any ideas, place to look, *anything* would be most appreciated!!!
> >> >
> >> > Thanks,
> >> > Eva
> >>
> >>
> >>
>
>|||I had a problem with a ReportingServices Parameter named 'reg', too. The report was called from c#-code with parameters via soap. It should get something like "param1='Value1';reg='Value2';param3='Value3'" but got 'param1='Value1(REG)=Value2';param3='Value'" ((REG) for the registry symbol 'R' in a circle). If anybody knows why this happens or how to mask a parameter named reg, I would like to know. Yes, the problem disappeared when I renamed that parameter
From http://www.developmentnow.com/g/115_2005_7_0_0_560706/Query-or-Parameter-issue.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
SELECT DISTINCT
vwProtocol.ProtocolNumber,
vwProtocol.HSCNumber,
PIName = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
TotalAccrual = (SELECT Count(PatientID)
FROM tb_PatientEnrollment
WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
tb_Protocol.ProtocolID,
Reg = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
RegPhone = (SELECT CASE Len(Telephone)
WHEN 7 THEN stuff(Telephone, 4, 0, '-')
WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
ELSE Telephone
END
FROM tb_Membership
WHERE tb_Membership.MemberID = tb_Protocol.RegulatoryMemberID),
tb_Protocol.Title,
IRBExpiration = vwProtocol.HSCExpiration,
vwProtocol.Status
FROM
tb_Protocol
RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
WHERE
vwProtocol.HSCExpiration between @.begindate
and @.enddate
AND
ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
I am using another dataset to populate the @.reg parameter. When I run this
in preview it works fine. When I deploy it, no matter what I do I get an
error:
This report requires a default or user-defined value for the report
parameter 'reg'. To run or subscribe to this report, you must provide a
parameter value. (rsReportParameterValueNotSet) Get Online Help
I have not a clue what to do next or what could possibly be going wrong. I
have another report on that same server that works just fine with a drop down
so I don't think it is the setup of my report server.
Any ideas, place to look, *anything* would be most appreciated!!!
Thanks,
EvaIn the report designer, under Report menu, look under parameters. You
should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
property in the left column, you should be able to set the default value.
This should fix the problem.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>I am using the following query for my dataset:
> SELECT DISTINCT
> vwProtocol.ProtocolNumber,
> vwProtocol.HSCNumber,
> PIName => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> TotalAccrual => (SELECT Count(PatientID)
> FROM tb_PatientEnrollment
> WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> tb_Protocol.ProtocolID,
> Reg => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> RegPhone => (SELECT CASE Len(Telephone)
> WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> ELSE Telephone
> END
> FROM tb_Membership
> WHERE tb_Membership.MemberID => tb_Protocol.RegulatoryMemberID),
> tb_Protocol.Title,
> IRBExpiration = vwProtocol.HSCExpiration,
> vwProtocol.Status
> FROM
> tb_Protocol
> RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> WHERE
> vwProtocol.HSCExpiration between @.begindate
> and @.enddate
> AND
> ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> I am using another dataset to populate the @.reg parameter. When I run
> this
> in preview it works fine. When I deploy it, no matter what I do I get an
> error:
> This report requires a default or user-defined value for the report
> parameter 'reg'. To run or subscribe to this report, you must provide a
> parameter value. (rsReportParameterValueNotSet) Get Online Help
> I have not a clue what to do next or what could possibly be going wrong.
> I
> have another report on that same server that works just fine with a drop
> down
> so I don't think it is the setup of my report server.
> Any ideas, place to look, *anything* would be most appreciated!!!
> Thanks,
> Eva|||Thanks for the reply!
When I set a default I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot read the next data row for the data set Upcoming.
(rsErrorReadingNextDataRow) Get Online Help
Syntax error converting datetime from character string.
However, if I remove the reg parameter entirely the 2 datetime parameters
work as expected.
Any other ideas?
"goodman93" wrote:
> In the report designer, under Report menu, look under parameters. You
> should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
> property in the left column, you should be able to set the default value.
> This should fix the problem.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >I am using the following query for my dataset:
> >
> > SELECT DISTINCT
> > vwProtocol.ProtocolNumber,
> > vwProtocol.HSCNumber,
> > PIName => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> > TotalAccrual => > (SELECT Count(PatientID)
> > FROM tb_PatientEnrollment
> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> > tb_Protocol.ProtocolID,
> > Reg => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> > RegPhone => > (SELECT CASE Len(Telephone)
> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> > ELSE Telephone
> > END
> > FROM tb_Membership
> > WHERE tb_Membership.MemberID => > tb_Protocol.RegulatoryMemberID),
> > tb_Protocol.Title,
> > IRBExpiration = vwProtocol.HSCExpiration,
> > vwProtocol.Status
> > FROM
> > tb_Protocol
> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> > WHERE
> > vwProtocol.HSCExpiration between @.begindate
> > and @.enddate
> > AND
> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >
> > I am using another dataset to populate the @.reg parameter. When I run
> > this
> > in preview it works fine. When I deploy it, no matter what I do I get an
> > error:
> > This report requires a default or user-defined value for the report
> > parameter 'reg'. To run or subscribe to this report, you must provide a
> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >
> > I have not a clue what to do next or what could possibly be going wrong.
> > I
> > have another report on that same server that works just fine with a drop
> > down
> > so I don't think it is the setup of my report server.
> >
> > Any ideas, place to look, *anything* would be most appreciated!!!
> >
> > Thanks,
> > Eva
>
>|||Most likely this comes from passing in a string to compare to
vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
your query for the begindate and "CAST(@.endate as datetime)" for the
enddate. You will probably also need to do a CAST(@.reg as integer) for that
to work do--depending on the data type of RegulatoryMemberID.
Hope this helped.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> Thanks for the reply!
> When I set a default I get the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot read the next data row for the data set Upcoming.
> (rsErrorReadingNextDataRow) Get Online Help
> Syntax error converting datetime from character string.
> However, if I remove the reg parameter entirely the 2 datetime parameters
> work as expected.
> Any other ideas?
> "goodman93" wrote:
>> In the report designer, under Report menu, look under parameters. You
>> should see a list for @.begindate, @.enddate and @.reg. If you choose the
>> @.Reg
>> property in the left column, you should be able to set the default value.
>> This should fix the problem.
>> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
>> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>> >I am using the following query for my dataset:
>> >
>> > SELECT DISTINCT
>> > vwProtocol.ProtocolNumber,
>> > vwProtocol.HSCNumber,
>> > PIName =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
>> > TotalAccrual =>> > (SELECT Count(PatientID)
>> > FROM tb_PatientEnrollment
>> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
>> > tb_Protocol.ProtocolID,
>> > Reg =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
>> > RegPhone =>> > (SELECT CASE Len(Telephone)
>> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
>> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
>> > ELSE Telephone
>> > END
>> > FROM tb_Membership
>> > WHERE tb_Membership.MemberID =>> > tb_Protocol.RegulatoryMemberID),
>> > tb_Protocol.Title,
>> > IRBExpiration = vwProtocol.HSCExpiration,
>> > vwProtocol.Status
>> > FROM
>> > tb_Protocol
>> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
>> > WHERE
>> > vwProtocol.HSCExpiration between @.begindate
>> > and @.enddate
>> > AND
>> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
>> >
>> > I am using another dataset to populate the @.reg parameter. When I run
>> > this
>> > in preview it works fine. When I deploy it, no matter what I do I get
>> > an
>> > error:
>> > This report requires a default or user-defined value for the report
>> > parameter 'reg'. To run or subscribe to this report, you must provide a
>> > parameter value. (rsReportParameterValueNotSet) Get Online Help
>> >
>> > I have not a clue what to do next or what could possibly be going
>> > wrong.
>> > I
>> > have another report on that same server that works just fine with a
>> > drop
>> > down
>> > so I don't think it is the setup of my report server.
>> >
>> > Any ideas, place to look, *anything* would be most appreciated!!!
>> >
>> > Thanks,
>> > Eva
>>|||What have you got your date params datatypes set as.
I tend to pass them as strings in mm/dd/yyy format, and it works fine for
evaluating against date datatypes.
"goodman93" wrote:
> Most likely this comes from passing in a string to compare to
> vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
> your query for the begindate and "CAST(@.endate as datetime)" for the
> enddate. You will probably also need to do a CAST(@.reg as integer) for that
> to work do--depending on the data type of RegulatoryMemberID.
> Hope this helped.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> > Thanks for the reply!
> >
> > When I set a default I get the following error:
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot read the next data row for the data set Upcoming.
> > (rsErrorReadingNextDataRow) Get Online Help
> > Syntax error converting datetime from character string.
> >
> > However, if I remove the reg parameter entirely the 2 datetime parameters
> > work as expected.
> >
> > Any other ideas?
> >
> > "goodman93" wrote:
> >
> >> In the report designer, under Report menu, look under parameters. You
> >> should see a list for @.begindate, @.enddate and @.reg. If you choose the
> >> @.Reg
> >> property in the left column, you should be able to set the default value.
> >> This should fix the problem.
> >>
> >> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> >> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >> >I am using the following query for my dataset:
> >> >
> >> > SELECT DISTINCT
> >> > vwProtocol.ProtocolNumber,
> >> > vwProtocol.HSCNumber,
> >> > PIName => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> >> > TotalAccrual => >> > (SELECT Count(PatientID)
> >> > FROM tb_PatientEnrollment
> >> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> >> > tb_Protocol.ProtocolID,
> >> > Reg => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> >> > RegPhone => >> > (SELECT CASE Len(Telephone)
> >> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> >> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> >> > ELSE Telephone
> >> > END
> >> > FROM tb_Membership
> >> > WHERE tb_Membership.MemberID => >> > tb_Protocol.RegulatoryMemberID),
> >> > tb_Protocol.Title,
> >> > IRBExpiration = vwProtocol.HSCExpiration,
> >> > vwProtocol.Status
> >> > FROM
> >> > tb_Protocol
> >> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> >> > WHERE
> >> > vwProtocol.HSCExpiration between @.begindate
> >> > and @.enddate
> >> > AND
> >> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >> >
> >> > I am using another dataset to populate the @.reg parameter. When I run
> >> > this
> >> > in preview it works fine. When I deploy it, no matter what I do I get
> >> > an
> >> > error:
> >> > This report requires a default or user-defined value for the report
> >> > parameter 'reg'. To run or subscribe to this report, you must provide a
> >> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >> >
> >> > I have not a clue what to do next or what could possibly be going
> >> > wrong.
> >> > I
> >> > have another report on that same server that works just fine with a
> >> > drop
> >> > down
> >> > so I don't think it is the setup of my report server.
> >> >
> >> > Any ideas, place to look, *anything* would be most appreciated!!!
> >> >
> >> > Thanks,
> >> > Eva
> >>
> >>
> >>
>
>|||I had a problem with a ReportingServices Parameter named 'reg', too. The report was called from c#-code with parameters via soap. It should get something like "param1='Value1';reg='Value2';param3='Value3'" but got 'param1='Value1(REG)=Value2';param3='Value'" ((REG) for the registry symbol 'R' in a circle). If anybody knows why this happens or how to mask a parameter named reg, I would like to know. Yes, the problem disappeared when I renamed that parameter
From http://www.developmentnow.com/g/115_2005_7_0_0_560706/Query-or-Parameter-issue.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
Query optimizer question
Hi,
we have a very strange behavior of SQL Server that we try to understand.
We have the following Select statement:
SELECT DISTINCT TOP 100000 Customer.id
FROM Customer LEFT JOIN CustomerAddress CA ON
Customer.idDefaultAddress=CA.id
WHERE 1=1
and Customer.id IN (
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
)
When we run this select we get the desired results from the database.
However if we run only the Select that is in the Where clause, that is:
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
we get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'DCDB' to a column of data
type int.
which is somehow normal as the column ValueAttribute is a varchar column
.
What I do not understand is why in the first situation it works.
Obviously looking at the execution plan you can see that it is made
different in the 2 situation but it's SQL that decides upon what
execution plan it should chose.
Does anyone have any explanation for this behavior or could you point me
to some documentation regarding the way the Query Optimizer works?
Thanks,
Florian
Here is the tables structure: CREATE TABLE [dbo].[Customer] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[idDefaultShippingAddress] [int] NULL ,
[idDefaultAddress] [int] NULL ,
[idPrincipalContact] [uniqueidentifier] NULL ,
[idSalesPerson] [uniqueidentifier] NULL ,
[idCustomerParent] [uniqueidentifier] NULL ,
[Code] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WebSite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[InternalNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[DotNetMessenger] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[EntCreationDate] [datetime] NULL ,
[NumberEmployee] [int] NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[BankName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[BankProvinceState] [int] NULL ,
[BankAccountNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[BankRoutingNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[CreditCardType] [int] NULL ,
[CreditCardNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[CreditCardExpMonth] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardExpYear] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[idDefaultLanguage] [int] NOT NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idTaxCode] [int] NOT NULL ,
[idCurrency] [int] NOT NULL ,
[idTimeZone] [int] NOT NULL ,
[Address1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ProvinceState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
,
[ZipPostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[InternalNotes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[ShippingInstruction] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[ISO3166GEO] [int] NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttribute] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idAttribute] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttributeDescription] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomerAttribute] [int] NOT NULL ,
[idCulture] [int] NULL ,
[ValueAttribute] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [PK_AddressCustomer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttribute] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttributeDescription] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [DF_Customer_id] DEFAULT (newid()) FOR [id],
CONSTRAINT [DF_Customer_Name] DEFAULT ('') FOR [Name],
CONSTRAINT [DF_Customer_Phone] DEFAULT ('') FOR [Phone],
CONSTRAINT [DF_Customer_Fax] DEFAULT ('') FOR [Fax],
CONSTRAINT [DF_Customer_WebSite] DEFAULT ('') FOR [WebSite],
CONSTRAINT [DF_Customer_Email] DEFAULT ('') FOR [Email],
CONSTRAINT [DF_Customer_Notes] DEFAULT ('') FOR [InternalNotes],
CONSTRAINT [DF_Customer_DotNetMessenger] DEFAULT ('') FOR
[DotNetMessenger],
CONSTRAINT [DF_Customer_NumberEmployee] DEFAULT (0) FOR
[NumberEmployee],
CONSTRAINT [DF_Customer_BankName] DEFAULT ('') FOR [BankName],
CONSTRAINT [DF_Customer_BankCity] DEFAULT ('') FOR [BankCity],
CONSTRAINT [DF_Customer_BankAccountNumber] DEFAULT ('') FOR
[BankAccountNumber],
CONSTRAINT [DF_Customer_BankRoutingNumber] DEFAULT ('') FOR
[BankRoutingNumber],
CONSTRAINT [DF_Customer_CreditCardNumber] DEFAULT ('') FOR
[CreditCardNumber],
CONSTRAINT [DF_Customer_idDefaultLanguage] DEFAULT (1) FOR
[idDefaultLanguage]
GO
CREATE INDEX & #91;IX_CustomeridDefaultShippingAddress]
ON
[dbo].[Customer]([idDefaultShippingAddress]) WITH FILLFACTOR =
90 ON
[PRIMARY]
GO
CREATE INDEX [IX_CustomerDefaultAddress] ON
[dbo].[Customer]([idDefaultAddress]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [IX_CustomeridPrincipalContact] ON
[dbo].[Customer]([idPrincipalContact]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Customer_Code] ON [dbo].[Customer](
1;Code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Customer] ON [dbo].[Customer]([id]) WITH
FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [DF_CustomerAddress_Address1] DEFAULT ('') FOR [Address1]
,
CONSTRAINT [DF_AddressCustomer_Address2] DEFAULT ('') FOR [Address2]
,
CONSTRAINT [DF_CustomerAddress_City] DEFAULT ('') FOR [City],
CONSTRAINT [DF_CustomerAddress_ProvinceState] DEFAULT ('') FOR
[ProvinceState],
CONSTRAINT [DF_CustomerAddress_Country] DEFAULT ('') FOR [Country],
CONSTRAINT [DF_CustomerAddress_ZipPostalCode] DEFAULT ('') FOR
[ZipPostalCode],
CONSTRAINT [DF_CustomerAddress_InternalNotes] DEFAULT ('') FOR
[InternalNotes],
CONSTRAINT [DF_CustomerAddress_Description] DEFAULT ('') FOR
[Description],
CONSTRAINT & #91;DF_CustomerAddress_ShippingInstructi
on] DEFAULT ('') FOR
[ShippingInstruction]
GO
CREATE INDEX [IX_CustomerAddressidCustomer] ON
[dbo].[CustomerAddress]([idCustomer]) WITH FILLFACTOR = 90 ON &
#91;PRIMARY]
GO
CREATE INDEX [IX_CustomerAddressTaxCode] ON
[dbo].[CustomerAddress]([idTaxCode]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [I_CustomerAddress] ON
[dbo].[CustomerAddress]([Address1], [Address2], [City])
WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [U_CustomerAttribute] UNIQUE NONCLUSTERED
(
[idCustomer],
[idAttribute]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT & #91;DF_CustomerAttributeDescription_Valu
eAttribute] DEFAULT
('') FOR [ValueAttribute]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]', N'[Customer].[
;Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]', N'[Customer].
1;Status]'
GO
setuser
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]',
N'[CustomerAddress].[Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]',
N'[CustomerAddress].[Status]'
GO
setuser
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [FK_Customer_Culture] FOREIGN KEY
(
[idDefaultLanguage]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress2] FOREIGN KEY
(
[idDefaultShippingAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress3] FOREIGN KEY
(
[idDefaultAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttribute] ADD
CONSTRAINT [FK_CustomerAttribute_Attribute] FOREIGN KEY
(
[idAttribute]
) REFERENCES [dbo].[Attribute] (
[id]
),
CONSTRAINT [FK_CustomerAttribute_Customer] FOREIGN KEY
(
[idCustomer]
) REFERENCES [dbo].[Customer] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] ADD
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cult
ure] FOREIGN KEY
(
[idCulture]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cust
omerAttribute] FOREIGN
KEY
(
[idCustomerAttribute]
) REFERENCES [dbo].[CustomerAttribute] (
[id]
) ON DELETE CASCADE
GOOn Mon, 05 Apr 2004 21:46:46 -0500, Florian Ion wrote:
>We have the following Select statement:
>SELECT DISTINCT TOP 100000 Customer.id
>FROM Customer LEFT JOIN CustomerAddress CA ON
>Customer.idDefaultAddress=CA.id
>WHERE 1=1
> and Customer.id IN (
> select idCustomer
> from CustomerAttribute left JOIN CustomerAttributeDescription ON
>CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
> where idattribute = 13844 AND ValueAttribute = 6
> )
Why are you using left join in the subquery? The test for
ValueAttribute = '6' (what you should use for varchar data - or use
CAST (ValueAttribute AS int) if you're not sure the '6' is formatted
consistently) will discard any rows that the left join included, so
you might just as well change it to an inner join. Good chance that
this will improve perfoprmance on this query!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
we have a very strange behavior of SQL Server that we try to understand.
We have the following Select statement:
SELECT DISTINCT TOP 100000 Customer.id
FROM Customer LEFT JOIN CustomerAddress CA ON
Customer.idDefaultAddress=CA.id
WHERE 1=1
and Customer.id IN (
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
)
When we run this select we get the desired results from the database.
However if we run only the Select that is in the Where clause, that is:
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
we get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'DCDB' to a column of data
type int.
which is somehow normal as the column ValueAttribute is a varchar column
.
What I do not understand is why in the first situation it works.
Obviously looking at the execution plan you can see that it is made
different in the 2 situation but it's SQL that decides upon what
execution plan it should chose.
Does anyone have any explanation for this behavior or could you point me
to some documentation regarding the way the Query Optimizer works?
Thanks,
Florian
Here is the tables structure: CREATE TABLE [dbo].[Customer] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[idDefaultShippingAddress] [int] NULL ,
[idDefaultAddress] [int] NULL ,
[idPrincipalContact] [uniqueidentifier] NULL ,
[idSalesPerson] [uniqueidentifier] NULL ,
[idCustomerParent] [uniqueidentifier] NULL ,
[Code] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WebSite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[InternalNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[DotNetMessenger] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[EntCreationDate] [datetime] NULL ,
[NumberEmployee] [int] NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[BankName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[BankProvinceState] [int] NULL ,
[BankAccountNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[BankRoutingNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[CreditCardType] [int] NULL ,
[CreditCardNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[CreditCardExpMonth] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardExpYear] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[idDefaultLanguage] [int] NOT NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idTaxCode] [int] NOT NULL ,
[idCurrency] [int] NOT NULL ,
[idTimeZone] [int] NOT NULL ,
[Address1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ProvinceState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
,
[ZipPostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[InternalNotes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[ShippingInstruction] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[ISO3166GEO] [int] NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttribute] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idAttribute] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttributeDescription] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomerAttribute] [int] NOT NULL ,
[idCulture] [int] NULL ,
[ValueAttribute] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [PK_AddressCustomer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttribute] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttributeDescription] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [DF_Customer_id] DEFAULT (newid()) FOR [id],
CONSTRAINT [DF_Customer_Name] DEFAULT ('') FOR [Name],
CONSTRAINT [DF_Customer_Phone] DEFAULT ('') FOR [Phone],
CONSTRAINT [DF_Customer_Fax] DEFAULT ('') FOR [Fax],
CONSTRAINT [DF_Customer_WebSite] DEFAULT ('') FOR [WebSite],
CONSTRAINT [DF_Customer_Email] DEFAULT ('') FOR [Email],
CONSTRAINT [DF_Customer_Notes] DEFAULT ('') FOR [InternalNotes],
CONSTRAINT [DF_Customer_DotNetMessenger] DEFAULT ('') FOR
[DotNetMessenger],
CONSTRAINT [DF_Customer_NumberEmployee] DEFAULT (0) FOR
[NumberEmployee],
CONSTRAINT [DF_Customer_BankName] DEFAULT ('') FOR [BankName],
CONSTRAINT [DF_Customer_BankCity] DEFAULT ('') FOR [BankCity],
CONSTRAINT [DF_Customer_BankAccountNumber] DEFAULT ('') FOR
[BankAccountNumber],
CONSTRAINT [DF_Customer_BankRoutingNumber] DEFAULT ('') FOR
[BankRoutingNumber],
CONSTRAINT [DF_Customer_CreditCardNumber] DEFAULT ('') FOR
[CreditCardNumber],
CONSTRAINT [DF_Customer_idDefaultLanguage] DEFAULT (1) FOR
[idDefaultLanguage]
GO
CREATE INDEX & #91;IX_CustomeridDefaultShippingAddress]
ON
[dbo].[Customer]([idDefaultShippingAddress]) WITH FILLFACTOR =
90 ON
[PRIMARY]
GO
CREATE INDEX [IX_CustomerDefaultAddress] ON
[dbo].[Customer]([idDefaultAddress]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [IX_CustomeridPrincipalContact] ON
[dbo].[Customer]([idPrincipalContact]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Customer_Code] ON [dbo].[Customer](
1;Code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Customer] ON [dbo].[Customer]([id]) WITH
FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [DF_CustomerAddress_Address1] DEFAULT ('') FOR [Address1]
,
CONSTRAINT [DF_AddressCustomer_Address2] DEFAULT ('') FOR [Address2]
,
CONSTRAINT [DF_CustomerAddress_City] DEFAULT ('') FOR [City],
CONSTRAINT [DF_CustomerAddress_ProvinceState] DEFAULT ('') FOR
[ProvinceState],
CONSTRAINT [DF_CustomerAddress_Country] DEFAULT ('') FOR [Country],
CONSTRAINT [DF_CustomerAddress_ZipPostalCode] DEFAULT ('') FOR
[ZipPostalCode],
CONSTRAINT [DF_CustomerAddress_InternalNotes] DEFAULT ('') FOR
[InternalNotes],
CONSTRAINT [DF_CustomerAddress_Description] DEFAULT ('') FOR
[Description],
CONSTRAINT & #91;DF_CustomerAddress_ShippingInstructi
on] DEFAULT ('') FOR
[ShippingInstruction]
GO
CREATE INDEX [IX_CustomerAddressidCustomer] ON
[dbo].[CustomerAddress]([idCustomer]) WITH FILLFACTOR = 90 ON &
#91;PRIMARY]
GO
CREATE INDEX [IX_CustomerAddressTaxCode] ON
[dbo].[CustomerAddress]([idTaxCode]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [I_CustomerAddress] ON
[dbo].[CustomerAddress]([Address1], [Address2], [City])
WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [U_CustomerAttribute] UNIQUE NONCLUSTERED
(
[idCustomer],
[idAttribute]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT & #91;DF_CustomerAttributeDescription_Valu
eAttribute] DEFAULT
('') FOR [ValueAttribute]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]', N'[Customer].[
;Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]', N'[Customer].
1;Status]'
GO
setuser
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]',
N'[CustomerAddress].[Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]',
N'[CustomerAddress].[Status]'
GO
setuser
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [FK_Customer_Culture] FOREIGN KEY
(
[idDefaultLanguage]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress2] FOREIGN KEY
(
[idDefaultShippingAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress3] FOREIGN KEY
(
[idDefaultAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttribute] ADD
CONSTRAINT [FK_CustomerAttribute_Attribute] FOREIGN KEY
(
[idAttribute]
) REFERENCES [dbo].[Attribute] (
[id]
),
CONSTRAINT [FK_CustomerAttribute_Customer] FOREIGN KEY
(
[idCustomer]
) REFERENCES [dbo].[Customer] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] ADD
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cult
ure] FOREIGN KEY
(
[idCulture]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cust
omerAttribute] FOREIGN
KEY
(
[idCustomerAttribute]
) REFERENCES [dbo].[CustomerAttribute] (
[id]
) ON DELETE CASCADE
GOOn Mon, 05 Apr 2004 21:46:46 -0500, Florian Ion wrote:
>We have the following Select statement:
>SELECT DISTINCT TOP 100000 Customer.id
>FROM Customer LEFT JOIN CustomerAddress CA ON
>Customer.idDefaultAddress=CA.id
>WHERE 1=1
> and Customer.id IN (
> select idCustomer
> from CustomerAttribute left JOIN CustomerAttributeDescription ON
>CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
> where idattribute = 13844 AND ValueAttribute = 6
> )
Why are you using left join in the subquery? The test for
ValueAttribute = '6' (what you should use for varchar data - or use
CAST (ValueAttribute AS int) if you're not sure the '6' is formatted
consistently) will discard any rows that the left join included, so
you might just as well change it to an inner join. Good chance that
this will improve perfoprmance on this query!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Wednesday, March 7, 2012
Query on YTD Distinct Count
Is it possible to calculate " YTD Distinct Count " of number of Customers who are been serviced by a agent. We have two dimensions for period...as Year and Month . Other dimension for agent heirarchy....
"Is their any way to access fact column in my MDX qry for calculated measure?".
Thanks !!
With one time key, at the (Year)month level, and one agent key in the fact table you should be able to achive this. You will have all the keys that you will need.
Regards
Thomas Ivarsson
|||Sasteam,
Were you able to accomplish this? I cannot get this to work for me. I keep ending up with the sum of the monthly distinct counts...which is not correct if the same customer was serviced in multiple months.
I would appreciate any sharing of info you may have.
Thanks.
|||njojo,
I assume you are doing this with a calculated measure. If you have your measure set up as a "distinct count" measure AND use the Aggregate() function in your calculation this should work. The aggregate() function in SSAS is "aware" of distinct count measures.
|||I can't seem to get Aggregate to work with YTD.
My measure is a distinct count: [Measures].[DC MctBrandID]
I ended up having to do this (which I found on another forum):
Count(NonEmptyCrossJoin(Descendants([MerchantBrand].CurrentMember,,Leaves)
,YTD([DateLevels].CurrentMember)
, {[Measures].[DC MctBrandID]},1))
I tried doing: Sum(YTD([DateLevels].CurrentMember),[Measures].[DC MctBrandID]).....which of course gave me the sum of the dc for each month in the year...not correct.
Then I did: Sum(YTD(Ancestor([DateLevels].CurrentMember,[DateLevels].[Year])), [Measures].[DC MctBrandID])....this number was correct but was for the whole year, not "to date"....still not right.
This is probably too much info and I apologize. I just want to do this calcuation the best possible way...my reports all have YTD and PTD requirements.
Thanks,
njojo
|||In that case I am guessing you are using AS 2000 (sorry, I had assumed SSAS 2005)
In AS2000, the aggregate function had a couple of issues, to the point where I don't think many people actually used it and you are probably left having to use the Count(NonEmptyCrossjoin()) solution.
The Sum(YTD(Ancestor(...))) function will always produce the full year figure as the Ancestor function is returning the member at the year level, which is making the YTD() function redundant, the cube is simply returning the distinct count from the year member.
|||
I am Nitin Sharma from SASTeam. The initial person who posted this query.
I solved this by designing a dimension in my database for each Period-Type (viz. YTD, Rolling 12 months, Period). The dimension which I had designed is a view on my base Year and Month period along with a key field to identify the type of Period for which we need to calculate base measure. The eg. for dimension data is as follows:
col1 | col2 | col3 | col4 | col5 |
2006 | FEB | 2006 | FEB | 1 |
2006 | FEB | 2006 | JAN | 2 |
2006 | FEB | 2006 | FEB | 2 |
2006 | FEB | 2005 | MAR | 3 |
2006 | FEB | 2005 | APR | 3 |
.
.
.
2006 | FEB | 2005 | DEC | 3 |
2006 | FEB | 2006 | JAN | 3 |
2006 | FEB | 2006 | FEB | 3 |
Using this dimension in my cube I have linked my fact with new dimension as follows: derived year (3rd col) to fact-year and derived month (4th col) to fact-month. Actual Year dimension is linked with my new dimension-year (1st col) and actual month dimension is linked with new dimension-month (2nd col). The last col is used to define the PeriodType....1 for Simple Period, 2 for YTD and 3 for Rolling 12 month.
Now my time constraints (Period-Type) problem is solved and I can calculate any DISTINCT COUNT or any base calculation on my fact usign this logic, and hence the problem of YTD Distinct Count is solved.
I hope this works for you.
Thanks & Best Regards,
Nitin Sharma
Query on YTD Distinct Count
Is it possible to calculate " YTD Distinct Count " of number of Customers who are been serviced by a agent. We have two dimensions for period...as Year and Month . Other dimension for agent heirarchy....
"Is their any way to access fact column in my MDX qry for calculated measure?".
Thanks !!
With one time key, at the (Year)month level, and one agent key in the fact table you should be able to achive this. You will have all the keys that you will need.
Regards
Thomas Ivarsson
|||Sasteam,
Were you able to accomplish this? I cannot get this to work for me. I keep ending up with the sum of the monthly distinct counts...which is not correct if the same customer was serviced in multiple months.
I would appreciate any sharing of info you may have.
Thanks.
|||njojo,
I assume you are doing this with a calculated measure. If you have your measure set up as a "distinct count" measure AND use the Aggregate() function in your calculation this should work. The aggregate() function in SSAS is "aware" of distinct count measures.
|||I can't seem to get Aggregate to work with YTD.
My measure is a distinct count: [Measures].[DC MctBrandID]
I ended up having to do this (which I found on another forum):
Count(NonEmptyCrossJoin(Descendants([MerchantBrand].CurrentMember,,Leaves)
,YTD([DateLevels].CurrentMember)
, {[Measures].[DC MctBrandID]},1))
I tried doing: Sum(YTD([DateLevels].CurrentMember),[Measures].[DC MctBrandID]).....which of course gave me the sum of the dc for each month in the year...not correct.
Then I did: Sum(YTD(Ancestor([DateLevels].CurrentMember,[DateLevels].[Year])), [Measures].[DC MctBrandID])....this number was correct but was for the whole year, not "to date"....still not right.
This is probably too much info and I apologize. I just want to do this calcuation the best possible way...my reports all have YTD and PTD requirements.
Thanks,
njojo
|||In that case I am guessing you are using AS 2000 (sorry, I had assumed SSAS 2005)
In AS2000, the aggregate function had a couple of issues, to the point where I don't think many people actually used it and you are probably left having to use the Count(NonEmptyCrossjoin()) solution.
The Sum(YTD(Ancestor(...))) function will always produce the full year figure as the Ancestor function is returning the member at the year level, which is making the YTD() function redundant, the cube is simply returning the distinct count from the year member.
|||
I am Nitin Sharma from SASTeam. The initial person who posted this query.
I solved this by designing a dimension in my database for each Period-Type (viz. YTD, Rolling 12 months, Period). The dimension which I had designed is a view on my base Year and Month period along with a key field to identify the type of Period for which we need to calculate base measure. The eg. for dimension data is as follows:
col1 | col2 | col3 | col4 | col5 |
2006 | FEB | 2006 | FEB | 1 |
2006 | FEB | 2006 | JAN | 2 |
2006 | FEB | 2006 | FEB | 2 |
2006 | FEB | 2005 | MAR | 3 |
2006 | FEB | 2005 | APR | 3 |
.
.
.
2006 | FEB | 2005 | DEC | 3 |
2006 | FEB | 2006 | JAN | 3 |
2006 | FEB | 2006 | FEB | 3 |
Using this dimension in my cube I have linked my fact with new dimension as follows: derived year (3rd col) to fact-year and derived month (4th col) to fact-month. Actual Year dimension is linked with my new dimension-year (1st col) and actual month dimension is linked with new dimension-month (2nd col). The last col is used to define the PeriodType....1 for Simple Period, 2 for YTD and 3 for Rolling 12 month.
Now my time constraints (Period-Type) problem is solved and I can calculate any DISTINCT COUNT or any base calculation on my fact usign this logic, and hence the problem of YTD Distinct Count is solved.
I hope this works for you.
Thanks & Best Regards,
Nitin Sharma
Subscribe to:
Posts (Atom)