Inserting dummy lines and padding

[Astra]

Hi All

Can you please help me with a few queries on adding a header line and
padding rows out.

I apologise profusely for not providing the DDL for this, but I don't have
it. All I have is stored procedure that I'm trying to edit to make the
front-end app display the right data.

The relevant part of the stored procedure that I'm working on is as follow:

Declare StockHelpCursor Scroll Cursor For
Select s.StockID,
ISNULL(sd.Short Description, s.StockID) +
space(30-len(ISNULL(sd.S hortDescription ,
s.StockID))) +
pl.name +
space(10-len(str(pl.name ,10,3))) +
sp.currencyid + str(sp.sellingp rice,10,3) +
space(10-len(str(sp.sell ingprice,10,3)) ) +
str(sq.quantity instock)
From Stock s, StockDescriptio ns sd, StockQuantities sq,
StockPrices sp, PriceLevels pl
Where (s.StockID Like @theID) And
(ISNULL(sd.Shor tDescription, sd.StockID) Like @theName) And
(s.StockID=sd.S tockID) And
(s.StockID=sq.S tockID) And
(s.StockID=sp.S tockID) And
(sp.PriceLevelI D=pl.PriceLevel ID) And
(sd.LanguageID= @theLanguageID) And
(sp.CurrencyID= @theCurrencyID)
Order By s.StockID
Open StockHelpCursor

PLEASE NOTE: this query works fine apart from the following problems:

1) Creating a header line - I need to insert a header line to this cursor
for the field headers, as the app is stripping off this header. I was
thinking of creating a var, sticking it in-between the Declare and the
Select part and inserting these field headers, but I don't know how to do
this. Any ideas?

2) Padding the results - As you can see from the script, I have tried to pad
out the above fields as the app's output window is basically a textbox, but
they just don't line up.

NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3
decimal places.

Could you please give me some pointers on how I can pad these out correctly.

Thanks

Robbie

[jim_geissman@countrywide.com]

Re: Inserting dummy lines and padding

This seems to work for the header, assuming you can figure out which is
the first line (minimum StockID) --

CREATE TABLE #Test(N int IDENTITY, String varchar(30))
INSERT #Test SELECT 'Line one'
INSERT #Test SELECT 'Line two'

DECLARE C CURSOR FOR SELECT
Head=CASE WHEN N=1
THEN 'Header'+CHAR(1 0)+String
ELSE String END FROM #Test
DECLARE @String varchar(30)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @String
IF @@FETCH_STATUS <> 0 BREAK
PRINT @String
END
CLOSE C
DEALLOCATE C

[Thomas R. Hummel]

Re: Inserting dummy lines and padding

To add a header row (if you mean what I think you mean) you can just do
a SELECT... UNION with your variable. Give it a StockID that will
appear before all of your other StockIDs. For example:

SELECT 0, 'Short Description Name...'
UNION
SELECT s.StockID, ISNULL(sd.Short Description, s.StockID) +
.... <rest of your query here>

I'm not sure why this is in a cursor or what you do outside of the
cursor, so I don't know if this will work for you in this case or not.

As far as lining up the output, does the text box used a fixed-width
font? Otherwise, padding with spaces will not help you. You might be
able to do something with tabs, but that might get somewhat
complicated.

-Tom.

[Astra]

Re: Inserting dummy lines and padding

Hi Thomas

I think your header thing might just work. Thanks for this

As for the text field, the reason I'm concat-ing the fields is because the
text field currently expects 2 fields and these appear to line up fine. I
just thought by concating the 2nd field with the params and then padding
these out to make nice columns would do it, but my columns go all over the
place.

Am I doing the padding incorrectly?

Rgds Rob


"Thomas R. Hummel" <tom_hummel@hot mail.com> wrote in message
news:1133546402 .708566.238850@ f14g2000cwb.goo glegroups.com.. .
To add a header row (if you mean what I think you mean) you can just do
a SELECT... UNION with your variable. Give it a StockID that will
appear before all of your other StockIDs. For example:

SELECT 0, 'Short Description Name...'
UNION
SELECT s.StockID, ISNULL(sd.Short Description, s.StockID) +
.... <rest of your query here>

I'm not sure why this is in a cursor or what you do outside of the
cursor, so I don't know if this will work for you in this case or not.

As far as lining up the output, does the text box used a fixed-width
font? Otherwise, padding with spaces will not help you. You might be
able to do something with tabs, but that might get somewhat
complicated.

-Tom.

[David Portas]

Re: Inserting dummy lines and padding

Astra wrote:[color=blue]
> Hi All
>
> Can you please help me with a few queries on adding a header line and
> padding rows out.
>
> I apologise profusely for not providing the DDL for this, but I don't have
> it. All I have is stored procedure that I'm trying to edit to make the
> front-end app display the right data.
>
> The relevant part of the stored procedure that I'm working on is as follow:
>
> Declare StockHelpCursor Scroll Cursor For
> Select s.StockID,
> ISNULL(sd.Short Description, s.StockID) +
> space(30-len(ISNULL(sd.S hortDescription ,
> s.StockID))) +
> pl.name +
> space(10-len(str(pl.name ,10,3))) +
> sp.currencyid + str(sp.sellingp rice,10,3) +
> space(10-len(str(sp.sell ingprice,10,3)) ) +
> str(sq.quantity instock)
> From Stock s, StockDescriptio ns sd, StockQuantities sq,
> StockPrices sp, PriceLevels pl
> Where (s.StockID Like @theID) And
> (ISNULL(sd.Shor tDescription, sd.StockID) Like @theName) And
> (s.StockID=sd.S tockID) And
> (s.StockID=sq.S tockID) And
> (s.StockID=sp.S tockID) And
> (sp.PriceLevelI D=pl.PriceLevel ID) And
> (sd.LanguageID= @theLanguageID) And
> (sp.CurrencyID= @theCurrencyID)
> Order By s.StockID
> Open StockHelpCursor
>
> PLEASE NOTE: this query works fine apart from the following problems:
>
> 1) Creating a header line - I need to insert a header line to this cursor
> for the field headers, as the app is stripping off this header. I was
> thinking of creating a var, sticking it in-between the Declare and the
> Select part and inserting these field headers, but I don't know how to do
> this. Any ideas?
>
> 2) Padding the results - As you can see from the script, I have tried to pad
> out the above fields as the app's output window is basically a textbox, but
> they just don't line up.
>
> NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3
> decimal places.
>
> Could you please give me some pointers on how I can pad these out correctly.
>
> Thanks
>
> Robbie[/color]

Please do not multi-post! I replied in
microsoft.publi c.sqlserver.pro gramming

--
David Portas
SQL Server MVP
--

[Thomas R. Hummel]

Re: Inserting dummy lines and padding

Astra wrote:[color=blue]
> As for the text field, the reason I'm concat-ing the fields is because the
> text field currently expects 2 fields and these appear to line up fine. I
> just thought by concating the 2nd field with the params and then padding
> these out to make nice columns would do it, but my columns go all over the
> place.
>
> Am I doing the padding incorrectly?[/color]

It looks like the padding is correct (although if your column is 30
characters, you might want to pad it out to 31 or 32 so that there is
space when you have a value that fills the full column width.

As I said though, the problem might be in how the front end is
displaying it. Some fonts are fixed-width while others are not. What
this means is that in a fixed-width font the letter "M" is just as wide
as the letter "I" once you take into account space around the letter.
If the font is not fixed-width then a letter "I" may use up 10 pixels,
while an "M" might use up 18 (I have no idea if these numbers are even
in the ballpark, but hopefully you get the idea). As a result, with a
fixed-width font if you have 30 characters on one line and 30 on the
next they will take up the same width no matter what they are. This is
not true if they are not fixed-width.

To see this effect, open up Notepad and type in:

This line is 35 characters long
This one is also 35 characters long

Highlight the two lines and change between Arial and Courier fonts
(under the Format menu). Courier is a fixed-width font and both lines
will be just as long. Arial is not, so the lengths of the lines will
change.

HTH,
-Tom.

[--CELKO--]

Re: Inserting dummy lines and padding

The most basic principle of a tiered architecture is that display is
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.

You might also ask why Stock , StockDescriptio ns, and StockQuantities
are all split into their own tables when they are clear attributes of a
stock item. This design flaw is called atrribute splitting for obvious
reasons.

[Tony Rogerson]

Re: Inserting dummy lines and padding

> The most basic principle of a tiered architecture is that display is[color=blue]
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.[/color]

Wrong! Formatting (display) is done where it is most efficient and scalable
to do it. We aren't using mainframes anymore, it matters how much data is
passed between the server and client/middle tier.

The most basic programming principle is that you look at your architecture
and design for what you have and not implement definitive statements likes
yours willy nilly.

Consider - paging, pivoting etc.... is it really efficient to pass back a
million rows to the client just to get the second page of 50 rows? Nope, it
isn't - but thats what your statement proposes.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@eart hlink.net> wrote in message
news:1133616462 .488819.190070@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> The most basic principle of a tiered architecture is that display is
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
>
> You might also ask why Stock , StockDescriptio ns, and StockQuantities
> are all split into their own tables when they are clear attributes of a
> stock item. This design flaw is called atrribute splitting for obvious
> reasons.
>[/color]