Formula Error - Cannot find dimension member

When I run a MDX query for a specific date, where I donot have any data in the fact table, I get the error - "Formula Error - Cannot find dimension member "--" - in a name binding function.
Query 1:
select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[30]
This works fine, but if I run the following query, I get the above error message, because there is no data for July 1st, in the fact table.

select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[1]

Can some one help me?
Thanks,
Sophia.We have two tables Loans (Parent) & LoanIndicators (Child).

LoanIndicators stores the Loan status (ex Outstanding Amount) date wise. This table is updated with a new record only when the Outstanding Amount Changes , until that time the last record holds the status.

Ex :
Loan Table
Id LoanId LoanAmount
1 L1 1000
2 L2 2000

LoanIndicator Table

Id LoanId IndicatorDate Outstanding Amount
1 L1 1/1/2005 100
2 L1 1/13/2005 90

So in the above case if i want to know the Outstanding amount on 1/10/2005 of L1 i.e the record dated 1/1/2005 gives me the outstanding amount of 100.

To Create a Cube on this I create a View linking Loan & LoanIndicators i.e the view holds all the Parent + Child Records of each of the Loans.
Also the cube has a dimension on IndicatorDate.

Now in the Cube the Total AMount of Loan disbursed is 4000 but it shouold be 3000.
It is 4000 because it is summing L1 twice as the view has two records for L1.
1) How do we prevent this summing of L1 twice ?

2) If I want to know the Outstanding Amount on 1/10/2005 for L1 , how do i get it (It should be 100 as the position only changes on 1/13/2005).

TIA

Shuchi Agarwalsql

Formatting syntax

Hi,

I am trying to convert an MS Access query to SQL script.The query builds a 16 digit field by combining 3 different columns.

access query

Select

"SP"& Format([CusNum],"000")& " "& Format$([InVoiceNum],"00000000") & "-" & Format$(,"00") AS RefNo,

From PurchaseOrder

RefNo

SP053 00000001-00

SP053 00000010-00

SP05314556895-00

SQL query to replace access query

Select

'SP'+Isnull(Cast(CusNumasvarchar(3)),'000')+''+Isnull(Cast(InVoiceNumasvarchar(8)),'00000000')+'-'+Isnull(Cast(PIasvarchar(2)),'00')

From dbo.PurchaseOrder

SP0531-0

SP05310-0

SP05314556895-00

I need some help in putting together a query that will give me the same results as produced by the access query.If InvoiceNum is less than 8 digits long I don’t know how to pad the field with 0000 to get it to be desired length.

Thanks for your help.

Nats

Nats:

Maybe something like this:

declare @.aNumber integer set @.aNumber = 715

select @.aNumber as [Input Number],
right ('0000000' + convert(varchar(8), @.aNumber), 8) as [formatted Number]

Input Number formatted Number
-
715 00000715

( Bemoaning my rustiness at Access; I wrote part of Access 97 Unleashed 2nd Ed, but I haven't used Access really since the previous millenium -- SHEESH! )

|||

Try the example below.

Chris

SELECT 'SP'

+ RIGHT('000' + ISNULL(CAST([CusNum] AS VARCHAR(3)), ''), 3)

+ ' '

+ RIGHT('00000000' + ISNULL(CAST([InVoiceNum] AS VARCHAR(8)), ''), 8)

+ '-'

+ RIGHT('00' + ISNULL(CAST(PI AS VARCHAR(2)), ''), 2)

FROM dbo.PurchaseOrder

--Note that PI should be in square brackets, however for some reason when I do this in the forum I end up with a piece of pizza, like so:

|||

Awesome works great...

Thanks a bunch!

formatting question with osql batch file

Hi all,

This is my first batch file and I want to query a database and output
it in a textfile. Up to now that works, the only problem is the
formatting in the text file. It's all screewed up...lines aren't lined
up and columns aren't right...how do you format the result from a
query to a text file. Here's my code:

osql.exe -S MYMACHINE -w 30 -E -d Demo2 -Q "select * from Title where
frequency = 'monthly'" -o "C:\output.txt"

Thanks...
JMTFigured it out guys...thanks...
It was because by default it only writes 80 characters per line if you
don'T specify how wide your columns should be. I had columns about 50
characters wide in my table...

Later,
JMT

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?
Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

Formatting number in sql

How do you convert the following Access query to SQL Server ?
Format([tblA].[PRICE],"0.0000") AS Price
Thanks.SELECT Price = CONVERT(DECIMAL(10,4), tblA.Price)
..
Or, you could let the client application format for you.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23hU7bAp%23FHA.2420@.TK2MSFTNGP12.phx.gbl...
> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
>|||fniles wrote:

> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
Access is an application development environment as well as a database.
SQL Server isn't. The client application is what controls how your
numeric values are formatted, not SQL Server. You need to consult the
documentation for whatever client environment you are running.
David Portas
SQL Server MVP
--

formatting in sql server

hi

below is my query

select * from guest.tbl1

i m getting result like this

id score

1 50
2 100

3 150

4 200

but i want this output like this

1 50 2 100

3 150 4 200

how to do this?

thanx

Where you want to display this record?In a web page or in the SQL server o/p?|||in sql server o/p|||

Code Snippet

CREATE TABLE #TMP1(ID INT IDENTITY (1,1),COL1 INT,COL2 INT)
CREATE TABLE #TMP2(ID INT ,COL3 INT,COL4 INT)

INSERT INTO #TMP1
(
COL1
,COL2
)
SELECT id
,val
FROM tbl1

INSERT INTO #TMP2(ID,COL3
,COL4
)
SELECT ID
,COL1
,COL2 FROM #TMP1 WHERE ID%2=1

SELECT * FROM #TMP1
SELECT * FROM #TMP2
SELECT T2.COL3
,T2.COL4
,T1.COL1
,T1.COL2 FROM #TMP1 T1 JOIN #TMP2 T2 ON T1.ID = T2.ID + 1
GO
DROP TABLE #TMP1
DROP TABLE #TMP2

|||

Afraid to believe the ID value , It may have gaps rite?

Code Snippet

Create Table #data (

[id] int ,

[score] Varchar(100)

);

Insert Into #data Values('1','50');

Insert Into #data Values('2','100');

Insert Into #data Values('5','150');

Insert Into #data Values('6','200');

Insert Into #data Values('9','200');

Insert Into #data Values('10','200');

If you use SQL Server 2005,

Code Snippet

;WITH CTE

as

(

Select

*,

Row_Number() Over(Order By ID) RID

From

#data

)

Select up.id,up.score,dwn.id,dwn.score from CTE up

left outer join CTE dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

If you use SQL Server 2000,

Code Snippet

Declare @.Table Table(

[id] int ,

[score] Varchar(100) ,

[RID] int identity(1,1)

)

Insert Into @.Table

Select Id,Score From #Data Order By 1;

Select up.id,up.score,dwn.id,dwn.score from @.Table up

left outer join @.Table dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

Formatting in sql query

Hello All,

Is it possible to format an integer to a float value with 2 decimals in an sql query.

If yes, Please help?

SELECT

CONVERT(DECIMAL(4,2),9)

You can replace number 9 above with your integer or integer column.

|||

Thanks Limno,

You are a life saver.

formatting for writing my query to an excel sheet

Hi ,

I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.

ThanksHave you tried using DTS to export your query directly into a preformatted Excel spreadsheet? While this doesn't do a 100% quality transfer, it's pretty close. Just create your Excel spreadsheet with the formatting you want, Use an export DTS wizard to walk you through putting your query as the source to be exported, then select your spreadsheet, then see if it works. You can then save it and rerun it on a schedule or manually.sql

formatting decimal places in a query in MS SQL

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.cast(cast(YourValue as decimal(10,2)) as varchar(6)) + '%'

But really, data formatting issues should be handled by the interface, not sql server. Do your formatting in Crystal, VB, ASP, Excel, or whatever else you are using to display results.|||It will do the rounding though:

select right('000000'+ltrim(str(123.87745, 6, 2)), 6) + '%'|||Probably better to use ROUND for rounding, so you know what you are trying to accomplish when you look at your code...

SELECT ROUND(127.4355,2) = 127.4400|||Makes sense.|||Originally posted by rdjabarov
Makes sense.
That works perfectly! Thanks. I've always used Access which is a breeze and I'm trying to use SQL more. I really appreciate all of y'alls help on this.

Formatting Dates as YYYY/MM/DD for a particular query

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!select convert(varchar(10), getdate(), 111)|||Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!

With the caveat that it's almost ALWAYS better to handle formatting on the client side, you might try:

Convert(varchar(10), [YourDate], 120)

Regards,

hmscott|||select convert(varchar(10), getdate(), 111)
Thanks! You've bailed me out of another toughee...

Formatting Date /Time in SQL 2000

I used to have an Access 2k query that formatted this date field:
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.

formatting columns in a view

I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.

The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.

Is this even possible? Any help would be appreciated.

You should be able to cast them in the select clause of your view (Assuming ALL of the data in those columns is numeric)

CREATE VIEW v1

AS

SELECT CAST(charcol1 AS Decimal(18,2)) AS numcol1, ...

|||Perfect. Thank you!

Formatting @query results using xp_sendmail

I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @.MessageSubject VARCHAR(50)
SELECT @.MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'me@.mine.com',
@.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@.subject = @.MessageSubject

The results in my email look like:

au_fname au_lname
------ ------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks"Terri" <Terri@.spamaway.com> wrote in message
news:c0u48d$tn2$1@.reader2.nmix.net...
> I know formatting should be handled in the client app and not in SQL
Server
> but this is the situation I must develop in so any help would be
> appreciated.
> I'm running the following in Query Analyzer
> USE Pubs
> DECLARE @.MessageSubject VARCHAR(50)
> SELECT @.MessageSubject = 'Report'
> EXEC master.dbo.xp_sendmail 'me@.mine.com',
> @.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
> @.subject = @.MessageSubject
> The results in my email look like:
> au_fname au_lname
> ------ ------------
> Abraham Bennet
> Reginald Blotchet-Halls
> Cheryl Carson
> I would like the results to be like:
> First Name: Abraham
> Last Name: Bennet
> First Name: Reginald
> Last Name: Blotchet-Halls
> First Name: Cheryl
> Last Name: Carson
> Thanks

See CHAR() in Books Online - you could try something like this:

select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.authors

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:403292f4$1_2@.news.bluewin.ch...
> See CHAR() in Books Online - you could try something like this:
> select
> 'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
> char(13) + char(13)
> from
> pubs.dbo.authors

This works, thanks.
@.query = 'select ''First Name: '' + au_fname + char(13) + ''LastName: '' +
au_lname + char(13) from pubs.dbo.authors',

Formating Sql Results

In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)|||Originally posted by Satya
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)

changing:
CONVERT(varchar(8), DATEPART(hh, Time_stamp))

to:
CONVERT(varchar(8), DATEPART(hh, Time_stamp), 100)

Doesn't change the output at all?|||Hi,

Replace
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)
With
Select substring ( Replace(convert(varchar, getdate(), 100), substring(convert(varchar, getdate(), 100), charindex(':', getdate()), 3), ''), 11, len(convert(varchar, getdate(),100)))

i think give u the desire result.

Cheers,
Gola munjal

Originally posted by Will trever
In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))|||Thanks for fine tuning...Gola|||one more solution...

select convert(varchar,getdate(),111) +
' ' +
left(convert(varchar,getdate(),108),2) +
right(convert(varchar,getdate(),100),2)

Formating results in Query Analyzer

I have a table as follows:
A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.982
19.00 002000000000000 -17.656

In the Query analyzer when I try:
SELECT * FROM TABLE1

A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.98
19.00 002000000000000 -17.66

I need the query analyzer to format them as they are in the tables.
Help please.it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||Did not work! Same results!!!

Originally posted by Karolyn
it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||are you sure that Balance is really stored as -37.982|||Yes, I am sure

Originally posted by Karolyn
are you sure that Balance is really stored as -37.982|||what is you're max precision ?

SELECT @.@.MAX_PRECISION|||38.0

Originally posted by Karolyn
what is you're max precision ?

SELECT @.@.MAX_PRECISION|||where do you see the data -37.982
?|||From the Enterprise Manager

Originally posted by Karolyn
where do you see the data -37.982
?|||SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||No error occured,
I am sure 100% that I am doing only a select *!!

Originally posted by Karolyn
SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||try contacting BlindMan or Breitt Keiser

Formating of columns in output of a SQL Statement in Query Analyzer

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdjand the difference is?|||What is the difference between two outputs?|||above the example is not appearing like I want but I want to delimite the size of a column setting the tool.|||Originally posted by joelperez
Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

left(col1,10) ?|||Originally posted by snail
left(col1,10) ?

yes left function for me!!!

Thanks!!!|||...or use CAST or CONVERT to specify the column widths of the resulting fields.|||Originally posted by blindman
...or use CAST or CONVERT to specify the column widths of the resulting fields.

Thanks for your recommendation blindman

formating in SqlServer vs Reporting Services

All else being equal, is it faster to use functions like ROUND and TRIM
in the SELECT statement of the query and let the db process it or to
set formating in the layout of the report and let Reporting Services
handle it?
In my case, all is not equal. We have the DB on a very powerful machine
that at sometimes is very overtaxed and runs smoothly at others. RS is
moving to a decently powerful server that it will have exclusively.
Thanks
Louis Ryder
SSRS Report DeveloperMy two cents. Formatting is a presentation concern. Rounding to 2 decimals
can be handled in the presentation layer (i.e., RS) using format strings.
TRIM isn't necessary unless you have some smokin' character columns...
-Tim
"Louis Ryder" <lroskind@.ryderauto.com> wrote in message
news:1152203807.627376.268390@.p79g2000cwp.googlegroups.com...
> All else being equal, is it faster to use functions like ROUND and TRIM
> in the SELECT statement of the query and let the db process it or to
> set formating in the layout of the report and let Reporting Services
> handle it?
> In my case, all is not equal. We have the DB on a very powerful machine
> that at sometimes is very overtaxed and runs smoothly at others. RS is
> moving to a decently powerful server that it will have exclusively.
> Thanks
> Louis Ryder
> SSRS Report Developer
>