Dispaly mask and select N-M records

Two questions:
1. With SQL, how can I setup display mask for dollar amount :
eg. 100000 display as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang1.
cast(100000 as money)

2.
select top 51 foo
from (
select top 100 foo
from yourtable
order by foo desc
) as derivedtable
order by foo

rudy
http://r937.com/|||Formatting of results is best handled by the user interface (in my humble opinion).

Rudy's method works for a specific case, but the TOP command (very unfortunately) does not accept variables as parameters. You will need to cast your query as a dynamic SQL statement to use it for the general N of M case.

A lot of people come across this issue when trying to produce results that can be "paged" through, such as in a web page. If this is the case for you, then instead of trying to find records N of M, have your interface submit the largest sort-value of the previous recordset as a parameter, and then your procedure can return the first 50 (hard-coded) records that are greater than that value.

blindman|||For question one,

I try the cast(100000 as money), result as:

100000.0000 , any way to output as: $100,000 ?

Thanks for advise.

Guyang|||SELECT '$' + CONVERT (varchar(10), 100000, 1)

If you don't want the decimal values:
SELECT '$' + LEFT(CONVERT(varchar(10), 100000, 1), len(CONVERT (varchar(10), 100000, 1) - 3))

...but I'll say again that it is better to let the interface handle this. I'm thinking that you are coming from the Access world, but Access is a database and an interface wrapped together. SQL Server is purely a database (but much more powerful than MS Access). You should you other tools to communicate with the database and format the output, such as VB applications, MS Access Data Projects, or (yech) Crystal Reports.

blindmansql

Dislpay Report Parameters selectively?

I was wondering if there is a way I can selective display report parameters?
For example, I have one parameters that lets the user select a report type.
If the user selects 'Daily' I want to display the report parameter that
allows the user select a date. On the other hand if the user selects
'Monthly' I want to display a drop down list that has all months preloaded.
I do not want the dropdown list that contains months to show up if the user
wants to run a 'Daily' report.
Any help would be much appreciated!
TIA
VinayPresuming that you are also able to display a drop-down list of available
dates if the user selects 'Daily' then the way I would do this is to have a
dataset using a stored procedure which takes the Daily/Monthly parameter and
generates the drop-down list accordingly.
HTH,
magendo_man
"Vinay" wrote:
> I was wondering if there is a way I can selective display report parameters?
> For example, I have one parameters that lets the user select a report type.
> If the user selects 'Daily' I want to display the report parameter that
> allows the user select a date. On the other hand if the user selects
> 'Monthly' I want to display a drop down list that has all months preloaded.
> I do not want the dropdown list that contains months to show up if the user
> wants to run a 'Daily' report.
> Any help would be much appreciated!
> TIA
> Vinay
>
>

Disk Error

I am getting this Error when i am doing a SELECT INTO Operation in a lot of
my databases on SQL SServer 2000
Pls help
Server: Msg 823, Level 24, State 2, Line 1
I/O error 1450(Insufficient system resources exist to complete the requested
service.) detected during read at offset 0x0000067c780000 in file
'K:\DCCTDG01\DCCTDG01_data2.ndf'.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
See if these are related:
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CB67EA77-EE46-4872-A933-3D2874FD2CAF@.microsoft.com...
> I am getting this Error when i am doing a SELECT INTO Operation in a lot
of
> my databases on SQL SServer 2000
> Pls help
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error 1450(Insufficient system resources exist to complete the
requested
> service.) detected during read at offset 0x0000067c780000 in file
> 'K:\DCCTDG01\DCCTDG01_data2.ndf'.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
>

Disk Error

I am getting this Error when i am doing a SELECT INTO Operation in a lot of
my databases on SQL SServer 2000
Pls help
Server: Msg 823, Level 24, State 2, Line 1
I/O error 1450(Insufficient system resources exist to complete the requested
service.) detected during read at offset 0x0000067c780000 in file
'K:\DCCTDG01\DCCTDG01_data2.ndf'.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection BrokenSee if these are related:
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CB67EA77-EE46-4872-A933-3D2874FD2CAF@.microsoft.com...
> I am getting this Error when i am doing a SELECT INTO Operation in a lot
of
> my databases on SQL SServer 2000
> Pls help
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error 1450(Insufficient system resources exist to complete the
requested
> service.) detected during read at offset 0x0000067c780000 in file
> 'K:\DCCTDG01\DCCTDG01_data2.ndf'.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
>

Disctinct selections

Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column

for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

AlanHi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

Alan

is there a way to tell which one of the records from thr above example should be displayed ... or should it be a random one.|||It really doesn't matter which one of the records gets selected. I just need to end up with one.

Is there a nth funtion in SQL perhaps?

Alan|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname

Ok, this could work but will it return my actual record. For my example I've excluded other fields that I need to select from my table such as Individual number, family number, gender and much more.

I think my original example was not the best

Your query if used with the MIN aggregated function won't I end up with the lowest value first name, lowest value gender etc..

Won't I ended up with the lowest value first name, lowest value gender, and lowest value induvidual number. etc?

So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?|||So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?|||that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?

I have a Unique Site number that is unique to each family. I will probably be doing the group by this number instead of the last name.

How about the 1st record in a group?

I tried using FIRST, but it doesn't appear to be an aggregated function in SQL server 2005.|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?

I currently don't have a primary key, but I can add one. Once I create this ID field, how would I designate it on my query?

Thanks for all the help!|||I know cursors are forbidden or banished to the WASTELAND:shocked: , and am try to find a way to do this without cursor, sure its possible!

:cool: Till than here is the cursor solution:
(tweeked the employee table a bit)

1> run next 11 line as a batch
declare @.tempname varchar(20)
declare rpt cursor for select distinct lastname from employees
open rpt
fetch next from rpt into @.tempname
while @.@.fetch_status=0
begin
select top 1 * from employees where lastname = @.tempname
fetch next from rpt into @.tempname
end
close rpt
deallocate rpt

Take r937 suggestion, using primary key is better any day, besides two people with same surname can reside a two totally different locations.

:angel: Hope it works, have fun|||Once I create this ID field, how would I designate it on my query?with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?

Bret, I am trying to net my results to one per site number. unsing a select query.

Thanks|||r937 you just gave me an idea:beer: .
This is really weird, the following sql command works:

select * from employees as A
where firstname=(select min(firstname)
from employees as B
where A.lastname = B.lastname)

Even though i have three persons with the same firstname and it returns unique records. does it work for anybody else!!! (i tweaked the employee table)|||with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname

This worked great. Thanks for your help!|||Not bad eh, for a guy who isn't a dba|||sql skills are not restricted to DBAs, man

that's like saying "wow, you can speak english -- not bad for a guy who isn't a DBA"

being a DBA means you do stuff like replication, installation, permissions, tuning, administration, etc.

you don't have to know any of that cr@.p to be really good at sql :)|||all the stuff I hate to do...especially on DB2 OS/390

disable implicit casting

Is there a way do disable/disallow implicit casting in Sql Server 2000?

Say for example:

if ' ' = 0 select getdate()

This wil print out the current date and time.

But ' ' (a string, varchar, whatever) is not the same type as 0 (say, int). Implicit casting is nice, but is there also an off switch?

I've looked at the book Itzik Ben-Gan (T-SQL Programming). On the paragraph about implicit conversion he doesn't cite any way to switch it off. He also suggests that in some case may be useful to explicit cast data to sql_variant and reports the following example, that maybe can suggest a workaround:

select
case
when 1>1 then 10
when 1=1 then 'abc'

when 1<1 then 10
end;

select

case

when 1>1 then cast(10 as sql_variant)

when 1=1 then cast('abc' as sql_variant)

when 1<1 then cast(10 as sql_variant)

end;

MS help says also that "Implicit conversions are not visible to the user", so I guess there's no way to disable it.

Take a look also at the following docs:
http://msdn2.microsoft.com/en-us/library/ms191530.aspx
http://msdn2.microsoft.com/en-us/library/ms190309.aspx|||

Thanks.

It's really weird. I would have suspected an feature like 'option strict' like there is in VB (and for a good reason). It's a shame it's not there in T-SQL.

disable filter

i am having a query in front end like this..

func()

select * from table where cond1 and cond2 and col.port in <string> ... goes

end

i passing this string from other function, i may be not be needing this filter(underlined) for some functionality, but i will be using the same function for both the functionalities

i need a string that should tell the sql server to allow all the possible values for that column, so by disabling the filter which i am using...

pls tell me URGENT
Thanks in advance

In your function check if string is empty or not and call a different SQL. ie:

if < string is empty >
select * from myTable where cond1 and cond2
else
select * from myTable where cond1 and cond2 and col.port in < string > ....
endif

|||

I agree with the other answer, but it seems too easy.. Is this a user defined function in T-SQL? Or dynamically built using your .NET code? If dynamic then it is essential that you leave off the (col.port in <string> ... ) part. You will force the thing to go through a table scan otherwise.

If it is in a user defined function, they you might be able to use a value of the string, like NULL and add
and (col.port in <string> or <string> is null)