Query Syntax: level

Showing posts with label level. Show all posts

Friday, March 30, 2012

Query.

SELECT TOP 10 *
FROM (
SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC
)
ORDER BY PA_VPatientID DESC
Error :-
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.
I did it through the following query
SELECT TOP 10 *
FROM (SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC) a
ORDER BY PA_VPatientID
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>
|||Why are you doing that? Simply using:
SELECT TOP 10 * FROM Patient ORDER BY PA_VPatientID DESC
would be more efficient.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>

Query!

Hi All,

Reporting service provides export to excel functionality at report level.

I want to provide the exprot to excel functionality at line item level.

Regards

Abdul

Hello,

Do yow want this functionality on your web page??

|||

I want to provide the exprot to excel functionality at line item level.

NO, the build-in exporting function doesn't support what you want.

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)

when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))

This is not a bug. This is due to the order in which certain expressions are evaluated. The query optimizer is free to move expressions around to determine the optimum plan. You could get this error in SQL Server 2000 also but it is more easy to hit in SQL Server 2005 due to newer optimizations to match computed column indexes/indexed views. This is documented in the behavior changes topic in BOL. See the link below and search for "Expressions in queries":

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

Basically, you need to use CASE expression in all the places where you are doing the CONVERT(float) with ISNUMERIC check in the WHEN clause. You will of course get slightly bad performance due to the complexity of the expressions. The correct approach however is to normalize the table and not mix different data domains in the same column.

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

sql

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

Monday, March 26, 2012

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

Friday, March 30, 2012

Query.

Query!

Query Works in SQL 2000 but not in SQL 2005

Query Works in SQL 2000 but not in SQL 2005

Monday, March 26, 2012

QUERY WITH COUNT

QUERY WITH COUNT

Query Syntax

Blog Archive

About Me