Distinct Sum for my column

Hi,

Bonjour,

I want distinct sum for one of my column.But iam not able to do that.

I tried DISTINCTSUM function given inMSDN, but it always return ZERO.

My function call in FOOTER section is called first, before my DETAILS section function call.

please help me for this.

thanks and regards

Hemant

You need to add an expression to your detail cells in the column, that expression should call a custom (code) function that records all unique values into an array:

=Code.AddUniqueNumber(myField)

then in your total just call another function that sums the array you built:

=Code.SumUniqueNumbers()

this is roughly what your code should look like:

Code Snippet

dim myArray() as Integer

public function AddUniqueNumber(Byval newNumber as integer) as integer

AddUniqueNumber = newNumber

dim i as integer

for i = lbound(myArray) to ubound(myArray)

'if this array element equals the number then it isn't unique

if myArray(i) = newNumber then exit sub

next

'increase the size of the array

redim preserve myArray(ubound(myArray) + 1)

'add the new unique number to it

myArray(ubound(myArray)) = newNumber

end function

public function SumUniqueNumbers() as Integer

dim sum as integer

dim i as integer

for i = lbound(myArray) to ubound(myArray)

sum = sum + myArray(i)

next i

SumUniqueNumbers = sum

end function

Note that this code is purely of the top of my head, my VBA is rusty, and it is UNTESTED and will have syntax errors. But it gives you an indication of how to do it. You will also need to initialise your array, probably by passing the rownum in as a parameter as well, and if the rownum = 1 then reinitialise the array.

|||

hi,

thanks for the reply.

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

-thanks and rgeards

Hemant

|||

HemantC wrote:

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

Then you are doing something wrong.... the code concept does work, i have used it in the past.

The columns in a report are evaluated left to right, top to bottom, so if your array was zero then maybe you have one of these things wrong:

- you have not inserted a call to add a value to the array in the detail rows (or you put the call in the wrong place)

- you are making the call correctly but not adding the new value to the array like you should

- you are reinitialising the array on every call, instead of on just the first row of the table

- you are not looping through the array correctly to sum it

- there is an error in the code and you are showing a zero instead of #ERROR

What i have found helpful in the past is to write the code in the macro editor of Excel, along with a test function that calls it, then once it is performing correctly i insert the code into the report.

|||

Hi,

Thanks again.

I deleted my table and again created new one.But my footer function is called first and then my details section.

For debugging i just put a messagebox, which shows that first footer function is called.

thanks and regards

Hemant.

|||Do you mean the page footer or do you mean the subtotal on a table?

If it is the former, then try referencing the code from a hidden text box in the page body, and then refer to the hidden text box from the footer using the "reportitems" collection|||

Its in footer.

I also tried in hidden textbox.

But we cant access textbox of details section in Footer section.It gives error.

thanks

Hemant

|||

this is my code

Public orderIDs As System.Collections.Hashtable
Public total As Double

Public function CalculateSum(ByVal orderID As Object, ByVal freight As Object) As Double

If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
CalculateSum = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
CalculateSum = total
End If
End Function

Public function SumUniqueNumbers() as Integer
System.Windows.Forms.MessageBox.Show("toto")
dim sum as integer
Dim myDE As System.Collections. DictionaryEntry

For Each myDE In orderIDs

sum =sum +myDE.Value
Next myDE
SumUniqueNumbers = sum
end function

|||workaround for that error
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1903450&SiteID=17|||

hi,

i have tried this but we cannot acess the textbox present in details section in footer.

this is the error

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

hemant

Distinct Rows but All Columns

I searched but did not find the answer to my specific question...
I have a table where I need to return all columns, however, I need only
distinct rows for one of the columns. The problem is that the data
types are uniqueidentifiers.
The DISTINCT keyword works on the entire row so I cannot simply use
SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
FROM dbo.ReportingTransactions AS A
I have looked at grouping with no luck either. How can I get all
columns but distinct rows on one of the columns?
Here's my table:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
Sample Data in Table
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:30:01.123
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:42:59.200
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357
937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:14:25.857
Desired Results
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357SELECT * FROM MyTable
WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
See if that helps you.
Yosh
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Sorry, I wasn't clear. I need the column called 'SomeForeignKey' to be
distinct. Using the same basic query you suggested but with the other
column doesn't work.
SELECT * FROM MyTable WHERE SomeForeignKey in (SELECT DISTINCT
SomeForeignKey FROM MyTable)
Returns all rows...not the rows with a DISTINCT SomeForeignKey value.|||Won't this return exactly the same recordset as SELECT * FROM MYTABLE since
LASTUPDATE will *always* be in the dataset returned by (SELECT DISTINCT
LastUpdated FROM MyTable)?
"Yosh" <yoshi@.nospam.com> wrote in message
news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
> SELECT * FROM MyTable
> WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
> See if that helps you.
> Yosh
>
> <Doug@.icr-consulting.com> wrote in message
> news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>|||How would you determine which row to return? From the looks of the
desired results, what you really want is the last updated row for a
particular FK value - which is different than distinct on one column only.
-- correlated subquery
select MyPK, SomeForeignKey, LastUpdated
from mytable t1
where lastupdate = (select max(lastupdated) from mytable where
someforeignkey = t1.someforeignkey)
or
-- derived table
select t1.MyPK, t1.SomeForeignKey, t1.LastUpdated
from mytable t1
join (
select someforeignkey, max(lastUpdated) as lastupdated
from mytable
group by someforeignkey
) t2
on t1.someforeignkey = t2.someforeignkey
and t1.lastupdated = t2.lastupdated
Doug@.icr-consulting.com wrote:
> I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
>|||This comes very close:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
insert into mytable values('D301D519-BC09-411B-8F31-8EACFD2E4775',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:33:32.873')
insert into mytable values('2827DA4D-EE8F-46ED-95D2-2372F727F510',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:30:01.123')
insert into mytable values('AC1B46B6-9C85-4FD7-830D-144E573CEFF2',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:49:11.450')
insert into mytable values('E1C45075-DEEE-47CB-8E8A-CFA37EFFA377',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:47:27.967')
insert into mytable values('9EC6A9E1-BDE1-494E-9010-13D0C786557E',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:42:59.200')
insert into mytable values('D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD',
'7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C', '2005-11-11 21:38:01.543')
insert into mytable values('A46E3001-0B4C-4669-8EA6-1409CDD1FDC5',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('7AD20272-39FD-43AA-B18C-7F6D265E3962',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('CF356908-9A77-4B70-8CBD-A4221DED72FC',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:15:36.357')
insert into mytable values('937143F8-4509-400D-81D9-B19EB02F97B0',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:14:25.857')
SELECT *
FROM MYTABLE T1
WHERE LASTUPDATED = (SELECT MAX(LASTUPDATED) FROM MYTABLE T2 WHERE
T1.SOMEFOREIGNKEY = T2.SOMEFOREIGNKEY)
drop table [MyTable]
The only real problem that I see is that when there are two values with the
same SOMEFOREIGNKEY and LASTUPDATED values it still returns multiple rows.
I'd have to think about that one a bit. I think the crux of the issue here
is that there is actually nothing distinct about the record that you want to
select.
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Actually, the LastUpdated column is purely informational (as far as my
use of it). It's the SomeForeignKey column that I need to be unique.
Utlimately, I will use the SomeForeignKey column to join on another
table. Once I get the query to return the SomeForeignKey column in
distinct rows I can figure out the rest.
BTW: Thanks for you input thus far.|||Yes. You are correct.
What was I thinking.
Thanks,
Yosh
"Steve Hamilton" <shamilton@.community.nospam> wrote in message
news:OoT25Hj6FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Won't this return exactly the same recordset as SELECT * FROM MYTABLE
> since LASTUPDATE will *always* be in the dataset returned by (SELECT
> DISTINCT LastUpdated FROM MyTable)?
>
>
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
>|||If it is not the combination of SOMEFOREIGNKEY and LASTUPDATED then I am
having a hard time grasping what is distinct about the dataset that you want
returned. It sounds like what you want is one single record returned for
each distinct SomeForeignKey value in your table. The problem with that is
that multiple records exist in your table for the value and you have to in
some form or another tell sql server exactly which record to return, it is
not going to guess on your behalf It sounds like what you need to do is to
define some rule to determine which record for the particular SOMEFOREIGNKEY
value will be returned. Once you have done that crafting the query in the
syntax of what I submitted earlier should be feasible. Hope this helps.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>|||I looked at your postings and my replies and decided to try and clarify
things a bit. In your example that you originally posted you wanted the
following record in the returned result:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
In your example data the following records contain that particular
SomeForeignKey value:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:42:59.200
In this case how did you pick the particular record that you wanted to
return? Once you identify the logic to pick the specific record it should
be possible to write a query that returns the expected result. If the
particular record doesn't matter you could simply use MAX(CAST(MYKEY AS
VARCHAR(36))) to identify a single distinct record.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

DISTINCT Query

Hi,
I want a query to return the values in three columns, but I only
want distinct values in one of the three columns. Is this possible? I
want to do something like this:
SELECT A, DISTINCT(B), C
FROM TABLE Z
but SQL Server doesn't like this syntax.
Any ideas?
JDHi
No
SELECT DISTINCT A, B, C
FROM TABLE Z
How do you expect a valid set to come back with only one column being unique
and every other column an possible permutations?
You do you want your data to look like?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:fwY1f.47009$lq6.25552@.fed1read01...
> Hi,
> I want a query to return the values in three columns, but I only
> want distinct values in one of the three columns. Is this possible?
> I
> want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. The "unit of work" in a SELECT
statement is a row; if this were a file system, then the fields would
be scanned from left to right.
So, in terms of RDBMS, your question and attempted syntax make no
sense. Also TABLE is a reserved word, so code fails.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant?
CREATE TABLE Foobar
(a INTEGER NOT NULL,
b INTEGER NOT NULL UNIQUE, -- no dups allowed!
c INTEGER NOT NULL,
.);|||Joe Delphi wrote:
> Hi,
> I want a query to return the values in three columns, but I
> only want distinct values in one of the three columns. Is this
> possible? I want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
I think you will need to show us some sample data and desired results
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Distinct on only one column, return all columns

I have a table with many columns. I want to return all columns, and I want
only the first record for each distinct value in the salutation column. I
come up with the following:
select c1.* from complainer as c1
join (select distinct salutation from complainer) as c2
on c1.complainerid = c2.complainerid
The code above returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'complainerid'.
Suggestions greatly appreciated.
RandyHow do you determine "first"? I'm assuming that you are using an
IDENTITY column as your id, so try something like this:
select c1.* from complainer as c1
join (select complainerid = MIN(complainerid), salutation
from complainer GROUP BY salutation) as c2
on c1.complainerid = c2.complainerid
HTH,
Stu

DISTINCT MonthName for a lot of dates....

Hi all,
I have a table with several rows, each has a datetime field.
I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...
table structure:
PregnancyLog
LogID int
LogDateTime datetime
sample data
LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05
Expected results
MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8
Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.
Regards
Rob"Rob Meade" wrote ...

> Any help would be appreciated
I hate it when this happens...looks like I might have sussed it myself...
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
Does that look acceptable to anyone? It gives me the results I wanted but I
just wanted to make sure..
Regards
Rob|||On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

>"Rob Meade" wrote ...
>
>I hate it when this happens...looks like I might have sussed it myself...
>SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
>FROM PregnancyLog
>GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
>ORDER BY MONTH(LogDateTime)
>Does that look acceptable to anyone? It gives me the results I wanted but
I
>just wanted to make sure..
>Regards
>Rob
>
Hi Rob,
Looks good.
Here's an (untested) alternative:
SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)
Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.
But its still less code than I had - many thanks :o)
Regards
Rob|||Hi Hugo,
Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...
Ie...
MonthName MonthNumber Counter
January 1 2
February 2 6
March 3 15
Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again...nightmare..
Any help appreciated.
Regards
Rob|||On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

>Hi Hugo,
>Any ideas how I would add a "count" to the end of the result set of the
>number of log items for each month returned by the existin query...
>Ie...
>MonthName MonthNumber Counter
>January 1 2
>February 2 6
>March 3 15
>Any help would be really appreciated, I've tried adding COUNT(LogID) to my
>query, but then I get message telling me that things need adding to the
>aggregate function or the group by clause, which I did try adding again but
>then I have to lose the order by or else I get EVERY row
>again...nightmare..
>Any help appreciated.
>Regards
>Rob
>
Hi Rob,
If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
> COUNT(LogID) AS Counter
> FROM PregnancyLog
> GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
> ORDER BY MONTH(LogDateTime)
> should work. If not, you'll need to provide more information, as
> described in www.aspfaq.com/5006.
Hi Hugo,
Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY...
Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)
Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)
Regards
Rob|||On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:
(snip)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
Hi Rob,
If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->
Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast.....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

DISTINCT COUNT - unhelpful error message

Hi,
I need to return a distinct count of customers who have ordered goods.
To do this I created a measure of type DISTINCT COUNT, over the CustomerID field in the orders fact table. Being a foreign key for the customers dimension table, the customerID is integer, not-null, and therefore ideal for the purpose.
The measue is created in a new measure group OK, and the cube processes through OK. However when I come to view the data by dragging the new measure onto the columns in the VS browser I get the wonderful message:

"
The query could not be processed: o Internal error: An unexpected exception occured.

"
Doing exactly the same creation process with the ProductID field works fine, and gives the expected results. I've looked at the tables and can see nothing obviously wrong with the data. The only difference is that there are about 3,800 products, and about 1,000,000 customers. However I did the same thing on the prototypes with no problem at all. Version is SQL 2005 Enterprise Ed.
Any thoughts?
Thanks as always,

Richard

Suggest you contact Analysis Services customer support for this situation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

distinct

How do i make this return only distinct records...
SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
dbo.tbl_purchase_orders.date_expected
FROM dbo.tbl_purchase_order_lines INNER JOIN
dbo.tbl_purchase_orders ON
dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
ORDER BY dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_orders.date_expected
help appreciated!!!!
chrisSELECT distinct
ol.stock_code,
ol.qty_ordered AS stock_ordered,
l.date_expected
FROM dbo.tbl_purchase_order_lines as ol INNER JOIN dbo.tbl_purchase_orders as l
ON ol.purchase_order_id =3D l.id
WHERE (l.delivery_complete =3D 0) AND
(l.date_expected > GETDATE() - 1)
ORDER BY stock_code, date_expected
ie just add the distinct, the top 100 percent looks effectively =redundant anyway, also corelation names (table aliases) make it easier =to read and less typing
Mike John
"Chris Dangerfield" <webmaster@.planetmicro.co.uk> wrote in message =news:p1iNa.219$CO4.17@.news-binary.blueyonder.co.uk...
> How do i make this return only distinct records...
> > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
> dbo.tbl_purchase_orders.date_expected
> FROM dbo.tbl_purchase_order_lines INNER JOIN
> dbo.tbl_purchase_orders ON
> dbo.tbl_purchase_order_lines.purchase_order_id =3D =dbo.tbl_purchase_orders.id
> WHERE (dbo.tbl_purchase_orders.delivery_complete =3D 0) AND
> (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
> ORDER BY dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_orders.date_expected
> > > help appreciated!!!!
> chris
> >=20

displaying report parameter names and values in a textbox

I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
thanks a lot
Axe
From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comUse Parameters!ParamName.Label
"Axel" wrote:
> I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
> My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
> thanks a lot
> Axel
> From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

displaying one data record

Hi all, this is a very basic question of diplaying a data.
on my aspx page I have datasource that will return only ONE record.

<asp:SqlDataSource ID="sdsCategoryName" runat="server" ConnectionString="<%$ ConnectionStrings:KaruselaConnectionString%>"
SelectCommand="SELECT Title FROM tbh_Categories WHERE CategoryID=@.categoryID ">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="-1" Name="CategoryID" QueryStringField="id" Type="int32"/>
</SelectParameters>
</asp:SqlDataSource>

on the server side I would like to manipulate the title of the page according to the data returned from the query:

and on the behind code

if (!this.IsPostBack && !string.IsNullOrEmpty(this.Request.QueryString["ID"])) { DataView dv2 = (DataView)sdsCategoryName.Select(arg);this.Title =string.Format(this.Title, dv2.Table.Columns[0].ToString()); dv2.Dispose(); }

of course it doesn't work. my question is this. do we really have to put the query datasource on the client side?

and secondly, how can I view the recorsd I recieves from the query?

Thanks for the help.

on yours code behind file write this code>>>>

add namespace

System.Data.SqlClient;

and on load event write

SqlConnection sqlconn = new SqlConnection("yourconnectionstring");

SqlCommand sqlcomd=new SqlCommad("SELECT Title FROM tbh_Categories WHERE CategoryID=@.categoryID ",sqlconn);

sqlcomd.CommandType= CommandType.Text;

sqlcomd.Parameter.Add(new SqlParameter("@.catergoryID") ,SqlDbType.NChar ,10);

sqlcomd.Parameter["@.categoryID"].Value = Request.QueryString["ID"];

SqlDataAdapter myadp = new SqlDataAdapter(sqlcomd);

DataTable dt = new DataTable();
myadp.Fill(dt);

Label1.Text= dt.Rows[0].ItemArray.GetValue (0).ToString ();

Mark post as answer if it helped u>>>

Have a great day!

Displaying multiple datasets in one report

I have a store procs that return more that one result set i.e. multiple
selects in the store proc. The report always picks out the first result set
it finds. How can I get a report to show the other result sets that the store
proc generates? Rewriting the store procs is not an option due to time
constraints.Bad news. Currently RS only works with the first recordset. If you can't
rewrite the sp's, you might be able to create separate wrapper sp's that
return the individual recordsets.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike S" <MikeS@.discussions.microsoft.com> wrote in message
news:B3FCBC28-EE83-4FA6-AF23-6838DA338954@.microsoft.com...
>I have a store procs that return more that one result set i.e. multiple
> selects in the store proc. The report always picks out the first result
> set
> it finds. How can I get a report to show the other result sets that the
> store
> proc generates? Rewriting the store procs is not an option due to time
> constraints.

Displaying Multiple Categories (from seperate tables) to be viewed on one page

Hello.

I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.

Sincerely,

Computergirl

use UNION ALL

select * from table1

union all

select * from table2

Displaying last record in SQL database table

Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.

Basically, I have a form I developed in Visual Studio using ASP.NET VB. I submit the form and now I want to recall the last entry into the database I would have just made and display it on the following page (thank you page).
Here is the code I have:

PublicClass WebForm1

Inherits System.Web.UI.Page

ProtectedWithEvents SqlSelectCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlInsertCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlUpdateCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlDeleteCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlConnection1As System.Data.SqlClient.SqlConnection

ProtectedWithEvents SqlDataAdapter1As System.Data.SqlClient.SqlDataAdapter

ProtectedWithEvents DataSet1As System.Data.DataSet

ProtectedWithEvents DataList1As System.Web.UI.WebControls.DataList

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()

Me.SqlSelectCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlInsertCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlUpdateCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlDeleteCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlConnection1 =New System.Data.SqlClient.SqlConnection()

Me.SqlDataAdapter1 =New System.Data.SqlClient.SqlDataAdapter()

Me.DataSet1 =New System.Data.DataSet()

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" & _

" authors"

Me.SqlSelectCommand1.Connection =Me.SqlConnection1

'

'SqlInsertCommand1

'

Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, " & _

"contract) VALUES (@.au_id, @.au_lname, @.au_fname, @.phone, @.address, @.city, @.state," & _

" @.zip, @.contract); SELECT au_id, au_lname, au_fname, phone, address, city, state" & _

", zip, contract FROM authors WHERE (au_id = @.au_id)"

Me.SqlInsertCommand1.Connection =Me.SqlConnection1

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

'

'SqlUpdateCommand1

'

Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @.au_id, au_lname = @.au_lname, au_fname = @.au_fname, ph" & _

"one = @.phone, address = @.address, city = @.city, state = @.state, zip = @.zip, cont" & _

"ract = @.contract WHERE (au_id = @.Original_au_id) AND (address = @.Original_addres" & _

"s OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_au" & _

"_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Origi" & _

"nal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (phon" & _

"e = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL AND" & _

" state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS NUL" & _

"L); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract" & _

" FROM authors WHERE (au_id = @.au_id)"

Me.SqlUpdateCommand1.Connection =Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlDeleteCommand1

'

Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @.Original_au_id) AND (address = @.Original_addr" & _

"ess OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_" & _

"au_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Ori" & _

"ginal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (ph" & _

"one = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL A" & _

"ND state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS N" & _

"ULL)"

Me.SqlDeleteCommand1.Connection =Me.SqlConnection1

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=pubs;persist security info=False;user id=" & _

"sa;workstation id=NDAVENPORT2;packet size=4096"

'

'SqlDataAdapter1

'

Me.SqlDataAdapter1.DeleteCommand =Me.SqlDeleteCommand1

Me.SqlDataAdapter1.InsertCommand =Me.SqlInsertCommand1

Me.SqlDataAdapter1.SelectCommand =Me.SqlSelectCommand1

Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"),New System.Data.Common.DataColumnMapping("au_lname", "au_lname"),New System.Data.Common.DataColumnMapping("au_fname", "au_fname"),New System.Data.Common.DataColumnMapping("phone", "phone"),New System.Data.Common.DataColumnMapping("address", "address"),New System.Data.Common.DataColumnMapping("city", "city"),New System.Data.Common.DataColumnMapping("state", "state"),New System.Data.Common.DataColumnMapping("zip", "zip"),New System.Data.Common.DataColumnMapping("contract", "contract")})})

Me.SqlDataAdapter1.UpdateCommand =Me.SqlUpdateCommand1

'

'DataSet1

'

Me.DataSet1.DataSetName = "NewDataSet"

Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()

EndSub

PrivateSub Page_Init(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Init

'CODEGEN: This method call is required by the Web Form Designer

'Do not modify it using the code editor.

InitializeComponent()

EndSub

#EndRegion

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

SqlDataAdapter1.Fill(DataSet1)

DataList1.DataSource = DataSet1

DataList1.DataBind()

EndSub

EndClass

Any corrections, thoughts, comments, ideas, or criticism is welcome. Thanks.

Try sorting it usingdesc andtop 1 inselect statement.|||

Simple...great! Thanks for redirection.

Found that I could not order descending o_id field in my query. The syntax was fine, but it would not return the rows in descending order. I simply changed it wihin SQL Server itself (design) and returned top 1 and it's very nice now. Thanks!

Display stored procedure results in a view

If a stored procedure returns a table, is it possible to return that as a
view. Something like this (although I know that this SQL doesn't work)...
CREATE VIEW vw_Equipment
AS
EXEC proc_Equipment_List
Thanks,
CraigYou could use OPENROWSET or OPENQUERY, like:
CREATE VIEW vw_Equipment
AS
SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
I wouldn't use this for production code, though, I find it to be a bit of a
hack.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig|||Hi Craig,
You just need to remember to set the dataaccess bit or the server to on
using the sp_serveroption. By default the server instance has data access
set off
Greg O
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OgZC9EbmFHA.3816@.tk2msftngp13.phx.gbl...
> You could use OPENROWSET or OPENQUERY, like:
>
> CREATE VIEW vw_Equipment
> AS
> SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
> I wouldn't use this for production code, though, I find it to be a bit of
> a hack.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
>|||Here is the example i just posted in another thread.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig

Display return value from sqldatasource in textbox

Hi,

I wonder is there anyway I can put the returned value from SELECT statement from sqldatasource into a textbox.

I have a simple select statement in a sqldatasource:

SELECT X, Y, Z FROM tablexyz

So, I want the value from X, Y, Z to be displayed in 3 different textboxes. How do I do it? I am able to do it using datareader from code-behind, but I just want to know how to make use of the sqldatasource to make the whole things work.

Thanks

You could use either a gridview or a detailveiw to do that, and strip out all of the headers and footers.

I would have thought it would be easier to just use the datareader and some text boxes, I guess it depends on what you are trying to achieve.

HTH

Display or export empty report

The report is running well. However, nothing will be displayed if there is no
data return from database. I would like to display an emty report (the report
frame) when there is no data. Could someone help?
Thanks,Why would you like to display an empty report when there is no data ?
Are you running it from some application, if so just display a message
that there are no records found for the criteria entered.
Billy wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Some scheduled reports (subscription) runnning every day and sending to
customers by emai in EXCEL format. When there is no qualified data, customers
do not want to see an empty file. Instead, they want to see an empty report
(display the report frame)
thanks,
"Sara" wrote:
> Why would you like to display an empty report when there is no data ?
> Are you running it from some application, if so just display a message
> that there are no records found for the criteria entered.
> Billy wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,
>|||If you have a table, list or subreport, you can set the the NoRows property ="No data found".
"Billy" wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Thanks William. Even the report frame is not displayed by setting this
property, the email attachement is not an empty file now.
"William" wrote:
> If you have a table, list or subreport, you can set the the NoRows property => "No data found".
> "Billy" wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,