AttribDescription as calculated field

[Axel]

Hi

in T-SQL,

(how) is it possible to concatenate 3 (varchar) fields into one; either
in a SQL query or through a calculated field (or using a view, if
anybody can explain to me how to use views), according to the following
rules:

{
first 30 chars of Trim(AttributeV al1)

if resulting string<30 chars append
", " & first 30 chars of Trim(AttributeV al2)

if resulting string<30 chars append
", " & first 30 chars of Trim(AttributeV al3)
}
=> define as new field StockItemDescri ption

ideally I would like SQL Server to do this processing rather than
building all these answer strings on the client side.

tia
Axel

[--CELKO--]

Re: AttribDescripti on as calculated field

>> ideally I would like SQL Server to do this processing rather than building all these answer strings on the client side. <<

Only if you are a bad programmer who does not understand the basic
idea of a tiered architecture or what First Normal Form means. Display
is not done in the database, but in the front end. If the
concatenation is actually a single data elment then replace the
existing three columns with one.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

For example, if you had correct DDL, a CHECK() constraint would make
sure you never need to TRIM() in the DML. Fields are not anything
like columns and one of the many differences is that a clumn has such
constraints. If you use the right words, you will have the right
mental model for SQL programming.

Use this in your SELECT statement or VIEWs if you just want a kludge
and not to fix the problem.

SUBSTRING (attrib_1 + ', ' attrib_2 + ', ' attrib_3, 1, 30) AS
stock_item_desc rp

[Erland Sommarskog]

Re: AttribDescripti on as calculated field

Axel (realraven2000@ hotmail.com) writes:[color=blue]
> in T-SQL,
>
> (how) is it possible to concatenate 3 (varchar) fields into one; either
> in a SQL query or through a calculated field (or using a view, if
> anybody can explain to me how to use views), according to the following
> rules:
>
> {
> first 30 chars of Trim(AttributeV al1)
>
> if resulting string<30 chars append
> ", " & first 30 chars of Trim(AttributeV al2)
>
> if resulting string<30 chars append
> ", " & first 30 chars of Trim(AttributeV al3)
> }
>=> define as new field StockItemDescri ption[/color]

SELECT str30 = substring(rtrim (AttributeVal1) ), 1, 30)
CASE WHEN len(AttributeVa l1) < 30
THEN ', ' + substring(rtrim (AttributeVal2) ), 1, 30)
CASE WHEN len(AttributeVa l1) + 2
len(AttributeVa l2) < 30
THEN ', ' + substring(rtrim (AttributeVal3) ), 1, 30)
ELSE ''
END
END

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Axel]

Re: AttribDescripti on as calculated field

Erland, thanks for the suggestion.

The one I came up with was
SELECT
left( left(P.ProductN ame,30)+#
'('+left(SI.Att ributeValue1,30 )+' '
+left(SI.Attrib uteValue2,30)+' '
+left(SI.Attrib uteValue3,30),6 0)
+')' AS ItemDesc
but this is still not flexible enough, I might have to do the
processing on the client side. I want it to dynamically decide to give
more room than 30 chars to the ProductName if the space is not used up
by the concatenated Attribute Values

consider this example:
ProductName='AP EX 1/4" Hex Insert Pozidrive Screwdriver Bits'
AttributeValue1 ='3 1" '
AttributeValue2 =' '
AttributeValue3 =' '

This is returned as ItemDesc='APEX 1/4" Hex Insert Pozidrive (3 1")'
and truncates valuable information from ProductName although the
resulting string is not even near the 60 character limit.

In pseudo code my algorithm will look like:
retrieve separate ProductName, Attrib1 - 3
concatenate Attributes (each clipped to 30 chars) => Count length of
resulting AttribString
count length of ProductName
if length(ProductN ame & AttribString) > 60 then
clip(AttribStri ng) to 30
if length(ProductN ame & AttribString) > 60 then
ProductName=lef t(ProductName,6 0-len(AttribStrin g))
endif
endif
This would probably be possible with T-SQL if I could put it into a
function but I would not like to attempt it as part of a SELECT
statement. The easiest is to leave this to the client and write a
simple VBScript function. The other advantage is that since the
presentation layer (website) has control on the way this string is
displayed it might as well get control on how many chars are displayed,
so this wraps it quite nicely.

regards
Axel

[Axel]

Re: AttribDescripti on as calculated field

Many thanks Celco for your input.

In fact I will revert to do processing client side, as it is
1. a lot easier than trying to do it in T-SQL,
2. presentation layer gets more control (e.g. I can change the number
of max characters to 70 w/o touching the SQL Server)

just to wrap up the thread...

The reason for trying to do this in the Stored Procedure was my maybe
misguided longing for encapsulation, and also a desire to streamline
the information going over the internet connection.

I am now only working for 1.5 weeks on ASP (most of this was spent with
research, mainly on CSS) and have since given up the desire to look at
the code in a more object oriented fashion (which was what I tried
before ASP when working with C++, VB, Access). I am slowly getting used
to the terrible mish mash of HTML and script code that seems to
constitute ASP programming.

The code I supplied was of course only pseudo code as I did not want to
suggest a certain way of solving the problem, this is why I did not use
DDL. Currently my SP looks like this - let me mention it uses dynamic
SQL (Where string built on web page) and I am aware of the security
risks that it poses; also I already got stick for it on the NGs - the
incoming string is chopped and cleaned (quotes are doubled, commas and
semicolons are stripped) in order to avoid SQL injection. I do not
supply complete table definitions as they are not necessary for the
question.
only these:
tblProduct.Prod uctName nvarchar(250)
tblProduct.Attr ibuteValue1 nvarchar(250)
tblProduct.Attr ibuteValue2 nvarchar(250)
tblProduct.Attr ibuteValue3 nvarchar(250)
(Sizes defined by Customer, hopefully I will be able to review and
shrink them a bit - most shared hosts only allow for a db size of 100
MB)

CREATE PROCEDURE dbo.findProduct s
@mycount int output,
@whereString varchar (1000)
AS

SET NOCOUNT ON

--Set a Default value for the Wherestring which will return all records
if the Wherestring is blank
IF @whereString is Null
SELECT @whereString = 'AND tblProduct.Prod uctID is not null'

--Declare a variable to hold the concatenated SQL string
DECLARE @SQL varchar(2500)

-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCod e] +
[AttributeValue1] +
-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))
Like '%screw%'))

SELECT @SQL = 'SELECT SI.CatalogueNo, SI.TypeNo, ' +
'left( left(P.ProductN ame,30)+'' (''+ left(SI.Attribu teValue1,30) +''
''+ left(SI.Attribu teValue2,30)+'' ''+
left(SI.Attribu teValue3,30),60 )+'')'' AS ItemDesc,' +
' SI.Price,P.Prod uctName, P.ChapterCode, P.ProductGroupC ode,
DisplayOrder' +
' FROM (tblProduct as P LEFT JOIN tblStockItem as SI ON P.ProductID =
SI.ProductID) ' +
' LEFT JOIN tblSupplier ON P.SupplierCode = tblSupplier.Sup plierCode' +
' WHERE 1=1 ' + @whereString +
' GROUP BY P.ProductID, SI.CatalogueNo, DisplayOrder,
SI.AttributeVal ue1, SI.AttributeVal ue2,SI.Attribut eValue3,' +
' SI.TypeNo, P.ProductName, P.ChapterCode, P.ProductGroupC ode,SI.Price'

execute (@SQL);
GO

regards
Axel

[Erland Sommarskog]

Re: AttribDescripti on as calculated field

Axel (realraven2000@ hotmail.com) writes:[color=blue]
> This is returned as ItemDesc='APEX 1/4" Hex Insert Pozidrive (3 1")'
> and truncates valuable information from ProductName although the
> resulting string is not even near the 60 character limit.
>
> In pseudo code my algorithm will look like:
> retrieve separate ProductName, Attrib1 - 3
> concatenate Attributes (each clipped to 30 chars) => Count length of
> resulting AttribString
> count length of ProductName
> if length(ProductN ame & AttribString) > 60 then
> clip(AttribStri ng) to 30
> if length(ProductN ame & AttribString) > 60 then
> ProductName=lef t(ProductName,6 0-len(AttribStrin g))
> endif
> endif
> This would probably be possible with T-SQL if I could put it into a
> function but I would not like to attempt it as part of a SELECT
> statement. The easiest is to leave this to the client and write a
> simple VBScript function. The other advantage is that since the
> presentation layer (website) has control on the way this string is
> displayed it might as well get control on how many chars are displayed,
> so this wraps it quite nicely.[/color]

Indeed putting it in a UDF could have performance implications.

The good news is that this is precisely what the CLR is good for in
SQL 2005. That is, in SQL 2005, you would write a scalar UDF in VB .Net
(or C#). This gives you the compiled performance of a 3GL language
as opposed the interpreted T-SQL. Also, in SQL2005, they have improved
performance on UDF calls, so even scalar UDFs in T-SQL are less
expensive in SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp