DISTINCT values from a table

Hi,

I am trying to output a list of data from a table, showing only one record of each TypeID.

So, for instance, I have a simple SQL query that says:

SELECT DISTINCT AlbumTypeIDFROM AlbumORDER BY AlbumTypeIDDESC

This works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?

When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

The output is completely wrong.

Many thanks

My guess is that Description doesn't belong to AlbumTypeID, but to something like AlbumID. If you have an AblumType table with an AlbumTypeID and a Description, change you query to run against that table instead:

SELECT DISTINCT AlbumTypeID, Description FROM AlbumType ORDER BY AlbumTypeID DESC

|||

SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

Presumably you have lots of album types and lots of descriptions, so this query will only return results where the combination of the two fields is different to all the other results. For example, two AlbumTypeId of 1 records with a description of "Cars" would result in a single record.

What did you want to get back?

|||

Try

SELECT DISTINCT dbo.GetFirstAlbumDescription(AlbumTypeId), AlbumTypeId FROM Album
where
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetFirstAlbumDescription
(
@.AlbumTypeId INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @.RETURN VARCHAR(100)
SELECT @.RETURN = DESCRIPTION FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END
GO|||When I ran it I got
AlbumTypeId
-------- ----
Album 1 of 1 1
Album 1 of 2 2
Album 1 of 3 3

(3 row(s) affected) [excess apces deleted]|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Using the additional function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[GetFirstAlbumId]
(
@.AlbumTypeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.RETURN INT
SELECT @.RETURN = Id FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END

SELECT DISTINCT dbo.GetFirstAlbumId(AlbumTypeId) as a, dbo.GetFirstAlbumDescription(AlbumTypeId) as B, AlbumTypeId FROM Album

gave

a B AlbumTypeId
---- ---------- ----
1 Album 1 of 1 1
3 Album 1 of 2 2
6 Album 1 of 3 3

|||Can you provide some more sample data with same AlbumId and different AlbumTypeId's..with expected output.|||

SELECT *

FROM Albumns a

JOIN (

SELECT AlbumnTypeID,MIN(AlbumnID) AS LowestAlbumnID

FROM Albumns

GROUP BY AlbumnTypeID) t1 ON a.AlbumnID=t1.LowestAlbumnID

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

Distinct & eliminate duplicate data

Hi there.. Although i know my questions will sound quite stupid, but i've tried n tried n i can't get the desired output.. so here goes...

I'm using SQL Server 2000. I have one table call SMSReceived. in that table, there's a lot of columns. But i need to display only 2 column which is column Sender and column Message. Since this is our project, we had a lot of data with the same number in the column Sender. I want to display the data but I only want the Sender number only once. (no redundant data) So i try my query like this :

select distinct Sender from SmsReceived <-- this works since they are no redundant data

then i tried 2 columns which is :

select distinct Sender, Message from SmsReceived

then, but the Sender number still redundant.. It's like distinct is not functioning. I think it is because of the different message.

So, can anyone help me with this?.. I need to display 2 columns but only one Sender number. you can eliminate either one data as long as the Sender number didn't appear twice.

Erm... i tried group by, but an error occured like this..

Column 'Message' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sender, Message
1234, hi how are you?
1234, r u ok?
7777, my name is
7777, hello

yeah, it is something like this.. But i need to eliminate either one...

So the result will become like this..

1234, hi, how are you?
7777,hello

Is it possible?.. Please help me... :( :(select Sender, min(Message) as lowest_message
from SmsReceived
group by Sender:)|||thanks..it already works...

select dnyFrom as 'SMS Sender', min(dnyReason) as 'Reason', min(dnySMSText) as 'SMS Text', min(dnycustomerid) as 'Customer Id'
from shldnysmsreceived
where dnycustomerid='PRM' and
dnycustomerid is not null and
dnyDateReceived between '2006-12-01 00:00:00.000' and '2007-07-01 00:00:00.000'
group by all dnyFrom
order by min(dnySMSText) asc

and one more thing... for example if i had data that looks like this

1234, successful
1234, invalid message
4444, winner of prize
4444, successful
4444, invalid format
6666, hello
6666, bulk sms

i need to display only the data with 'successful'. so the output will look like this

1234, successful
4444, successful
6666, hello <-- can choose either one (hello or bulk sms)|||the solution i gave you was for two columns, one for the GROUP BY and the other for the MIN() function

you do realize that by having multiple MIN() expressions, the resulting values are not necessarily from the same row, don't you?|||No, i don't know that... why we can't have multiple expression? Can u explain by giving examples? .. I really dont understand :(|||oh, you can have multiple MIN() expressions, that's not the problem

example:

tom 12 ggg 105
tom 11 hhh 108
tom 15 xxx 104
bob 16 bbb 112
bob 13 ccc 109
bob 17 aaa 137

assume GROUP BY on the first column, with MIN on the others, the resuts are:

tom 11 ggg 104
bob 13 aaa 109

see? good results from the query, but these do not represent actual rows from the table|||owh.. so how am i going to choose 'successful' only from other data? i don't know how to use if statement..|||you would probably use a CASE expression

but i really do not understand what you are trying to do

your example data uses two columns, but your query has four|||owh.. i was just trying to make it easy for the readers to understand.. the actual data have 4 or more columns that needed to be displayed. but for the moment, i only need to know how to display 2 columns with conditions that already stated above... and i don't know how to use CASE.. :(|||if i give you the solution for only two columns, it will be of very little use to you...

... so i won't, sorry

for an example of the CASE expression, see http://www.sqlzoo.net/fun_case|||Thanks everyone..I already solve it..using case...|||hi.. i have some query that i want to solve. i had a data that looks like this. (it's actually a text messages where people sms in to win prizes) and it have to start with 'PRM'

SMSReceived <-- column name
Prm 65719471
Prm 68516237
PRM 72847410
Prm 75031193
PRM (SPACE) PINSEND TO32080
prm (space)36398226
PRM < > 22733564
Prm < 82848916 > pin
PRM <36943554>

how can i choose only the number? so the desired output will look like this..
65719471
68516237
72847410
75031193
32080
36398226
22733564
82848916
36943554

please help..|||While most SQL engines can do this kind of text manipulation, the SQL standard doesn't provide any standard way that I know to acomplish this. In other words, you can use the vendor specific extensions to get this done, but I don't know of anything in the SQL language definition that will give you a generic solution that will work on any SQL implementation.

I'd post this question in the appropriate vendor/engine specific forum to get an answer (if I didn't already know how to solve it using the engine of choice).

-PatP|||I would recommend REGEXP in MySQL but i know you're not using MySQL. I'm sure there is a MSSQL equivalent though. More importantly I think you should be stripping this information down before it goes into the database...

ever thought that someone can SQL inject from a text message?|||Isn't this exactly why your answer should be classed as incorrect if you don't follow a set pattern.
E.g. "Text PRM then a space then your answer to 55555"
If your results were always consistent it'd be easy as pie to extract just the number partsql

Displaying the output

Hi,
I want to write a query such that part of the output is displayed and the
complete output is put into a temp table.
For example i have a table customer and i want the total customer details to
be put in a temp table and the top 5 to be displayed.
select * into #temp1 from customer
select top5 * from customer
I run this two queries for my result, but is there any way to get the result
in one query.
Thanks,
MaheshMahesh wrote:
> Hi,
> I want to write a query such that part of the output is displayed
> and the complete output is put into a temp table.
> For example i have a table customer and i want the total customer
> details to be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result in one query.
> Thanks,
> Mahesh
No. You are talking about two different queries. You need to use an
ORDER BY with the TOP clause, unless you want somewhat random data
returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
My requirement is when i run a query it should go to temp table (e.g.
#temp1) and also display the output. This should happen on a single run of
query. Can you please help me in this.
Thanks,
Mahesh
"David Gugick" wrote:

> Mahesh wrote:
> No. You are talking about two different queries. You need to use an
> ORDER BY with the TOP clause, unless you want somewhat random data
> returned.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Mahesh
can you explain why you want to do that.
however it appears to be possible with functoon or procedure
--procedure
CREATE PROC DISPLAY
AS
select * into #temp1 from customer
select top5 * from customer ORDER BY CUSTOMER_ID
RETURN
GO
EXEC DISPLAY
GO
--function: you can't use temp table here but table variable
CREATE FUNCTION DISPLAYFUNCTION()
RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
AS
BEGIN
INSERT INTO @.TOPTABLE
SELECT * FROM EMPLOYEES
RETURN
END
GO
SELECT TOP 5 * FROM DISPLAYFUNCTION()
GO
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:

> Hi,
> I want to write a query such that part of the output is displayed and t
he
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the resu
lt
> in one query.
> Thanks,
> Mahesh|||space between top and 5 is missing; TOP 5
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||RD, this is requires because i will be running multiple queries to analyze
the data, while i am running i would like to see the data and then when at
the end if it is stored in a temp table then i can run a simple select on
that temp table instead of running the whole query again. can you please hel
p
me on this.
Thanks,
Mahesh
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||David already answered. One query cannot both store the results in a temp ta
ble *and* display the
results. It is one of those, but not both. How about pushing all data into t
he temp table and then
SELECT TOP 5 from there?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:E23898C7-8D15-492B-9CDE-0CFE24AC44CA@.microsoft.com...
> Hi David,
> My requirement is when i run a query it should go to temp table (e.g.
> #temp1) and also display the output. This should happen on a single run of
> query. Can you please help me in this.
> Thanks,
> Mahesh
> "David Gugick" wrote:
>|||Either you got to use sprocs or use two queries at a time with GO in betwee
n
--
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:
> RD, this is requires because i will be running multiple queries to analyze
> the data, while i am running i would like to see the data and then when at
> the end if it is stored in a temp table then i can run a simple select on
> that temp table instead of running the whole query again. can you please h
elp
> me on this.
> Thanks,
> Mahesh
> "R.D" wrote:
>|||Hi,
Try this. I did it with the northwind DB
select * into #temp1 from customers where customerid in (select top 5
customerid from customers)
Nils
"Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
> Hi,
> I want to write a query such that part of the output is displayed and
> the
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
> to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result
> in one query.
> Thanks,
> Mahesh|||nils
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
this inserts only five rows while Mahesh wants all rows to temp and five
rows to be displayed.
--
Regards
R.D
--Knowledge gets doubled when shared
"Nils Wolf" wrote:

> Hi,
> Try this. I did it with the northwind DB
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
> Nils
>
> "Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
>

Displaying SQL Statement as output on report

The requirements of Sarbanes-Oxley are getting downright outrageous.
Everytime we run a report, we need to verify that the SQL statement has not
been modified since it was last verified as accurate. To combat the constant
requests to show the SQL behind the scenes, I would like to show the SQL as
part of the report.
Is this possible and how would it be done?
I am guessing that it would require custom code that captures the SQL in a
variable and then outputs that variable into the report.Could you get away with driving each report by a seperate stored procedure
( this is what we do anyway ).
Tell them the name of the stored procedure for each report.
If they want to go the extra paranoid step :
Write a custom assembly which uses sp_helptext on the stored proc name to
get the text of the storedproc ( a line at a time )
Generate a SHA1 hash of the text ( you might want to ignore whitespace and
non alphanumeric characters )
Write to some custom reportlog file, reportname, who ran, when, on what
machine, the hash of the procedure name
( Of course you need to record somewhere the hash of the stored procedure
when its verified )
You could even throw an exception if hash doesnt match the last verified
hash.
If thats not good enough ( !! ) you could encrypt the hash with a private
key, i.e. give it a full digital signature.
Or you could just generate a hash of the entire db schema !!
> Everytime we run a report, we need to verify that the SQL statement has
> not
> been modified since it was last verified as accurate.|||I would think the best thing to do is to put everything in SP. Then you have
the date the SP was created and can compare that against when it was
verified. If you keep the date of verification in a table you can then run a
report that shows any unverified SP based on the creation date. This seems
to me to be a much better solution. Straight forward and verifiable (which
is usually what you need for Sarbanes-Oxley).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <David@.discussions.microsoft.com> wrote in message
news:4C2307F6-5002-4713-8B92-DC9A468FFEF2@.microsoft.com...
> The requirements of Sarbanes-Oxley are getting downright outrageous.
> Everytime we run a report, we need to verify that the SQL statement has
not
> been modified since it was last verified as accurate. To combat the
constant
> requests to show the SQL behind the scenes, I would like to show the SQL
as
> part of the report.
> Is this possible and how would it be done?
> I am guessing that it would require custom code that captures the SQL in a
> variable and then outputs that variable into the report.

Displaying Comments

Can anyone tell me how to display comments on the output
(In QA or DTS query output e.t.c).
Thanks.Ron wrote:
> Can anyone tell me how to display comments on the output
> (In QA or DTS query output e.t.c).
> Thanks.
Print 'This is my comment"
Make sure to use TEXT output instead of GRID.
David Gugick
Imceda Software
www.imceda.com|||Thanks..........

>--Original Message--
>Ron wrote:
>Print 'This is my comment"
>Make sure to use TEXT output instead of GRID.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

Displaying Comments

Can anyone tell me how to display comments on the output
(In QA or DTS query output e.t.c).
Thanks.
Ron wrote:
> Can anyone tell me how to display comments on the output
> (In QA or DTS query output e.t.c).
> Thanks.
Print 'This is my comment"
Make sure to use TEXT output instead of GRID.
David Gugick
Imceda Software
www.imceda.com
|||Thanks..........

>--Original Message--
>Ron wrote:
>Print 'This is my comment"
>Make sure to use TEXT output instead of GRID.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

Displaying Comments

Can anyone tell me how to display comments on the output
(In QA or DTS query output e.t.c).
Thanks.Ron wrote:
> Can anyone tell me how to display comments on the output
> (In QA or DTS query output e.t.c).
> Thanks.
Print 'This is my comment"
Make sure to use TEXT output instead of GRID.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks..........
>--Original Message--
>Ron wrote:
>> Can anyone tell me how to display comments on the output
>> (In QA or DTS query output e.t.c).
>> Thanks.
>Print 'This is my comment"
>Make sure to use TEXT output instead of GRID.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

Display space on report server

Hi,
In my report summary I need to display space. The output looks like:
Total Restaurants: 20
Total seats : 315
I'm using
="Total Restaurants: " & Sum(Fields!Restaurant.Value)
="Total seats: " & Sum(Fields!Seats.Value)
It looks fine in the preview but after I deployed it on the report
server, it doesn't look right on the server:
Total Restaurants: 20
Total seats: 315
The spaces after "Total seats" got eaten up.
I tried to use Space(5) after "Total seats" but it still doesn't work.
Is there way to do this other than spliting them into two fields?
Thanks in advance.
DanniHi,
I'm sorry but I still don't know how to use "ALT+255".
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> If it would be urget, I would have used "ALT+255" instead of SPACE
> "Danni Liao" wrote:
> > Hi,
> > In my report summary I need to display space. The output looks like:
> >
> > Total Restaurants: 20
> > Total seats : 315
> >
> > I'm using
> > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > ="Total seats: " & Sum(Fields!Seats.Value)
> >
> > It looks fine in the preview but after I deployed it on the report
> > server, it doesn't look right on the server:
> >
> > Total Restaurants: 20
> > Total seats: 315
> >
> > The spaces after "Total seats" got eaten up.
> >
> > I tried to use Space(5) after "Total seats" but it still doesn't work.
> >
> > Is there way to do this other than spliting them into two fields?
> >
> >
> > Thanks in advance.
> > Danni
> >|||Hold ALT key and press 255 on num pad and then release ALT key. Hope this
help..
"Danni Liao" wrote:
> Hi,
> I'm sorry but I still don't know how to use "ALT+255".
> "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > If it would be urget, I would have used "ALT+255" instead of SPACE
> >
> > "Danni Liao" wrote:
> >
> > > Hi,
> > > In my report summary I need to display space. The output looks like:
> > >
> > > Total Restaurants: 20
> > > Total seats : 315
> > >
> > > I'm using
> > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > ="Total seats: " & Sum(Fields!Seats.Value)
> > >
> > > It looks fine in the preview but after I deployed it on the report
> > > server, it doesn't look right on the server:
> > >
> > > Total Restaurants: 20
> > > Total seats: 315
> > >
> > > The spaces after "Total seats" got eaten up.
> > >
> > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > >
> > > Is there way to do this other than spliting them into two fields?
> > >
> > >
> > > Thanks in advance.
> > > Danni
> > >
>|||Hi Ameet:
Thank you for your reply.
I need 5 white spaces so according to your suggestion I used:
="Total seats:" & "ALT+255 4" & Sum(Fields!Seats.Value) in the
expression of that textbox, I don't see the "ALT+255" shown and the
result is:
Total seats:4201
So it still doesn't work.
I think this is a bug in the reporting services.
Danni
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<3AB33890-3B65-4C6F-9A21-622AE7E9B3C4@.microsoft.com>...
> Hold ALT key and press 255 on num pad and then release ALT key. Hope this
> help..
> "Danni Liao" wrote:
> > Hi,
> > I'm sorry but I still don't know how to use "ALT+255".
> >
> > "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > > If it would be urget, I would have used "ALT+255" instead of SPACE
> > >
> > > "Danni Liao" wrote:
> > >
> > > > Hi,
> > > > In my report summary I need to display space. The output looks like:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats : 315
> > > >
> > > > I'm using
> > > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > > ="Total seats: " & Sum(Fields!Seats.Value)
> > > >
> > > > It looks fine in the preview but after I deployed it on the report
> > > > server, it doesn't look right on the server:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats: 315
> > > >
> > > > The spaces after "Total seats" got eaten up.
> > > >
> > > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > > >
> > > > Is there way to do this other than spliting them into two fields?
> > > >
> > > >
> > > > Thanks in advance.
> > > > Danni
> > > >
> >

Display output parameters from stored procedure

Hi,

I have a report that uses a stored procedure. The stored procedure has 3 parameters 1 input and two output and also returns a resultset.

I have managed to hide the user prompt by changing the prompt for the two output parameters to an empty string.

The report runs fine and the user is not prompted for these two values, however the report does not show the values that are returned by these output parameters.

I could add extra fields to the resultset and not bother with the ouput parameters but this seems extremely inefficient as the resultset could contain several thousand rows and the fields would be the same for every single row.

Is it possible to display output paramater values?

Thanks In Advance

Chris

Chris,

have you thought of redesigning your stored procedure?

split it into two sp's, one containg the resultset, the second containg your output parameters, which should be redefined as "normal" parameters and brought back as a resultset via select @.parm1, @.parm2.

cheers,
Markus

Display leading zeros

I want my SQL 2005 output to display leading zeros up to a total of 6
characters. Foe example, the number 23 would display 000023, 2000 would
display 002000, etc. Is there a "Fill" type of command or can I do this in
a CAST function? Thanks.
DavidHi,
no there isn=B4t but you can you this snippet of mine to achieve it:
DECLARE @.SomeValue INT
SET @.SomeValue =3D 34
DECLARE @.TrailingDigits INT
SET @.TrailingDigits =3D 7
SELECT RIGHT(
REPLICATE('0',@.TrailingDigits) +
CONVERT(VARCHAR(200),@.SomeValue)
,@.TrailingDigits
)
I think you should be smart enough to put a function around it, right
:-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||David,
better to format the output in the client application. Try:
declare @.i int
set @.i = 23
select right(replicate('0', 6) + ltrim(@.i), 6)
go
AMB
"David" wrote:

> I want my SQL 2005 output to display leading zeros up to a total of 6
> characters. Foe example, the number 23 would display 000023, 2000 would
> display 002000, etc. Is there a "Fill" type of command or can I do this i
n
> a CAST function? Thanks.
> David
>
>|||Here is one option. I had to jump through a few hoops because I stored 23
in an int column....
DECLARE @.foo table (col1 int)
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(CONVERT(varchar(6),col1))) +
CONVERT(varchar(6),col1) FROM @.foo
if your data is stored as varchar you can do something like this:
DECLARE @.foo table (col1 varchar(6))
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(col1)) + col1 FROM @.foo
Keith Kratochvil
"David" <dlchase@.lifetimeinc.com> wrote in message
news:%23NOVduNXGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I want my SQL 2005 output to display leading zeros up to a total of 6
>characters. Foe example, the number 23 would display 000023, 2000 would
>display 002000, etc. Is there a "Fill" type of command or can I do this in
>a CAST function? Thanks.
> David
>|||That worked! Thanks.
David
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7D66A28-CB7F-4EC1-948D-A9F5BE29BD0D@.microsoft.com...
> David,
> better to format the output in the client application. Try:
> declare @.i int
> set @.i = 23
> select right(replicate('0', 6) + ltrim(@.i), 6)
> go
>
> AMB
> "David" wrote:
>