PAD and Left justify in T SQL

Hi,
Is it possible to PAD and justify output from a select statement? I have to
build an input record from two fields in a table that I am concatenating
together. The result is then going to be passed as a parameter value to a
dll. The dll is looking for input of the form
Field Name position Length
record type 1 1
ClaimID 2 17
some of the ClaimID's are less than 17 characters in length
typical output (an underscore here denotes a space)
Record # Data
1 C_______1234567890
so I need to pad the '1234567890' value with 7 spaces to the left.
At the moment I am doing this after the SELECT from SQL Server but I
wondered if I could do this in the actual SELECT itself?
Any help appreciatedYes, but it is also possible to do this in the DLL or elsewhere in the
presentation area.
(As a demonstration):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1234567890), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1565), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(12345678901234567), 17)
(In your query):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(ClaimID), 17)
So, as long as claimID is never more than 17 characters, this will work. If
you exceed 17, you will trim off the leading character(s).
A
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Joe,
Is this what you're asking for:?
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '1234567890'
SELECT REPLICATE(' ',7) + @.VAR AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
DECLARE @.VAR CHAR(50)
SET @.VAR = '1234567890'
SELECT RTRIM(REPLICATE(' ',7) + @.VAR) AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||This should get you going. Add 17 spaces to right of the ClaimID, and then k
eep the 17 right.most
characters:
SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
FROM
(
SELECT 'C' as c1, '1234567890' AS c2
) AS tbl
I prefer doing this stuff in the client app, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have t
o
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Thank you for the replies , its working well, without help from you guys I
think I would be out of a job.
"Tibor Karaszi" wrote:

> This should get you going. Add 17 spaces to right of the ClaimID, and then
keep the 17 right.most
> characters:
> SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
> FROM
> (
> SELECT 'C' as c1, '1234567890' AS c2
> ) AS tbl
> I prefer doing this stuff in the client app, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
>|||> Thank you for the replies , its working well, without help from you guys I
> think I would be out of a job.
I've often wondered if there is an easy way to divert portions of salaries
of the people we help, then I wouldn't need a job. :-)|||If you're anything like me you couldn't stop this even if you wanted to or
was rich enough to. We'd get bored ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> I've often wondered if there is an easy way to divert portions of salaries
> of the people we help, then I wouldn't need a job. :-)
>|||rest assured my salary sucks - perphaps the newsgroup could introduce a
voluntary donation to a charity of choice for really good advice.
"Jerry Spivey" wrote:

> If you're anything like me you couldn't stop this even if you wanted to or
> was rich enough to. We'd get bored ;-)
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
>
>sql

Overriding any mdx statement using product line 'A' to also include product line 'B'

Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'? I started to look at the Scope keyword, but I'm at a loss for understanding it just yet.

The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Q:Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'?

A:

You can put set in Scope:

Scope ({[Product].[Product Line].[Accessory], [Product].[Product Line].[Components]});
This = 100;
END SCOPE;

Q: The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Mosha fixed me here. I thought it is hard to do, but he showed in the following post.

Vidas Matelis

|||

Well, you can always do the following:

Accessory = Accessory + Components;

But this will give you double counting of Components at the All level. To make this apply after 1/9/2007 - you can do:

SCOPE [1/9/2007] : NULL;

Accessory = Accessory + Components;

END SCOPE;

|||

Using the unique name for all of those items and then adding the scope script works, thanks.

Is there anyway to tackle the 'all' value being incorrect because of the duplication?
I tried to add another scope, but it didn't help - probably because all is always the aggregate of its children:

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

One way to deal with 'All' would be to do

FREEZE [All Products];

Before writing assignments on levels below All in Products dimension. This, of course, assumes that there are no other changes which may affect All further in the script.

|||Sweet, thanks!|||Searching through the forums, I have found that I have a use for this as well. The only exception is I need the calculation to work for certain calculated members and not all measures and calculated members. I tried adding another SCOPE statement with my specific measures, but it didn't work as I expected.
Here's an example of the code I have that works for all measures. It would be appreciated if I could get this updated to work for 3 specific measures. thanks

FREEZE [All Products];

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

FREEZE [All Products];

SCOPE ({Measure1, Measure2, Measure3}, [1/9/2007] : NULL);

Accessory = Accessory + Components;

END SCOPE

|||Works perfect. I realized I was trying to use calculated members instead of the measures in which they were derived.

Thanks for the help.