Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

Return COMPUTE from stored proc

I'm trying to figure out how to get my stored proc below to just return the result for COMPUTE only:
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select (cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='06'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
When my stored proc is run, it should only return one value, the result of COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100). Right now however, it returns only the list from the select (below) which I don't want, I just want the COMPUTE value returned which is one value (the sum of the items below):
27256.000000
14218.000000
0.000000
14930.000000
54824.000000
148616.666667
73320.000000
85956.000000
105507.500000
67911.904762
55276.190476
14467.500000
5985.000000
20910.000000
118784.000000
5340.000000
5295.000000
567.500000

It is not possible to modify behavior of COMPUTE. It is a non-standard / proprietary extension so you should avoid using it. Instead write your own query using say ROLLUP/CUBE operator to get the sum at the desired levels. This will allow you to control the output rows by filtering on GROUPING function return values. See BOL for examples.|||

Thanks figured that after spending half my day trying. I found a way to sum what I needed and return one GT without compute:

Select Total = SUM(gt)

FROM

(Select DistinctCustomerName,

CustomerNumber,

FeeGoal_AZ AS FG,

FeeSchedule,

(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

from DCR WHERE branch='00002'

group by CustomerName,

CustomerNumber,

FeeGoal_AZ,

FeeSchedule

) as dTable

Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>

Retrieving multiple values from stored procedure: parameter and recordset

Hello,

The problem below appears to be a very common problem, but despite having tried every solution I can find offered online, the solution continues to elude me. Any tips you could offer would bemuchappreciated.

Problem: I'm successfully retrieving a recordset from a SQL Server 2000 stored procedure, but don't know how to retrieve an Output Parameter as well.

Components:

a simple Web Form with a Text Box (txtOutput) and a GridView (dgOutput). (Visual Web Developer Express 2005)
a simple stored procedure which - when executed in the Studio Output window - returns the correct output.Running [dbo].[usp_Test] ( @.Region = <NULL>, @.StartDate = <NULL> ).

State Total
-------- ----
ACT 27
NSW 26
NT 6
QLD 20
SA 44
TAS 17
VIC 28
WA 48
No rows affected.
(8 row(s) returned)
@.StartDate = 27/11/2006
@.RETURN_VALUE = 0
Finished running [dbo].[usp_Test].
the code behind the form, to call the stored proc and bind the grid to the output of the stored proc. 'Already declared and populated strConn and strSelect
Dim conn As New SqlConnection(strConn)
Dim storedProc As New SqlCommand
With storedProc
.CommandType = CommandType.StoredProcedure
.CommandText = strSelect
.Connection = conn
End With
Dim returnParam As SqlParameter
returnParam = New SqlParameter("@.StartDate", SqlDbType.DateTime, 30)
returnParam.Direction = ParameterDirection.Output
storedProc.Parameters.Add(returnParam)

Dim dr As SqlClient.SqlDataReader
conn.Open()
dr = storedProc.ExecuteReader
dgOutput.DataSource = dr
dgOutput.DataBind()
dr.Close()
conn.Close()

Situation:

the GridView successfully displays the 8-row recordsetI don't know how to display the Output Parameter ("@.StartDate") in the Text Box. Without success I've tried variations on:
txtOutput.Text = storedProc.Parameters("@.StartDate").Value.ToStringtxtOutput.Text = returnParam.Value.ToStringusing dr.NextResultIf possible, I would like to be able to display the 'number of row(s) returned', which is already being returned (as printed above).

Any assistance would be sincerely appreciated - it's been a long day of trying. Thank you for your time.

- Sarah

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

Hi Dinakar,

Thank you for looking at my problem Unfortunately the same problem is still happening.

The stored proc is already returning the output parameter with the correct value of27/11/2006(as shown in the output in my first post), but any time I try to retrieve it, it seems to have a null value.

In my VB code, I've changed my parameter creation to match your suggested code.

When I use the new DateTime variable as you suggest, and set it tostoredProc.Parameters("@.StartDate").Value, it's value is set to 1/01/0001 12:00:00 AM .

Any suggestions?

Thank you again for your time.

|||Can you post your stored proc code?|||

CREATE PROCEDURE usp_Test(
@.Region AS VARCHAR(8) = NULL, --Optional
@.StartDate DATETIME OUTPUT
)
AS

BEGIN
SET @.StartDate =DATEADD(Week, DATEDIFF(Week,0,GETDATE()), 0) --Monday of current week

SELECT Loc.LocationState AS State,
COUNT(Appt.ApartmentID) AS Total
FROM Apartment AS Appt
LEFT JOIN ReportLocation AS Loc ON Appt.LocationID = Loc.LocationID
WHERE (( Appt.InsertedOn >= @.StartDate) AND
(Appt.InsertedOn <= GETDATE()) AND
((@.Region IS NULL) OR (Loc.LocationState LIKE @.Region)) AND
(Appt.isBreak = 0) AND
(Appt.isBlock = 0) AND
(Appt.isCancelled = 0)
)
GROUP BY Loc.LocationState
ORDER BY Loc.LocationState
END
GO

|||Is there anything else it might be? I'm happy to post any of my code, which might expose the cause.|||

Allright, after a long long time, I wrote up a quick windows app. I used a datagridview and called a stored proc which returns a result set through a SELECT statement. The stored proc also has an OUTPUT parameter. I was able to retrieve both.

alter proc dbo.test_dk @.fileidint, @.rowcountint output as begin select fileid, orderid, creationdatefrom servicefilewhere fileid = @.fileidselect @.rowcount =@.@.rowcountend

And the code for the windows app is:

Dim objconAs SqlConnection objcon =New SqlConnection("server=...")Try If objcon.State = 0Then objcon.Open()Dim dsAs New DataSetDim daAs SqlDataAdapter =New SqlDataAdapter da.SelectCommand =New SqlCommand da.SelectCommand.CommandType = CommandType.StoredProcedure da.SelectCommand.CommandText ="test_dk"Dim sqlparamAs New SqlParameter("@.fileid", SqlDbType.Int) sqlparam.Value = 7035833Dim sqlparam2As New SqlParameter("@.rowcount", SqlDbType.Int) sqlparam2.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(sqlparam) da.SelectCommand.Parameters.Add(sqlparam2) da.SelectCommand.Connection = objcon da.Fill(ds,"servicefile") DataGridView1.DataSource = ds DataGridView1.DataMember ="servicefile" TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").ValueCatch excAs Exception MsgBox(exc)Finally If objcon.State = ConnectionState.OpenThen objcon.Close()End If'objCon.Dispose()End Try

|||

That worked beautifully - you're a champion, Dinakar! Some day they'll sing songs about you.

Thank you very much for your time and help.

|||You are welcome.|||

ndinakar:

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

I'm trying to follow your example but on this line..

Your line

TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").Value

My line

lbl_ERROR.Text = da.SelectCommand.Parameters("@.RETURNError").Value;

I get a build error.. Error 1 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

sql

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
****************************************
*/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unex
pected behaviors, this is why it
will be removed in some future release. Did you consider using the modern ou
ter join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.goog
le.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427,210504,1)
insert into tbl1(oldID, newID, refID) values(1953,210514,0)
insert into tbl1(oldID, newID, refID) values(646,210486,3)
insert into tbl1(oldID, newID, refID) values(650,210487,4)
insert into tbl1(oldID, newID, refID) values(749,210491,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427210504NULL
1953210514NULL
646210486NULL
650210487NULL
749210491NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/
I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.co m...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

Retrieving data in a Multiple Record Scenario

I have table "student" and it has 3 fields "Id", "Name" and "JoinDt".

Its contents are given below

Id Name JoinDt

1 One 1/1/2005

2 Two 2/2/2006

3 Three 3/3/2007

When I tried to execute the following query

declare @.Id int
declare @.Name varchar(50)

Select @.Id =Id, @.Name=Name From student

Where JoinDt>'1/1/2006' Order By JoinDt

Select @.Id ,@.Name

Iam getting the result like

3 Three

That means the query returns the value of last record in that cursor.

My doubt is whether this sort of queries will provide consistent results or not.

Yes the above query always give the last record.

First it will assign the 1 row value, then 2 row value ..... finally it will assign the last row value (somthing like loop).

|||

The simple & faster version of query ...

Select Top 1 @.Id =Id, @.Name=Name From student

Where JoinDt>'1/1/2006' Order By JoinDt Desc

Select @.Id ,@.Name

|||

If you are using @.ID and @.Name for display the out put then TSQL Look like,

Select Top 1 Id, Name From student

Where JoinDt>'1/1/2006' Order By JoinDt Desc

Regards

manoj

|||Thank U It worked!!!

Retrieving Data from next row

I have a little problem. An example of the data I work with is pasted below.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
The Query I'm making needs to give me the next row's ASSEMBLED as the
Previous row's LastAssembled so that I can calculate the time that was
carried out on that action. Anyone that can give me a helping hand? ^^
Thanks ^^Hi
Look at below example
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 20:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:23','20000610 20:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:25','20000610 21:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:00','20000610 21:15')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:16','20000610 21:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:25','20000610 21:35')
SELECT
DISTINCT StartTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> I have a little problem. An example of the data I work with is pasted
below.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
>
> The Query I'm making needs to give me the next row's ASSEMBLED as the
> Previous row's LastAssembled so that I can calculate the time that was
> carried out on that action. Anyone that can give me a helping hand? ^^
> Thanks ^^
>|||Thank you for the response. I think me previous example was a bit confusing.
In my previous example I had the Column LastAssembled. That was my failure
to get it to show as I want it.
This is what I have to work with.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06
Thus There's only 1 timestamp per event. But the one timestamp to the next
is the lenght of the action. Thus what I'm tyring, is to create another
tale, or just a function, wich would either place the 2nd line's Assembled
next to the 1s't lines assembled, so I can work out the difference between
them, or even directly subtract the first line from the 2nd line. End of the
day I need to caculate how much time was spent on each action, so I can just
add the totals of the actions.
Thanks again ^^
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Look at below example
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 20:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:23','20000610 20:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:25','20000610 21:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:00','20000610 21:15')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:16','20000610 21:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:25','20000610 21:35')
>
> SELECT
> DISTINCT StartTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
>
>
> "Nightshade" <Nightshad3@.telkomsa.net> wrote in message
> news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> below.
>|||Hi
Does that mean the query I posted does not work?
Please post DDL + expected result.
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18r49$1lbg$1@.newsreader02.ops.uunet.co.za...
> Thank you for the response. I think me previous example was a bit
confusing.
> In my previous example I had the Column LastAssembled. That was my failure
> to get it to show as I want it.
> This is what I have to work with.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06
>
> Thus There's only 1 timestamp per event. But the one timestamp to the next
> is the lenght of the action. Thus what I'm tyring, is to create another
> tale, or just a function, wich would either place the 2nd line's Assembled
> next to the 1s't lines assembled, so I can work out the difference between
> them, or even directly subtract the first line from the 2nd line. End of
the
> day I need to caculate how much time was spent on each action, so I can
just
> add the totals of the actions.
> Thanks again ^^
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
0
07:17:29
>

Retrieving Binary Image from SQL

i am trying to retrieve an image blob stored in SQL to a web matrix form (using VB.NET). I am totaly new to this and i got the code below from a site but it doesn't see to work....

<%@. Page Language="VB" %>

<%@. import Namespace="System.Data" %>

<%@. import Namespace="System.IO" %>

<%@. import Namespace="System.Drawing.Image" %>

<%@. import Namespace="System.Drawing.Bitmap" %>

<%@. import Namespace="System.Drawing.Graphics" %>

<%@. import Namespace="System.Web.UI.WebControls" %>

<%@. import Namespace="System.Data.Odbc" %>

<%@. import Namespace="System.Data.SqlClient" %>

<script runat="server">

Private Function GetImageFromDB(ByVal ImageID As Integer) As System.Drawing.Image

' Get the Connection string

Dim strConnection As String

strConnection = "server='(local)'; trusted_connection=true; database='mydatabasename'"

Dim conn As New SqlConnection(strConnection)

Dim sqlCommand As String

Dim strImage As String

Dim image As System.Drawing.Image

Try

sqlCommand = "SELECT ImageField FROM ImageTable WHERE MyImageId = " + ImageID.ToString

Dim cmd As New SqlCommand(sqlCommand)

cmd.Connection = conn

conn.Open()

Dim dr As SqlDataReader

dr = cmd.ExecuteReader()

While (dr.Read())

Dim byt As Byte()

byt = dr.Item(strImage)

Dim bmp As New System.Drawing.Bitmap(New System.IO.MemoryStream(byt))

image = bmp

End While

Catch ex As Exception

' Log Error

Response.Write(ex.Message)

Finally

If conn.State = ConnectionState.Open Then

conn.Close()

End If

End Try

Return image

End Function

Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs)

If Not IsPostBack Then

GetImageFromDB(2111)

End If

End Sub

</script>

<html>

<head>

</head>

<body>

<form enctype="multipart/form-data" runat="server">

<asp:Image id="BrandSignImg" runat="server"></asp:Image>

</form></body></html>

You can not send two different content types with a single http request. ie Html and Image data.
You will need a HttpHandler for serving images out, or you can use a special .aspx page, here is a link to get your started.
http://www.sadeveloper.net/Forums/ShowPost.aspx?PostID=52636
bill

Retrieving a piece of datalogged equipment by most recent time

The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.

String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";

It is possible that I am missing something. The SQL is very hard to read in this format.

But, I think if you just add a TOP 1 to the first select so SELECT top 1 a.Equipment

and add ORDER BY a.TIME desc at the end.

That would give you the most recently logged item meeting your criteria.

|||

wow that's sweet.

two questions.

1) I'm getting the most currently logged item now but I'm only getting one piece of equipment when I should be getting two if I just search by Category?

2) Do you have some sql that I can look at to tighten up my format. I've only be using SQL for a month or less. So this is all new and only going off what I've seen and read.

String dbsql = " SELECT TOP 1 a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" ORDER BY a.Time desc " +
"";

|||

wow that's sweet.

two questions.

1) I'm getting the most currently logged item now but I'm only getting one piece of equipment when I should be getting two if I just search by Category?

so all soldering irons, function generators etc if they are currently being tracked should be returned with their most currently know location determined by time if Category is the only control used.

2) Do you have some sql that I can look at to tighten up my format. I've only be using SQL for a month or less. So this is all new and only going off what I've seen and read.

String dbsql = " SELECT TOP 1 a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" ORDER BY a.Time desc " +
"";

|||

I'd need some more information.

So do you have multiple rows per EquipmentID in tblRdrLog and you just want the one row with the greatest time for each EquipmentID? (but the result can have 1 to many equipmentIDs)

If so, there are a couple of ways for you to do it. Can you answer one more question?

Does the time field change over the life of a row in tblRdrLog? Or is it just inserted when the row is created? I am asking because I am wondering if I can use the find the MAX(ReaderID) instead of the MAX(Time).

Ultimately it won't make a huge difference, but I want to give you the best solution for your problem.

|||

1) Ya, absolutely. There are multiple rows per EquipmentID in tblRdrLog... The search form has Category and Type controls... If they choose Electronics for a Category and don't choose the Type of equipment they are looking for should return all Electronic equipment by using only their most recently logged record.

(Datalogs items for adminstration use by just inserting to see log life of equipment or in this case for public institutional use only the most recent log is shown to display current positioning)

2) No, the Time for each row is static. Once a piece of equipment is logged a row is created and a time is inserted for all time.

|||

First, you should modify your code to use stored procedures or views and use parameterized commands. Your code currently is open to SQL injection attacks and it is very easy to break. Using a SP or view also provides some additional layer of security and it makes debugging the query easier. You can run your query in the backend and try various ways to solve the problem easily.

Lastly, for this issue it will be good if you can post some sample DDL (which shows constraints, indexes, references etc), data and expected results. It will then be easier to suggest the correct query. You have access to the data and know the expected results but it is often harder to articulate that to someone else who can't see it. So best is to post a simpler repro to get the correct solution.

|||

I think I would do something like this. (add one more join to a derived table)

SELECT a.EquipmentID " +
, f.Subcategory " +
, c.Area " +
, d.Room " +
FROM tblRdrLog a " +
JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
JOIN (SELECT EquipmentID, MAX(ReaderID) FROM tblRdrLog GROUP BY EquipmentID) as LastEntry
ON a.READERID = LastEntry.ReaderID
...

|||sorry I'll post some more stuff. as for the rest I only know half of what your talking about. I tried to use stored procedures but couldn't figure them out and I'm under a time restraint so instead of panicing I just did the only way I could. I'm not sure what a DDL is? I'll post some stuff in a second.|||

so your trying to grab the most recent entry by the log tables primary key, ReaderID, that is associated with a specific EquipmentID? How come READERID is capped after ON at the bottom any specific reason?

I didn't know you could Join to a nested select what does that do for the results produced? or basically how does that work?

|||

Works great! Thanks for the help. I change it just a bit and added a nested select. I'm sure there is a much smaller cleaner way of doing this and I'd love to see it but now I can push on and come back to this if I have time and clean it up.

Thanks again, here's the last version.

String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" JOIN (SELECT EquipmentID " +
" , MAX(LogID) AS LogID " +
" FROM tblRdrLog GROUP BY EquipmentID) AS LastEntry ON a.LogID = LastEntry.LogID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
"";

retrieve stored procedure

I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated.

CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates]
@.UserName NVARCHAR(256),
@.StartingDate datetime,
@.EndDate datetime
AS
DECLARE @.UserId AS UNIQUEIDENTIFIER
SET NOCOUNT ON

SELECT @.UserId=UserId FROM aspnet_users WHERE UserName=@.UserName

SELECT
@.UserName as UserName,
SUM (timeentryDuration) AS TotalDuration,
SUM (timeentryOvertime) AS TotalOvertimeHours
FROM
aspnet_starterkits_TimeEntry
WHERE
aspnet_starterkits_TimeEntry.TimeEntryUserId=@.UserId
AND
TimeEntryDate between @.StartingDate and @.EndDate

Hello,

Here is a working sample. Hope it can help.

<%@. Page Language="C#" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<script runat="server"
protected void Button1_Click(object sender, EventArgs e)
{

GridView1.Visible = true;

}
</script
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td>
</td>
<td> <asp:DropDownList ID="ddl1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="UserName" DataValueField="UserName">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet_staterKits_TimeTracker%>"
SelectCommand="SELECT [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</td>
<td>
</td>
</tr>
<tr>
<td>T1
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>T2
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /></td>
<td>
</td>
</tr>
</table>
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
<asp:BoundField DataField="TotalDuration" HeaderText="TotalDuration" SortExpression="TotalDuration" />
<asp:BoundField DataField="TotalOvertimeHours" HeaderText="TotalOvertimeHours" SortExpression="TotalOvertimeHours" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet_staterKits_TimeTracker %>"
SelectCommand="aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates2" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="UserName" ControlID="ddl1" />
<asp:ControlParameter Name="StartingDate" ControlID="TextBox1"/>
<asp:ControlParameter Name="EndDate" ControlID="TextBox2"/>
</SelectParameters>
</asp:SqlDataSource>

</div>
</form>
</body>
</html>

retrieve Primary key value from OLAP Cube

I want to retrieve Primary key value from my OLAP Cube. can you please tell
me how can i do this in below query?
SELECT NON EMPTY { [Measures].[Total Test Count] } ON COLUMNS,
NON EMPTY { ([Dim Station].[Station Name].[Station Name].&&
#91;1ST CHOICE
EMISSIONS & INSPECTIONS] *
[Dim Test Cycle].[Test Cycle].[Test Cycle].ALLMEMBERS *
[Dim OverallResult].[Overall Result].[Overall Result].ALLMEMBERS
) } ON ROWS
FROM [OLAP Test Cube]
Dinesh Patelthere is no primary keys in a cube.
I presume that you want to retrieve the drill through result?
The drill through command return the row from the source database which are
used to fill the targeted cell.
search on the web for samples and syntaxes.
"Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
news:1EA5DE29-39D8-4002-BD57-C19998A18832@.microsoft.com...
>I want to retrieve Primary key value from my OLAP Cube. can you please tell
> me how can i do this in below query?
> SELECT NON EMPTY { [Measures].[Total Test Count] } ON COLUMNS
,
> NON EMPTY { ([Dim Station].[Station Name].[Station Name].
&[1ST CHOICE
> EMISSIONS & INSPECTIONS] *
> [Dim Test Cycle].[Test Cycle].[Test Cycle].ALLMEMBERS *
> [Dim OverallResult].[Overall Result].[Overall Result].ALLMEMBE
RS
> ) } ON ROWS
> FROM [OLAP Test Cube]
> Dinesh Patel|||Thanks for info.
"Jeje" wrote:

> there is no primary keys in a cube.
> I presume that you want to retrieve the drill through result?
> The drill through command return the row from the source database which ar
e
> used to fill the targeted cell.
> search on the web for samples and syntaxes.
>
> "Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
> news:1EA5DE29-39D8-4002-BD57-C19998A18832@.microsoft.com...
>
>