Concatenate strings after assigning text in place of bit strings

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:

select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

I solved my own problem:

I needed to include an else statement

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.newdatabase

|||

I solved my own problem:

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.database

Computed columns and constant

Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?

Przemek

hi,

computed columns can only be based on constants, variables, functions and columns of the same table, and the last one exclude your

Users have to have possibility to change currency rate easily (maybe another table or constant)

the client side calculation can be an option, as the same can be true at SELECT time as well.. you can do lot of sophisticated things in your SELECT statement, defining a "virtual" columns as well based on calculations..

ie:

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Rate varchar(10) NOT NULL,

Val decimal (8,4) NOT NULL

);

CREATE TABLE dbo.t2 (

Id int NOT NULL,

RateId int NOT NULL

CONSTRAINT fk_t2$has$rates_on_t1 FOREIGN KEY

REFERENCES dbo.t1 (Id),

Rent decimal(8,4) NOT NULL

);

INSERT INTO dbo.t1 VALUES ( 1 , 'Dollar', 1 );

INSERT INTO dbo.t1 VALUES ( 2 , 'Euro', 1.25 );

INSERT INTO dbo.t2 VALUES ( 1 , 1 , 5 );

INSERT INTO dbo.t2 VALUES ( 2 , 2 , 5 );

SELECT t2.Id, t1.Rate, t2.Rent, t1.Val * t2.Rent AS [calculated value in base rate units]

FROM dbo.t2 t2

RIGHT JOIN dbo.t1 t1 ON t1.Id = t2.RateId;

DROP TABLE dbo.t2, dbo.t1;

--<

Id Rate Rent calculated value in base rate units

-- - --

1 Dollar 5.0000 5.00000000

2 Euro 5.0000 6.25000000

regards

complex queries and performance

i have a question on the above mention... say if i need to run a batch
procedure with very complex and multiple set base CRUD functionalites, does
it make any difference if i break them down into 1 store procedure per query
or multiple query per procedure. Presumbly everything is set to run with no
lock, does it make any difference?It usually depends. Consider recompilations: what needs to be
recompiled if you refresh statistics on one table? What needs to be
recompiled when you add an index on one table? How much time does
recompilation take?