Configuration Error...name wanted

Hi, does somebody recognize the problem with my code? -Thanks!

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.

Source Error:

<

sessionStatemode="SQLServer"stateConnectionString="Jensen"sqlConnectionString=

"datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>

problem solved

<

sessionStatemode="SQLServer"stateConnectionString="tcpip=BRAHMA"sqlConnectionString="data source=epscor;user id=EpscorUser;password=epsc0r"cookieless="false"timeout="60"/>

coneverting oledb code to connect to remote sqlserver

i have some oledb code made in c#(vs 2005) it is for local msaccess file. i want to conevert the code for sql server where connection string placed in web.config file seperately. please help me.
here is code

private void buildGrid(){string conStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; conStr += Server.MapPath("~/common/db/demo.mdb"); System.Data.OleDb.OleDbConnection dbCon =new System.Data.OleDb.OleDbConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.OleDb.OleDbDataAdapter dbAdapter =new System.Data.OleDb.OleDbDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

please healp me..

Try this:

private void buildGrid(){string conStr ="Server=MyServer; Database=MyDatabase; User Id = MyUser; Password=MyPassword";System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

I hope this will help.

Good luck.

|||

thank you,

i got you but i placed my connection string in to the web.config file how to use connection string placed in the web.config file with code you posted

|||

Try this:

private void buildGrid(){string conStr =ConfigurationSettings.AppSettings("MyConnectionStringInWebConfig"); System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

Here is a good link:
How to: Read Connection Strings from the Web.config File
http://msdn2.microsoft.com/en-us/library/ms178411.aspx

Good luck.

Conditionally showing/hiding a parameter

I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

Yes, this is possible. Lets say your 5th parameter is driven off of a query, then you can pass what ever is selected in the 4th parameter as an input to the query. Also in the report parameter your 5th parameter should be below the 4th parameter meaning the order of the parameters is important.

Let me know if you would want me to elaborate this further.

|||

You're describing how to change the available values of the 5th parameter based on the choice made for the 4th parameter. I think the original question is it possible to show or hide the 5th parameter based on the choice made for the 4th parameter.

I have never found a way to conditionally hide parameters and don't believe it's possible. If I'm wrong then I'd love to hear how to do it. When we've encountered cases like this we've had to just change the values of the 5th parameter (as Techquest has described) to have only a single item available such as "n/a" and then defaulted to that value.

I'd also love to hear if there's a way to conditionally change the label for parameters. I don't believe this is possible either but there's a lot of smart people out there and it would be great if someone could show me that I'm wrong.

tia

-bruce

|||Bruce, that's what I wanted to do.

I haven't been working with SSRS for too long, I wonder if it's possible to write a script or extension to allow more flexibility in the parameters.

I like the N/A idea though, that'll work if I can't figure out anything new.

Thanks,

Erik

conditional logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

Conditional join?

I have a dropdown box in a .net app that I am populating from a couple
tables.
One is a salesrep table, with the sales rep code and a name in it.
The other table is a table with a sold to sales rep code and a ship to
sales rep code - which may not always be the same in both these columns.
One of those two columns will be in the salesrep table.
Is there a way to do a join on the first table from the second?
Basically, I want to pick out a statement like:
select repname from salesrep_table
inner join sourcetable on
sourcetable.soldtorepcode = salesrep_table.repcode or
sourcetable.shiptorepcode = salesrep_table.repcode
In other words, I want to show this repname in a box when the repcode
that is selected is in either the soldto repcode column, or the shipto
repcode table.
Wiil this work? If not, how would you go about doing this?
Any help would be appreciated.
BC>> I have a dropdown box in a .net app that I am populating from a couple ta
bles. <<
This is a database group and we do not care about the front end. The
basic principle of a tiered architecture is that display and input are
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.
This is a major screw up. There is no LOGICAL difference in these
guys. A sales rep is a sales rep; if you want to flag with a role,
then that goes into a column, not a separate table.
Look up "attribute splitting" as a design error.|||Hi Blasting Cap,
This help?
select repname
from salesrep_table as st
inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
t.shiptorepcode )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:OiYTzAWmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>I have a dropdown box in a .net app that I am populating from a couple
>tables.
> One is a salesrep table, with the sales rep code and a name in it.
> The other table is a table with a sold to sales rep code and a ship to
> sales rep code - which may not always be the same in both these columns.
> One of those two columns will be in the salesrep table.
> Is there a way to do a join on the first table from the second?
> Basically, I want to pick out a statement like:
> select repname from salesrep_table
> inner join sourcetable on
> sourcetable.soldtorepcode = salesrep_table.repcode or
> sourcetable.shiptorepcode = salesrep_table.repcode
> In other words, I want to show this repname in a box when the repcode that
> is selected is in either the soldto repcode column, or the shipto repcode
> table.
> Wiil this work? If not, how would you go about doing this?
> Any help would be appreciated.
> BC|||> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
Check the group title AGAIN -> MICROSOFT.SQLSERVER.PROGRAMMING
SQL Server is not just a database rather RDBMS - its a data processing
engine with many different features other than just SQL and store/retrieve.
If you don't like the posts then keep your gob shut and don't answer them!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151366508.537061.16560@.m73g2000cwd.googlegroups.com...
> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
>
> This is a major screw up. There is no LOGICAL difference in these
> guys. A sales rep is a sales rep; if you want to flag with a role,
> then that goes into a column, not a separate table.
> Look up "attribute splitting" as a design error.
>|||On 26 Jun 2006 17:01:48 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>This is a database group and we do not care about the front end. The
>basic principle of a tiered architecture is that display and input are
>done in the front end and never in the back end. This a more basic
>programming principle than just SQL and RDBMS.
The questions was not about how to program the front end, it was
simply about database retrieval. Mentioning the front end when
describing the query requirement and asking for help with the query is
not a problem.

>This is a major screw up. There is no LOGICAL difference in these
>guys. A sales rep is a sales rep; if you want to flag with a role,
>then that goes into a column, not a separate table.
>Look up "attribute splitting" as a design error.
Nonsense. This exact configuration occured where I worked, and was an
important BUSINESS REQUIREMENT. Surely you remember that the database
must model the business? The salesman who walked into the customer's
headquarters and made a sale that resulted in orders going to hundreds
of stores received credit for the sale. However, each store (shipto)
was also associated with a local salesman. Sales reporting required
tracking BOTH associations, the appropriate one chosen for any given
report.
Roy Harvey
Beacon Falls, CT|||Tony:
Thanks for the help..
It does what it is supposed to do - but not exactly what I want.
The table structure is sort of like this:
Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
Cust1 Rep1 Rep1 Grp1 Grp1
Cust2 Rep1 Rep2 Grp1 Grp2
Cust3 Rep1 Rep3 Grp1 Grp3
Cust4 Rep4 Rep4 Grp2 Grp2
Because Coalesce returns the first non-null value, it returns Rep1 in
the query when I actually want Reps 1, 2 & 3 to show up. I am going to
use this same thing when determining which groups I want to show up, too.
I believe if I reverse the columns (shipto first, soldto second), it may
do exactly what I wanted.
Many many thanks for your succinct help.
BC

> Hi Blasting Cap,
> This help?
> select repname
> from salesrep_table as st
> inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
> t.shiptorepcode )
> Tony.
>|||Hi BC,
If you get stuck just post another thread - good luck.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:O3esDoemGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Tony:
> Thanks for the help..
> It does what it is supposed to do - but not exactly what I want.
> The table structure is sort of like this:
> Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
> Cust1 Rep1 Rep1 Grp1 Grp1
> Cust2 Rep1 Rep2 Grp1 Grp2
> Cust3 Rep1 Rep3 Grp1 Grp3
> Cust4 Rep4 Rep4 Grp2 Grp2
> Because Coalesce returns the first non-null value, it returns Rep1 in the
> query when I actually want Reps 1, 2 & 3 to show up. I am going to use
> this same thing when determining which groups I want to show up, too.
> I believe if I reverse the columns (shipto first, soldto second), it may
> do exactly what I wanted.
> Many many thanks for your succinct help.
> BC
>
>

Conditional Image Visibility with Embedded code

I am trying to create a embedded function that will accept a dataset field
value and based on the value, make an image visibility hidden or visible in
the body of the report. I receive an error that the mathimage is not
declared. How do I reference an image in the body of the report. The field is
within a list and based on the subject an image is displayed in the bodiy of
the report.
Below is a sample of the embedded code.
Function VisibleYN(subject as string,passedyn as boolean) as double
if subject = "Math" and passedyn = true then
mathimage.visibility = visible
end if
if subject = "Science" and passedyn = true then
scienceimage.visibility = visible
end if
THe following expression is in the visibility property tab of the textbox
that holds the subject.
code.visibleyn(fields!subject.value as string, passedyn as boolean)
Any help would be appreciated!
--
MarieThe actual property is under the visibility and is named "IsHidden", so your
function should return a boolean rather than try and set a property.
Change
mathimage.visibility = visible
to
Return False
and likewise for your science one to get it to work.
"Marie" wrote:
> I am trying to create a embedded function that will accept a dataset field
> value and based on the value, make an image visibility hidden or visible in
> the body of the report. I receive an error that the mathimage is not
> declared. How do I reference an image in the body of the report. The field is
> within a list and based on the subject an image is displayed in the bodiy of
> the report.
> Below is a sample of the embedded code.
> Function VisibleYN(subject as string,passedyn as boolean) as double
> if subject = "Math" and passedyn = true then
> mathimage.visibility = visible
> end if
> if subject = "Science" and passedyn = true then
> scienceimage.visibility = visible
> end if
> THe following expression is in the visibility property tab of the textbox
> that holds the subject.
> code.visibleyn(fields!subject.value as string, passedyn as boolean)
> Any help would be appreciated!
> --
> Marie|||The reason I am trying to set the property is because the function is being
called from a textbox bound to a field. Based on the value in the field I am
setting the property of a checkmark image. If I return the boolean value how
do I use this value to set the property of another report item? I was hoping
I could do something like Reportitem.image1.ishidden = false.
Any suggestions?
Thanks for your help.
--
Marie
"David Swanson" wrote:
> The actual property is under the visibility and is named "IsHidden", so your
> function should return a boolean rather than try and set a property.
> Change
> mathimage.visibility = visible
> to
> Return False
> and likewise for your science one to get it to work.
> "Marie" wrote:
> > I am trying to create a embedded function that will accept a dataset field
> > value and based on the value, make an image visibility hidden or visible in
> > the body of the report. I receive an error that the mathimage is not
> > declared. How do I reference an image in the body of the report. The field is
> > within a list and based on the subject an image is displayed in the bodiy of
> > the report.
> >
> > Below is a sample of the embedded code.
> >
> > Function VisibleYN(subject as string,passedyn as boolean) as double
> > if subject = "Math" and passedyn = true then
> > mathimage.visibility = visible
> > end if
> > if subject = "Science" and passedyn = true then
> > scienceimage.visibility = visible
> >
> > end if
> >
> > THe following expression is in the visibility property tab of the textbox
> > that holds the subject.
> >
> > code.visibleyn(fields!subject.value as string, passedyn as boolean)
> >
> > Any help would be appreciated!
> >
> > --
> > Marie|||You can use an expression for the hidden property of the image in the report.
Right click on the image, expand Visibility, then use this in the Hidden
property
=TextBox1.Value="Math"
or
=iif(textBox1.Value="Math",true,false)
I was looking for something similar the other day, and I found the custom
code is a little quirky because I could not find a way to access control
members via code.
When I passed Me as an object, I got some interesting results. For example,
try
this:
function Test(me as object) as boolean
msgbox("String=" & Me.ToString)
return false
end sub
TextBox9.Value=Code.Test(me)
Check out the title of the msgbox itself! It looks like there is funcky
name mangling going on in the host, which prevents us from using objects
directly in custom code.
msgbox("Name=" & Me.Name) indicates a public member not available, so it
appears that unless there is a reflection trick, I have yet to find a way to
access member variables of a report control in custom code.
I'll keep an eye on this thread, and I hope this is of some help to you..
Dwayne

Conditional Full-text search

Is there any way to use full-text search conditionally? For example, I have a query:

Code Snippet

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]
where
Items.ItemId = 1
and
Items.Type >= 0
order by
Search.Rank DESC

How can I put where clause to optimize my query? I want where clause to be executed before freetexttable, for more perfomance. It's obvious, that full-text search is not optimized well in my case, because where is executed after full-text search.

Is there any way to do it? Thank you in advance.

And if I have a query

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]

Is there any way to force freetexttable return only first 10 results, but not all of them? (I know, I can do it after freetexttable with 'select top 10', but I want inside freetexttable, to minimize the load of my SQL Server).
|||Read
"Items.ItemId = 1"
like
"Items.ItemId in (1, 2, 3, 4, 5, 10"
|||Unfortunately in this version of SQL Server, freetext search and SQL Server is separated from each other. Therefore the query optimizer is not able to optimize both queries within one step. You will need to use the optional top parameters provided for freetext search. Look in the BOL:

top_n_by_rank

When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.

If filtering is performed in addition to the FREETEXTTABLE predicate, the filter is applied to the top n rows and fewer than top_n_by_rank rows will be returned. Enabling the precompute rank option in the sp_configure stored procedure can increase the prerformance of FREETEXTTABLE queries that use the top_n_by_rank parameter. For more information, see sp_configure (Transact-SQL) and sp_fulltext_service (Transact-SQL).

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||

Hi Zhuravl,

As said by Jean, you can use top_n_by_rank to limit resultset. This is in the BOL:

" Limiting Result Sets to Return the Most Relevant Results

In many full-text queries, the number of items matching the search condition is very large. To prevent queries from returning too many matches, use the optional argument, top_n_by_rank, in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to rank you want returned.

Note:

Using the top_n_by_rank argument returns a subset of rows that satisfy the full-text query. If top_n_by_rank is combined with other predicates, the query could return fewer rows than the number of rows that actually match all the predicates.
With this information, Microsoft SQL Server orders the matches by rank and returns only up to the specified number. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.

If you want only the top 3 matches returned on an earlier example using CONTAINSTABLE, the query looks like the following:

USE Northwind; GO SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C INNER JOIN CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))', 3) AS K ON C.CustomerID = K.[KEY]; GO

Here is the result set:

RANK CompanyName ContactName address
- -- -
123 Bon app' Laurence Lebihan 12, rue des Bouchers 65 Du monde entier Janine Labrune 67, rue des Cinquante Otages 15 France restauration Carine Schmitt 54, rue Royale

This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."

SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY]; GO
"

Other references:

http://www.developmentnow.com/blog/SQL+Server+2005+Full+Text+Search+On+HTML+Documents.aspx

Regards

Nilton Pinheiro

www.mcdbabrasil.com.br

Conditional Formatting - not so conditional?

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .What part of the matrix do you have this in, and what values show in the textbox?|||OK, well you pointed me right to my problem. The values are percentages. I was doing my conditional formatting based on 3 instead of .03 . . .

That is fixed but I still have something else wrong. A very small number of values that are not .03 are coloring red. I am also getting a warning that says "[rsNonAggregateInMatrixCell] The Color expression for the textbox ‘textbox22’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling."

I assume this message is related to my problem.

Here is a sample of what the output is looking like. All of the values formatted correctly except for the 2.60 %. The 2.60% was colored red as if it was >= .03. I know you can't see the colors here:

WRAP

3.50 %

1.22 %

1.15 %

3.13 %

2.43 %

2.60 %

14.21 %

0.00 %

8.41 %

6.14 %

5.23 %

5.23 %

2.42 %

2.42 %

1.87 %

1.87 %

2.39 %

2.39 %

4.17 %

4.17 %

3.15 %

|||

A matrix cell is always in the intersection of a row and a column group. Usually, several detail rows will go into the same matrix cell instance based on the row/column grouping. That's the reason why you get the warning. By just referencing a field without aggregate function (=iif(Fields!Wrap.Value >= 0.03 , "Red", "Green") ), you reference just the field value of the first row in that cell - while the textbox in the cell may actually show the aggregate of the values.

You should use the following color expression instead: =iif(Sum(Fields!Wrap.Value) >= 0.03 , "Red", "Green")

-- Robert

|||I made the change that you suggested. Which eliminates the warning that I was receiving. However, I am still getting inconsistent results with the formatting. For instance, of the following values only 5.09, and the two 0.00's are colored green.

Could it be because I have enabled drill-down in this report? Even if I drill down to the lowest level the colors still are not consistently populating based on the conditions in this formula.

WRAP

5.09 %

2.73 %

0.00 %

0.00 %

1.77 %

2.55 %

3.61 %

6.68 %

3.02 %

3.50 %

2.86 %

Conditional column NAME on insert/update

I need to dinamically select a column in which to insert based on a
parameter, I have this code, but it throws an incorrect syntax
error.
The value that I'm inserting is always static (the current date) what I
need to be dynamic is the column in which it'll be inserted.
How do I dinamically select a column to insert based on a parameter?
Create PROCEDURE dbo.UpdateDetalleOT (
@.eotId int, --Parameter
)
insert into OT (
select Case
when @.eotId = 1 THEN OTFechaBorrador
when @.eotId = 2 THEN OTFechaAAsignar
end
) values ......
Here's the explanation of the case:
Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished...)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...
That's why I need to create a dynamic Insert/Update, because depending
the
state the Job Order will be saved...will depend which column
(DraftDate, ConfirmedDate, etc) to insert the current
date.
Best Regards
Fabio CavassiniYou should go out of your way to avoid dynamic SQL. If that means you have
to write several nearly identical insert statements, then so be it. A
little redundant code is a whole lot easier to understand and to maintain
and a whole lot more secure than dynamic SQL. You could also specify all
columns in the column list and then use CASE in a SELECT clause to insert
NULLs into the nonrelevant columns (That's what will be inserted anyway if
column values aren't supplied.).
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>|||"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>
In an INSERT there is no need to do such a thing. Obviously ALL columns are
affected by an INSERT statement, so just specify values for the ones you
want to populate and defaults or nulls for the ones you don't.
In the case of UPDATE you can use the general form:
UPDATE tbl
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3),
..
WHERE ...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I didn't have realized that every column is affected with the insert,
I'll use condition for the values...and use COALESCE for the update.
Thanks very much for your help
Best Regards
Fabio Cavassini|||This is how I implemented:

>INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
>SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
I include all columns and the insert, and then I conditionally select
the value (null or current date) according to the parameter value.
Best Regards
Fabio Cavassini

Conditional Column Name On Insert

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@.eotId int,
)

insert into OT (
select Case
when @.eotId = 1 THEN OTFechaBorrador
when @.eotId = 2 THEN OTFechaAAsignar
end
) values ...

Best Regards
Fabio Cavassini
http://www.pldsa.com--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Try something like this instead:

If @.eotID = 1
BEGIN
INSERT INTO OT (OTFechaBorrador)
VALUES ...
END

IF @.eotId = 2
BEGIN
INSERT INTO OT (OTFechaAAsignar)
VALUES ...
END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F9/oechKqOuFEgEQL0DwCfVQyA7xrkbFiBhXHJwcZwh6jlv1sAnj8 x
Ig0V5L9rm9Cpt13pG+Talbie
=0ECz
--END PGP SIGNATURE--

cavassinif@.gmail.com wrote:
> I need to dynamic select a column in which insert a vale based on a
> parameter value, I have this code, but it throws an incorrect syntax
> error.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int,
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ...|||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.|||On 20 Jan 2006 11:49:20 -0800, cavassinif@.gmail.com wrote:

>I need to dynamic select a column in which insert a vale based on a
>parameter value, I have this code, but it throws an incorrect syntax
>error.
>How do I dinamically select a column to insert based on a parameter?
>Create PROCEDURE dbo.UpdateDetalleOT (
>@.eotId int,
>)
>insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
>) values ...
>
>Best Regards
>Fabio Cavassini
>http://www.pldsa.com

Hi Fabio,

You can't insert into just one column - you insert a complete row, and
you'll have to give values (either real values or NULL) for all columns.
Sure, the language permits you to leave out some columns, but that's
just a shorthand way for specifying that you want to insert the defined
DEFAULT value (if any) or NULL in all the other columns.

To do what you appear to want (and I *really* hope that this is an
extremely simplified illustration of the real problem, because if this
is your real procedure, you have much, much bigger problems), you can
either use the code posted by MGFoster, or use

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar)
SELECT CASE WHEN @.eotId = 1 THEN ... ELSE NULL END),
CASE WHEN @.eotId = 2 THEN ... ELSE NULL END)

--
Hugo Kornelis, SQL Server MVP|||Thanks for all your advices:

MGFoster:
Yes, this would be a solution...but the table in which I'm inserting
has more than 30 columns...the insert code is huge...and I wouldn't
like to copy the insert for just one column of difference.

--CELKO--
Here's the explanation of the case:

Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished...)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...

That's why I need to create a dynamic Insert, because depending the
state the Job Order will be saved...will depend which column
(DraftDate, ConfirmedDate, etc) it will have to insert the current
date.

Hugo:
I don't want to select a dynamic value... the value will be always the
current date, I need to dinamically specify in which column I will
insert the current date

Best Regards
Fabio Cavassini|||On 22 Jan 2006 15:12:16 -0800, cavassinif@.gmail.com wrote:

(snip)
>Yes, this would be a solution...but the table in which I'm inserting
>has more than 30 columns...the insert code is huge...and I wouldn't
>like to copy the insert for just one column of difference.

Hi Fabio,

Hmmm. Maybe you could explain in some more detail what is the actual
business problem you're trying to solve. A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table - but I can only say for
sure if I know more about your actual problem and your current table
structure.

(snip)
>Hugo:
>I don't want to select a dynamic value... the value will be always the
>current date, I need to dinamically specify in which column I will
>insert the current date

I had used ellipsis as a placeholder for the value to delete. Now that I
know it's the current date, I can complete my proposed code. I've also
added a third column and ellipsis to show how you can extend this to as
many columns as you need.

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
...

If called with @.eotId equal to 1, this will create a row with
CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
two (or more) other columns. If @.eotId is 2, OTFechaAAsignar will be the
current datetime and the other columns are NULL. Etc, etc.

--
Hugo Kornelis, SQL Server MVP|||Do it in two steps: Insert the common column data first, then do an
update to the appropriate row/column based on the new entry and the
type.

Hugo Kornelis wrote:
> On 22 Jan 2006 15:12:16 -0800, cavassinif@.gmail.com wrote:
> (snip)
> >Yes, this would be a solution...but the table in which I'm inserting
> >has more than 30 columns...the insert code is huge...and I wouldn't
> >like to copy the insert for just one column of difference.
> Hi Fabio,
> Hmmm. Maybe you could explain in some more detail what is the actual
> business problem you're trying to solve. A parameter that governs in
> which of 30 columns the current date has to be inserted sounds as if the
> best solution would be a redesign of your table - but I can only say for
> sure if I know more about your actual problem and your current table
> structure.
> (snip)
> >Hugo:
> >I don't want to select a dynamic value... the value will be always the
> >current date, I need to dinamically specify in which column I will
> >insert the current date
> I had used ellipsis as a placeholder for the value to delete. Now that I
> know it's the current date, I can complete my proposed code. I've also
> added a third column and ellipsis to show how you can extend this to as
> many columns as you need.
> INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
> SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
> ...
> If called with @.eotId equal to 1, this will create a row with
> CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
> two (or more) other columns. If @.eotId is 2, OTFechaAAsignar will be the
> current datetime and the other columns are NULL. Etc, etc.
> --
> Hugo Kornelis, SQL Server MVP|||The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH|||The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH|||Thanks for all the replies,

I implemented it as Hugo sayed, with condition in the value:

>INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
>SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),

I haven't realized that in an insert...in fact all values are
modified...consequently I need to put the condition in the value.

>The problem is your database design. Instead of 30 columns for dates
>A parameter that governs in
>which of 30 columns the current date has to be inserted sounds as if the
>best solution would be a redesign of your table

I know..that there are many columns...but...the business model
requires it. In addition I don't like to have many tables in my
databases, it's too much simple to maintain a reduced (respecting
normal forms, of course) set of tables.

Best Regards
Fabio Cavassini

conditional color property based on date

I want to use an IIF statement to set the color property for a text box.
Here is my code that is not working:
=IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
Now(), "red", "black")Use " instead of '
( ' is a comment for vb, even in reporting services)
Mike G.
"ladydi_1226" <ladydi1226@.discussions.microsoft.com> wrote in message
news:92B486DB-EFFA-49BC-AEE3-76CBBF4A24AE@.microsoft.com...
>I want to use an IIF statement to set the color property for a text box.
> Here is my code that is not working:
> =IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
> Now(), "red", "black")

condition in script

hi

I need to alter a procedure depend on some information .

if A is true then

alter procedure .... < code 1>

else

alter procedure .... <code 2>

is it possible?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1557658&SiteID=1

Check out my post in there.

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

concatinating string values from multiple rows

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:

CREATE TABLE [dbo].[TransactionTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MaskValue] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[HDResponse](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[LoggedDateTime] [datetime] NULL,
[ResponseTypeTripID] [int] NULL,
[ResponseTypeID] [int] NULL,
[ResponseTypeObjectID] [int] NULL,
[ObjectID] [int] NULL,
[IDHolderID] [int] NULL,
[TransportCode] [int] NULL,
[CardID] [int] NULL,
[IssueCode] [smallint] NULL,
[EventDateTime] [datetime] NULL,
[Response] [bit] NULL,
[TranTypeID] [int] NULL)
ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

What version of SQL Server are you using? SET CONCAT_NULL_YEILDS_NULL is ON by default unless you have changed the settings after connecting to SQL Server. This is true for any connections made via ODBC/OLEDB. You can use the PIVOT operator in SQL Server 2005 to solve this problem. You can also do this using a standard SQL technique like below:

select max(case p.trantype when 1 then p.description else '' end) +
max(case p.trantype when 2 then ' ' + p.description else '' end) +
max(case p.trantype when 4 then ' ' + p.description else '' end) +
max(case p.trantype when 8 then ' ' + p.description else '' end) +
max(case p.trantype when 16 then ' ' + p.description else '' end) as "Transaction Type"
from (
select t.description, (t.MaskValue & h.TranTypeID) as trantype
from HDResponse as h
join transactiontypes as t
on (t.MaskValue & h.TranTypeID) in (1, 2, 4, 8, 16)
) as p

Concatenation of patient name question

I have a report built that returns patient information. My code to concatenate the patient name fields (last name, first name, middle name and surname) work fine unless the middle name or surname fields are null, then it returns the concatenated patient name field as null. The code is posted below. Is there an easy method to determine if the field is null and then apply the correct logic to concatenate the name with the elements that are not null?

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_mname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_sname))

as SRM_PatientName

Hello,

Yes, you can. This should work.

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_mname)), '')

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

Hope this helps.

Jarret

|||

Use the following expression,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

|||

I created a lot of reports with concatenated columns at least 20 in different combinations and all I needed was the CONVERT function. But you need ISNULL or COALESCE so here are some examples and the link for SQL Server Concatenation documentation for more options. Hope this helps.

COALESCE(a,'') + COALESCE(b,'')

ISNULL(a, ”) + ISNULL(b, ”)

http://msdn2.microsoft.com/en-us/library/ms177561.aspx

|||

Thanks Jarrett and Manivannan. I have adapted your examples and they're working great. I appreciate your helpl

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?
<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegro ups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA
|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.
|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegr oups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegroups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegroups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id