Anoying bug...

Hello,
I have a report which used AS as a source. I have a report parameter and a
dataset with the following query:
="with
Member [Measures].[BGBNLUniqueName] as
'[Product].[Concern].currentmember.UniqueName'
member [Measures].[BGBNLDisplayName] as 'IIF(
[Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
Business Groups"", [Product].[Concern].Currentmember.Name)'
set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6920]} ' "
& IIF( Parameters!Product_BG.Label = "All Business Groups",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
")' ") & "
select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] } on
columns,
{ [ChosenProducts] } on rows
from [EDWH_SMRT_VIRT_SALESOUT]"
I have tested this in the mdx sample application and in order to work the
Parameter has to be enclosed by double quotes. When I do that I always get
the errormessage from AS that the value from the parameter is not enclosed
with double quotes.
Any Ideas? How can I escape the double quote?
Regards,
QI think the third part of the expression should be this:
& IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
[ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
""")' ") & "
Note: I have added "" around your parameter string concatenation.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> Hello,
> I have a report which used AS as a source. I have a report parameter and a
> dataset with the following query:
> ="with
> Member [Measures].[BGBNLUniqueName] as
> '[Product].[Concern].currentmember.UniqueName'
> member [Measures].[BGBNLDisplayName] as 'IIF(
> [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> Business Groups"", [Product].[Concern].Currentmember.Name)'
> set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> [Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6920]} ' "
> & IIF( Parameters!Product_BG.Label = "All Business Groups",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> ")' ") & "
> select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> on
> columns,
> { [ChosenProducts] } on rows
> from [EDWH_SMRT_VIRT_SALESOUT]"
>
> I have tested this in the mdx sample application and in order to work the
> Parameter has to be enclosed by double quotes. When I do that I always get
> the errormessage from AS that the value from the parameter is not enclosed
> with double quotes.
> Any Ideas? How can I escape the double quote?
> Regards,
> Q|||Hi Robert,
I have tried this but still get the error.
Q
"Robert Bruckner [MSFT]" wrote:
> I think the third part of the expression should be this:
> & IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
> [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
> """)' ") & "
> Note: I have added "" around your parameter string concatenation.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> > Hello,
> >
> > I have a report which used AS as a source. I have a report parameter and a
> > dataset with the following query:
> >
> > ="with
> > Member [Measures].[BGBNLUniqueName] as
> > '[Product].[Concern].currentmember.UniqueName'
> > member [Measures].[BGBNLDisplayName] as 'IIF(
> > [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> > Business Groups"", [Product].[Concern].Currentmember.Name)'
> >
> > set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> > [Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6920]} ' "
> >
> > & IIF( Parameters!Product_BG.Label = "All Business Groups",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> > ")' ") & "
> >
> > select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> > on
> > columns,
> > { [ChosenProducts] } on rows
> >
> > from [EDWH_SMRT_VIRT_SALESOUT]"
> >
> >
> > I have tested this in the mdx sample application and in order to work the
> > Parameter has to be enclosed by double quotes. When I do that I always get
> > the errormessage from AS that the value from the parameter is not enclosed
> > with double quotes.
> >
> > Any Ideas? How can I escape the double quote?
> >
> > Regards,
> > Q
>
>

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentid
try:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||To determine which is most efficient, you need to look at the execution plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>
|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentidtry:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0247_01C6E0CB.6186E090
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To determine which is most efficient, you need to look at the execution =plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID FROM Students s
JOIN CourseStudent c
ON s.StudentID =3D c.StudentID
WHERE c.StudentID IS NULL
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message =news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> > I have the following query
> > select studentid from students where studentid not in (select =studentid from
> coursestudent)
> > is there some way I could write this with using the 'not in'?
> > student is a table of students. coursestudent is a join table of =courses to
> students. I'm trying
> to find those students who are not registered for a course. While =the
> above query works,
> I'd like to find a more efficient query.
> > create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> > create table coursestudent
> (courseid int
> studentid int)
> > notes:
> - non clustered index on courseid and non clustered index on studentid =exist
> - there is a foreign key relationship from coursestudent's studentid =to
> student studentid
> > > >
--=_NextPart_000_0247_01C6E0CB.6186E090
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

To determine which is most efficient, =you need to look at the execution plans, examine the I/O, and execution =times.
Here is an alternative:
SELECT StudentID =FROM Students s JOIN CourseStudent c ON s.StudentID =3D =c.StudentIDWHERE c.StudentID IS NULL
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker" =wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...> =Hi> > I have the following query> > select studentid =from students where studentid not in (select studentid from> coursestudent)> > is there some way I could write this =with using the 'not in'?> > student is a table of students. coursestudent is a join table of courses to> students. I'm trying> to find those students who are not registered for a course. While the> above query works,> I'd =like to find a more efficient query.> > create table =student> (studentid int identity primary key,> name varchar(50),> address1varchar(50),> address2 varchar(50),> city varchar(50),> state varchar(4),> zip varchar(11))> => create table coursestudent> (courseid int> studentid int)> > notes:> - non clustered index on courseid =and non clustered index on studentid exist> - there is a foreign key =relationship from coursestudent's studentid to> student studentid> => > >

---

--=_NextPart_000_0247_01C6E0CB.6186E090--|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
>> Hi
>> I have the following query
>> select studentid from students where studentid not in (select studentid
> from
>> coursestudent)
>> is there some way I could write this with using the 'not in'?
>> student is a table of students. coursestudent is a join table of courses
> to
>> students. I'm trying
>> to find those students who are not registered for a course. While the
>> above query works,
>> I'd like to find a more efficient query.
>> create table student
>> (studentid int identity primary key,
>> name varchar(50),
>> address1varchar(50),
>> address2 varchar(50),
>> city varchar(50),
>> state varchar(4),
>> zip varchar(11))
>> create table coursestudent
>> (courseid int
>> studentid int)
>> notes:
>> - non clustered index on courseid and non clustered index on studentid
> exist
>> - there is a foreign key relationship from coursestudent's studentid to
>> student studentid
>>
>>
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentidtry:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||To determine which is most efficient, you need to look at the execution plan
s, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.co
mcast.com...
> Hi
>
> I have the following query
>
> select studentid from students where studentid not in (select studentid fr
om
> coursestudent)
>
> is there some way I could write this with using the 'not in'?
>
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
>
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
>
> create table coursestudent
> (courseid int
> studentid int)
>
> notes:
> - non clustered index on courseid and non clustered index on studentid exi
st
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
>
>|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

Another SQL Query!

Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
SimonTry:
select
f.id
from
Fixtures f
where not exists
(
select
*
from
Results r
where
r.fixture_id = f.id
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
Simon|||Select f.id
From Fixtures f
Left Join Results r on f.id = r.fixture_id
Where r.id Is Null
Order By f.id
Tom
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
> Dear All,
> I have two tables in my database, part of a tennis league results and
> fixtures website I am putting together.
> Results
> id fixture_id home_team_rubbers away_team_rubbers
> 1 229 2 2
> 2 253 2 2
> 3 265 1 3
> 4 230 2 2
> Fixtures Table
> id home_team_id away_team_id match_date
> 229 20 26 2006-05-03
> 230 20 29 2006-05-31
> 231 20 45 2006-05-17
> 232 20 78 2006-06-28
> 233 20 79 2006-07-26
> 234 20 89 2006-07-12
> 235 26 20 2006-06-20
> 236 26 29 2006-07-25
> 237 26 45 2006-07-11
> 238 26 78 2006-05-16
> 239 26 79 2006-06-13
> 240 26 89 2006-05-30
> 253 78 20 2006-05-09
> 265 89 20 2006-05-23
> What I want to do it list all the fixture (based on fixure id) that
> don't have a result associated with the fixture through an SQL query.
> The resulting output from the query that I desire is the following.
> Query Output
> fixture_id
> 231
> 232
> 233
> 234
> 235
> 236
> 237
> 238
> 239
> 240
> Any ideas how I can do this?
> Cheers,
> Simon
>|||Thanks guys, much appreciated!

another sql query problem

I have a table like this

DESCRIPTION DEBI CREDIT
-----------
Deposit 0.00 100.00
Withdraw 10.00 0.00
Payroll 0.00 230.00
Withdraw 50.00 0.00

How can i make an query that this table look like this

ID DESCRIPTION DEBI CREDIT Current Balance
------------------
1. Deposit 0.00 100.00 100.00
2. Withdraw 10.00 0.00 90.00
3. Payroll 0.00 230.00 320.00
4. Withdraw 50.00 0.00 270.00

Please helpTry this:

SELECT TOP 100 PERCENT QTest.ID, QTest.Description, QTest.Debit, QTest.Credit, SUM(QTest.Credit) - SUM(QTest.Debit) AS [Current Balance]
FROM QTest,
(SELECT TOP 100 * FROM (SELECT ID, Credit, Debit FROM QTest) QTest_1)
AS QTest_1
WHERE QTest.ID >= QTest_1.ID
GROUP BY QTest.ID, QTest.Description, QTest.Debit, QTest.Credit
ORDER BY QTest.ID

where QTest is yout table

Originally posted by bashka_abdyli
I have a table like this

DESCRIPTION DEBI CREDIT
-----------
Deposit 0.00 100.00
Withdraw 10.00 0.00
Payroll 0.00 230.00
Withdraw 50.00 0.00

How can i make an query that this table look like this

ID DESCRIPTION DEBI CREDIT Current Balance
------------------
1. Deposit 0.00 100.00 100.00
2. Withdraw 10.00 0.00 90.00
3. Payroll 0.00 230.00 320.00
4. Withdraw 50.00 0.00 270.00

Please help

another sql query ?

I'm trying to do a mass update, w/different values: I have an Approval Table with the columns, FacilityID, CompanyID, EmployeeID, and SupervisorID. I would like to take the Users SuperID in the [User] Table who have a roleid= 'Supervisor' and place it in the Approval Table. However in order to know which super matches which employee you must match the Employee tables DeptID to the User Tables Department field.

I did the following and it gives me an error:

UPDATE Approval SET SupervisorID = (select u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')

The error is:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The select gives me the right data--this much I've checked.

You sub-query is probably returning more than 1 row in some cases. You have to make sure you only get one result row. Try:

UPDATE Approval SET SupervisorID = (selectTOP 1u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')|||Well see that's what I'm confused about--I would like to update all of the SupervisorID's in the table at once. There are 125 rows in the Approval Table, and about 10 different superviosrID's in the User Table. Am I going about this wrong?|||

You have to make sure to provide a condition where the table you're updating has one-to-one relationship with the sub-query value returned for each row. In your query, you have nothing that defines the relationship between the Approval table (one you're updating) and other tables in the sub-query. I can't tell you what it should look like since I have no clue on what the tables look like. There must be some column in the Approval table that links to other tables being referenced in your sub-query, so provide that relationship in your where clause.

|||

You've almost got it. Problem is your subselect is returning ALL the supervisors for every department. Your statement therefore is trying to set the Supervisor in the Approval table to every supervisor, which...You can't do.

You want to set the supervisor in the approval table to a specific supervisor. In order to do that, your subselect needs to know how it relates to the rows in the approval table. In english, that means you need something else in your WHERE clause in the subselect, something along the lines of... AND e.EmployeeID=approval.EmployeeID.

|||I see I got it now--your addition of query code worked great. Appreciate the help.|||Ok--since I'm stuck I might as well keep on asking--this is a new SQL query I have an SP already and it's the following:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
LEFT JOIN EmployeeEval ev --this left join will return all rows from Employee, but only rows from Eval where the employee is in.
ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = @.Period
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND (e.DeptID = @.deptID) and Status = 'Active'
GO

So now I need to incorporate my Approval Table, we're trying to get rid of being dependent on finding employees based on Dept, b/c we need some supervisors to be in charge of multiple departments.

So I need to Pull back the Info in the SELECT above and keep the EmployeeEval stuff. As well I need to remove the Dept data at the bottom of the above query. My approval table has the following columns: FacilityID, CompanyID, EmployeeID, and SupervisorID so I was thinking do something like this:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e LEFT JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = 175
INNER JOIN Approval a ON e.employeeid = a.employeeid
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND e.Status = 'Active' AND a.supervisorID = '09246'
GO

But it's returning 0 rows--I have one person in the approval table with supervisorID set to '09246'. they have no data in the employeeeval table. But in my where clause i have the ev.employeeid is null which should still allow 4 them to be brought back--let me know if this doesn't make sense.|||well I guess my query was right--I just didn't have my settings in the new table set up right--it's now showing up. thanks for all the help.

another sql query

Hi All,

I'm really struggling to figure out and sql query that will retrieve the first 30 characters of a database field. My plan is to have a brief preview of the text and then have ...[more] afterwards. So far I have the below (text is the field I'm trying to only retrieve the first 30 characters):-

SELECT id, title, text FROM w_pages WHERE ID <> '1' ORDER BY ID

I've set the query to not retrieve id 1 as that's the text of my homepage. I've attempted LEFT(text,30) as I thought that was the way to do it, but it didn't work for me :(

Also, can these types of queries be used with sql2000 'ntext' database fields?

Many thanks in advance.

Regards,

RichI imagine the ntext is your problem. From Books online:
LEFT ( character_expression , integer_expression )

Arguments
character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that can be implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive whole number. If integer_expression is negative, a null string is returned.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]))

Giving the EXACT error message you are getting would really help quite a bit.|||Thanks for the speedy reply.

I'm still new to asp.net and sql queries and I appreciate all your help :)

I've tried what you suggested and now it's giving the below error message:-

-------

Server Error in '/' Application.
------------------------

The left function requires 2 arguments.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The left function requires 2 arguments.

Source Error:

Line 121: Dim resultsDataSet as New DataSet()
Line 122: Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(sql, myConnection)
Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124: Content.DataSource = resultsDataSet
Line 125: Content.DataBind()

Source File: E:\kunden\homepages\1\articles.aspx Line: 123

Stack Trace:

[SqlException: The left function requires 2 arguments.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.articles_aspx.GetContent() in E:\kunden\homepages\1\articles.aspx:123
ASP.articles_aspx.BindData() in E:\kunden\homepages\1\articles.aspx:107
ASP.articles_aspx.Page_Load(Object Sender, EventArgs E) in E:\kunden\homepages\1\articles.aspx:13
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +742

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.910

--------

Regards,

Rich|||OK. Sorry about that.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]),30)|||Thanks for that, a different error now (below) which is confusing, as I know the database field exists.

Sorry to keep bugging you with this problem.

Regards,

Rich

-----------------------------

Server Error in '/' Application.
------------------------

A field or property with the name 'text' was not found on the selected datasource.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: A field or property with the name 'text' was not found on the selected datasource.

Source Error:

Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124:Content.DataSource = resultsDataSet
Line 125: Content.DataBind()
Line 126:End Sub
Line 127:|||Then, perhaps the column name in the table is not text?|||Very strange, I just checked the column name and it's definately 'text'.

Oh well, I'll have a play about with it and see what happens.

Thanks for all your help, it's much appreciated.

Regards,

Rich|||Got it working :)

I used:-

SELECT id, pagedate, title, show, LEFT(CONVERT(nvarchar(300),[text]),300) as '111' FROM w_pages WHERE id <> '1' AND show = 'yes' ORDER BY ID DESC;

And then changed the datafield in my datagrid to '111' instead of 'text'

Thanks again for your help :)

Regards,

Rich

Another Slow Execution Plan with sp_prepare

We are having a similar problem. We have a 3rd party application that uses
sp_prepare and then executes it. The problem is the query plan from the
sp_prepare statement is different than if you run the statement using
sp_execute or sp_executesql. I run the following statements:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This takes almost a minute to return.
Then I run this statement:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
DBCC Freeproccache
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This returns in under 1 second. The only difference is the freeproccache.
The first sp_execute is using the store query plan from cache. The second i
s
forced to compile a new execution plan which is much faster.
Does anyone know why the sp_prepare query execution plan would be slower?
Shouldn't it create the same plan?
Also, how did you change sp_prepare to use varchar instead of nvarchar? I
get an error stating the sp_prepare expects @.statement of type
ntext/nchar/nvarchar.
Thanks for any help.
Doug Matney
"lorinda" wrote:

> This makes sense. Looks like I will have to find a work around. Thanks
> again for your help!
>dmatney (dmatney@.discussions.microsoft.com) writes:
> We are having a similar problem. We have a 3rd party application that
> uses sp_prepare and then executes it. The problem is the query plan
> from the sp_prepare statement is different than if you run the statement
> using sp_execute or sp_executesql. I run the following statements:
>...
> This returns in under 1 second. The only difference is the
> freeproccache. The first sp_execute is using the store query plan from
> cache. The second is forced to compile a new execution plan which is
> much faster.
> Does anyone know why the sp_prepare query execution plan would be slower?
> Shouldn't it create the same plan?
Since I have never understood the point with sp_prepare, or what it really
achieves, this had me stumped at first, but I think I know the answer.
SQL Server is fond of parameter sniffing. This means that when you
run something that has parameters, be that a stored procedure or
sp_executesql, it looks at the parameter values and take these as
guidance for the plan.
But when you run sp_prepare there are no actual parameter values. Still
SQL Server builds a plan this point, using standard assumptions. Your
query includs a lot of >=. I believe the standard assumption here is
a 30% hit-rate. This is far above the limit where a non-clustered index
is deemed to be more expensive than a table scan.
When you flush the cache, there is no plan for sp_execute to use, so
a new plan has to be created, and since the parameter values are now
available, the optimizer can "sniff" them, and build a plan that better
fits the actual values. Presumably, the input values are close to the
edges.
Since this is from a third-party app, I guess your options to address
this are limited. You could investigate if you could make any of the
involved indexes clustered, but assuming that there is a clustered index
already, this could have ramifications elsewhere in the application.
If you are on SQL 2005, you could add a plan guide, which is quite an
advanced exercise.

> Also, how did you change sp_prepare to use varchar instead of nvarchar? I
> get an error stating the sp_prepare expects @.statement of type
> ntext/nchar/nvarchar.
I guess you don't. It's the same with sp_executesql. The parameter is
ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
procedure, there is no implicit conversion from varchar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I think you are correct about the parameter sniffing. I have tried
creating a plan guide using a Recompile hint and it works for this particula
r
sp_prepare but since the application doesn't use parameters for all values (
the OrderID in clause uses real values) I can't get it to work every time.
I'm going to do a little more research and see if we can't convince the
application developer to use a different method. Thanks again for your help
.
"Erland Sommarskog" wrote:

> dmatney (dmatney@.discussions.microsoft.com) writes:
> Since I have never understood the point with sp_prepare, or what it really
> achieves, this had me stumped at first, but I think I know the answer.
> SQL Server is fond of parameter sniffing. This means that when you
> run something that has parameters, be that a stored procedure or
> sp_executesql, it looks at the parameter values and take these as
> guidance for the plan.
> But when you run sp_prepare there are no actual parameter values. Still
> SQL Server builds a plan this point, using standard assumptions. Your
> query includs a lot of >=. I believe the standard assumption here is
> a 30% hit-rate. This is far above the limit where a non-clustered index
> is deemed to be more expensive than a table scan.
> When you flush the cache, there is no plan for sp_execute to use, so
> a new plan has to be created, and since the parameter values are now
> available, the optimizer can "sniff" them, and build a plan that better
> fits the actual values. Presumably, the input values are close to the
> edges.
> Since this is from a third-party app, I guess your options to address
> this are limited. You could investigate if you could make any of the
> involved indexes clustered, but assuming that there is a clustered index
> already, this could have ramifications elsewhere in the application.
> If you are on SQL 2005, you could add a plan guide, which is quite an
> advanced exercise.
>
> I guess you don't. It's the same with sp_executesql. The parameter is
> ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
> procedure, there is no implicit conversion from varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Another simple SQL query (MS Access)

Hi. I'm having trouble doing another SQL query. It needs to compare a height against the average height of a group. If it is larger, than it is shown. I can create the sub query but it returns an error when going to the outer query.

Select avg(height)
from Materials
group by MCode

What is an outer query that would compare a single height against that groups average height and display the rows if the single height is greater?oh yeah, another thing. My outer query select can only have 2 columns (material, height)|||select material
, height
from Materials ZZ
where height
> ( select avg(height)
from Materials
where MCode = ZZ.MCode )

Another SelectCommand Distinct problem

why i type the select command like below the disctinct doesnt work? the query stil show all the Category i hav so how do i fix it??

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC">

<SelectParameters>

<asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

Hi

You may want to try

"SELECT DISTINCT Category FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC"

The SELECT shown, viz., "Category, ID" brings all the distinct combinations of Category AND ID

Fouwaaz

|||

it work using singel feild.lets say if i select category, Title and weblink but i oni wan DISTINCT Category how do i change the code??

SELECT DISTINCT Category, Title,Weblink FROM Bookmarks WHERE (UserName = @.username) AND (Category = 'Top1' OR Category = 'Top2' OR Category = 'Top3' OR Category = 'Top4' OR Category = 'Top5')

now the distinct is handle 3 feild how to i make the distinct handle 1 feild?? bcoz i was trying to the output like this:

Original Data

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Output is the title become weblink...

|||

Hi

Sorry, it looks like I did not understand your question. This is the output you have shown

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Now, could you show the output that you would like to get?

Thanks

Fouwaaz

Another question please

We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.

My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.

Please help optimize my slow query!

Database schema is:

Column Name

Data Type

Length

Allow Nuls

1

msrepl_tran_version

uniqueidentifier

16

0

0

[time]

GMTtime (int)

4

0

0

msec

int

4

1

0

offset

GMToffset (smallint)

2

0

0

type

nameType (varchar)

15

1

0

host

nameType (varchar)

15

1

0

process

nameType (varchar)

15

1

0

dbName

nameType (varchar)

15

1

0

ptName

nameType (varchar)

15

1

0

description

descType (varchar)

47

1

0

rtuName

nameType (varchar)

15

1

0

groupName

nameType (varchar)

15

1

0

message

msgType (varchar)

131

0

0

fgInt

tinyint

1

1

0

sevInt

tinyint

1

1

0

key1

int

4

1

0

key2

int

4

1

0

spooler

tinyint

1

1

- The database view eventView that's used by my queries:

SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time]
FROM dbo.summary

- Fast totals query:

select
(select count(*)
from [event].[dbo].[eventView]) as Events,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1)as Low,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2)as Medium,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3) as High,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4) as Low1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6) as Medium1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8) as High1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9) as High2

- Slow daily query:

declare @.myDay datetime
declare @.begDay datetime

declare @.myTable
table( myDay datetime,
Events int,
Low int, Medium int,
High int, Low1 int, Medium1 int,
High1 int, High2 int )

select @.myDay = getdate()

select @.begDay = convert(datetime, '10/01/2006')

while @.begDay <= @.myDay
begin

insert into @.myTable (myDay) values (convert(varchar(20), @.begDay, 101))

update @.myTable set Events =
(select count(*) as Events
from [event].[dbo].[eventView]
where
convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Low =
(select count(*) as Low
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Medium =
(select count(*) as Medium
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High =
(select count(*) as High
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Low1 =
(select count(*) as Low1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Medium1 =
(select count(*) as Medium1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High1 =
(select count(*) as High1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High2 =
(select count(*) as High2
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

select @.begDay = dateadd(day,1,@.begDay)

end

select * from @.myTable

Try this is a replacement for your fast query.

select Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'

It should return the same results, but I bet it's at least 10 times faster.

I'm looking at your slow query now. I would bet there it can be improved a lot. One thing I see is that you are doing a lot of converts, which appear to be unnecessary. For example... where myDay = convert(varchar(20), @.begDay, 101) myDay is a date time field (with time removed/set to 0). @.begDay is also a DateTime variable with time removed. Because of this, you should be able to remove the convert from your where clauses.

|||

I'm not sure, but this may be a suitable replacement for the slow query. Please double and triple check the results before putting this in to production.

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

I would be interested to know if this works for you, and also what the difference in time is between your query and the one I posted here.

|||

Shazam! This worked like a charm - I'm stunned really.

Processed the 232 day's worth of data in my system in 6 seconds.

That other one takes about 60 seconds / day's data - so roughly 4 HOURS to do that same job.

I know I'm just a hacker when it comes to doing this stuff. Thanks a LOT for the help. This makes sense to me!

|||

The events are counted using a different criteria - they should be calculated just as a count of all the daily messages, not restricted by the where clause.

select count (*) from [event].[dbo].[eventview] as Events

With the code you are suggesting Events are counting all the messages that are of Type = 'alarm'. How to specify a different critieria within that same query?

|||

Are you saying this is missing?

and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))

Why not add it to mastros' query and then throw that into a stored proc? Just like you started with, but with mastros' query.

|||

Instead of "Count(*) As Events", use a sub-query like:

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
(select count (*) from [event].[dbo].[eventview]) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

|||The help is VERY much appreciated!

I've been struggling with this one for weeks.

What's a good way to learn more about SQL?|||Pick up a SQL book, take a class, and/or hang out on this forum!

Really, though, the best way to learn SQL is to keep working with your data, day in and day out.|||

Making that change (counting events as a sub query) gives me the total of all events.

Sorry - I need a daily total and I'm not clear how to couple that back into the rest of the query.

|||

Instead of putting type='alarm' in the where clause, put it in the case statements, like this...

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
count (*) As Events,
Sum(Case When SevInt = 1 And Type='alarm' Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 And Type='alarm' Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 And Type='alarm' Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 And Type='alarm' Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 And Type='alarm' Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 And Type='alarm' Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 And Type='alarm' Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

Your performance may drop a little because you are processing more rows, but some times that is necessary to get the correct data.

|||Yes it is a tad slower - 8 seconds to query the database versus 6 in the earlier one...|||Do you get the correct results?|||

It gets the job done, thanks.

I still don't quite understand this "add another query with different criteria" thing. So I'm going to work on this some more until I can figure out how to get an additional daily value for

select count(*) from [event].[dbo].[eventView] where [message] not like '%NORMAL state%' and [message] not like '%restored - normal%' and [message] not like '%communication%restored%' and [message] not like '%PLM - NORMAL%' and [type] = 'alarm' as Alarms