Concatenating Problem Two Field In Case Expression

Jun 19, 2008

Dear all,
I am using sql server 2005.
I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament
Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null

select @DOCNAME = CASE
WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname
ELSE dr_cust_name + dr_cust_lastname
END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE

Please suggest some ideas or any other idea please suggest
Thank you.

View 1 Replies


ADVERTISEMENT

Concatenating With A Case

Apr 21, 2005

I have a table with 1-4 20 character description fields which need to be concatenated into one value. The data is stored like below

patientid allergy1 allergy2 allergy3 allergy4
100
200 tylenol
300 tylenol bees

The way the data is stored there would never be a allergy3
with an allergy2 being null (the blanks are nulls)

I tried below but it doesn't like the case and concatenation. ANy better ideas

select patientid,allergy_cmt1 +
allergy_cmt2 =
case
when allergy_cmt2 = null then null
else allergy_cmt2 + ','
end
from cms_vw_ptdrugallergies2

View 3 Replies View Related

Concatenating Parameter Values && Text; Nested CASE Statements

Sep 27, 2007

Hello.  I'm trying to reduce some code in my stored procedure and I'm running into lots of errors.  I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial.
I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw).  I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this.
So far I've tried the following with many errors.WITH Users AS (
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC')
WHEN @OrderBy='FirstName' THEN FirstName
WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC')
WHEN @OrderBy='LastName' THEN LastName
END
) AS Row,
UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM
vRF_Users
)

SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM Users
WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
 
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc.
I also tried: DECLARE @OrderByDirection varchar(32)
DECLARE @DESC varchar(4)
SET @DESC = ' DESC'

IF @Direction = 'DESC'
BEGIN
SET @OrderByDirection = (@OrderBy + @DESC)
END
And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection
ELSE @OrderBy
ENDObviously this didn't work either.  Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
 
Thanks in advance,
Jen

View 26 Replies View Related

Concatenating A Field From A View Into One Field

Feb 4, 2008

Hi i need to concatenate a value into one field, i currently have a "view" in my sql which is retrieving values from a couple of tables including a junction table "tblUser_Constructor", someof these values are repeating at the moment, the only field changing is the constructor field, which i am trying to concatenate, for instance the following is happening;
 
User_ID       UserName      Constructor_ID    Constructor
1                 Jim                       2                     Keeley
1                 Jim                       5                     Truro
 
 
 below is my view;
 
CREATE VIEW dbo.vwUserASSELECT         dbo.tblUser.User_ID, dbo.tblUser.Username, dbo.tblUser.User_Password, dbo.tblUser.User_Title, dbo.tblUser.User_Forename,                       dbo.tblUser.User_Surname, dbo.tblUser.User_Phone, dbo.tblUser.User_Mobile, dbo.tblUser.User_Email, dbo.tblUser.Access_Right_ID,                       dbo.tblUser.Tstamp, dbo.tblUser.Professional_Qualifications, dbo.tblUser.Organisation_Name, dbo.tblUser.Organisation_Address,                       dbo.tblUser.Is_MainContact, dbo.tblUser.LastLogin_DateTime, dbo.tblUser_Constructor.Constructor_ID, dbo.tblAccess_Right.AccessLevel AS [Group],                       dbo.tblCompany.Company_Name AS ConstructorFROM            dbo.tblCompany RIGHT OUTER JOIN                      dbo.tblUser_Constructor ON dbo.tblCompany.Company_ID = dbo.tblUser_Constructor.Constructor_ID RIGHT OUTER JOIN                      dbo.tblUser LEFT OUTER JOIN                      dbo.tblAccess_Right ON dbo.tblUser.Access_Right_ID = dbo.tblAccess_Right.Access_Right_ID ON                       dbo.tblUser_Constructor.User_ID = dbo.tblUser.User_ID

View 13 Replies View Related

Concatenating Multiple Returns On One Field Only

Nov 18, 2004

I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.

Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

My actual SQL code, if you are interested, is:

SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_id

View 1 Replies View Related

Concatenating Field For Multiple Records

May 30, 2008

Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:

ID.....Code
5......33
5......23

ID.....Code...Result
5......33.....33 23
5......23.....33 23

I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc...

(SELECT SUM(CODE)
FROM Table
WHERE ID = Table.ID) AS Result

But I don't know how to write it so it will combine strings.

View 5 Replies View Related

Concatenating A Field While Grouping Records

Jul 20, 2005

All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug

View 2 Replies View Related

Concatenating Three Longint Fields Into A Date Field

Apr 20, 2001

Good afternoon one and all,

I have the following problem that I can use some help with :

I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement

Something like

SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate

(the above line does not work)

Hope that is clear, thanx in advance for any and all help

Gurmi

View 1 Replies View Related

SQL Server 2012 :: Concatenating Multiple Records Into One Field

Oct 5, 2015

I have a requirement where I have the following separate tables:

Table A:
FldA FldB
34
35
43
53
54
55
64
74
75

Table B:
FldC FldD
1Break Begin
2Break End
3Out
4In
5Dept

Desired Result:
FldA FldD
3 In;Dept
4 Out
5 Out;In;Dept
6 In
7 In;Dept

I have played around with the newly discovered 'for xml path' but I can't quite seem to get the sql syntax right.

View 2 Replies View Related

SQL Server 2008 :: Concatenating Multiple Text Fields Into One Field In Another Table

Oct 7, 2015

SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).

I have Table1 Containing two fields with the below entries

VehicleType Name

Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan

The result I'm looking for would be

Table2

Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan

View 1 Replies View Related

Do A CASE With A Expression

Aug 16, 2007



I know i can do a "IF" using expressions, the question is, can i do a CASE?

Thanks!

View 3 Replies View Related

Can I Use CASE Expression In AND Condition?

Jul 8, 2005

Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah(   @id INT,   @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND   (      CASE @val WHEN 1 THEN otherCol = someValue END      CASE @val WHEN 2 THEN otherCol != someOtherVlaue END   )-------------------------------------------

View 2 Replies View Related

Case Expression In Where Claus

Jan 11, 2006

I there a way to differ the filtering field through a Case expression in the Where claus?
ex:
Where   Case    When @StuNum = ''    Then S.SSN = @SSN   Else     S.StuNum = @StuNum  End
And ...
The actual field to filter by differs, but I can't seem to find the right syntax to do this.
Any help is appreciated : )

View 3 Replies View Related

Using Not Equal In Case Expression

Jul 22, 2013

The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <

SELECT DISTINCT CASE CUST_EMLAD_TX
WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX
END
FROM ...

View 2 Replies View Related

Problem With Case Expression

Sep 30, 2005

In the Portal1 case expression in the script at the bottom I would liketo replace where the result 1 is returned, with the substring functionreturned as Portal{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}However, I am experiencing errors. I think it is because The substringfunction will not return a number as the case expression expects so Imust incorporate cast or convert, but do not know how. Can you help???SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8)- (CHARINDEX('tonep', Field1) + 4)) AS portal,Table.*FROM Table

View 2 Replies View Related

Problem With Case Expression

Dec 4, 2007

I'm trying to write a case expression similar to the expression listed below. I basically want to compare the Anniversary date to the current date. Whenever I write the expression like this I get a syntax error.

Msg 102, Level 15, State 1, Line 147

Incorrect syntax near '<'.


Case AnniversaryDate

WHEN AnniversaryDate < GETDATE() Then 'Not Yet'

WHEN AnniversaryDate = GETDATE() Then 'Now"

ELSE 'You Missed it'

END AS Anniversary

Could someone please guide me in the correct syntax for this expression.
Thanks

View 5 Replies View Related

Error In CASE Expression

Aug 25, 2006

I would appreciate assistance as to how I should correct the following CASE expression::

BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END

I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.

View 3 Replies View Related

Problem With CASE Expression

May 2, 2006

Hi, all here,

I have a problem with CASE expression in my SQL staments.

the problem is:

when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end

the result was strange: then all the values for column a turned to null.

so what is the problem tho?

Thanks a lot in advance for any guidance.

View 4 Replies View Related

Case Expression Stor Proc, Need Some Help

Jul 10, 2006

ALTER PROCEDURE dbo.TEST_TOTALCALLS
    (
        @varDate as varchar (255),
        @StartDate as datetime,
        @EndDate as datetime
    )
AS

SELECT
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE     (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)

GROUP BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)  ' <---this part gave me an error, because of the comma,
END
ORDER BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~

View 10 Replies View Related

Case Expression In SQL Server 2000

Aug 25, 2006

I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price,        Budget = CASE price         WHEN price > 20.00 THEN 'Expensive'          WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'          WHEN price < 10.00 THEN 'Inexpensive'          ELSE 'Unknown'        END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!

View 5 Replies View Related

Using Case Expression In Exec SP Parameters

Jul 17, 2007

Hi,
 Maybe this is just totally wrong but this is what I'm trying to do.  I want to dynamically insert a value using a CASE expression while executing an external SP from my current SP.
 EXEC uspAddMessage @UserId,
CASE @MyAction
WHEN 'DELETE' THEN 'DELETED TRANSACTION.'
WHEN 'APPROVE' THEN 'TRANSACTION APPROVED.'
WHEN 'REJECT' THEN 'TRANSACTION REJECTED.'
END,
@DateStamp
 Keep in mind that this is in my stored proc that is doing the processing.  I'm calling this common proc when I complete my transactions.
 Thanks,
S

View 2 Replies View Related

Update Sub Query In Case Expression

Oct 3, 2005

Hello everyone,
is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah

View 3 Replies View Related

UPDATE Involving CASE Expression

Oct 25, 2007

I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!

Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:

CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]

There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.

I have created another table called UserMostRecent which contains the most recent record for each user.

I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.

Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"

UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)

Thank you, forum participants. You are the best!

View 4 Replies View Related

Case Expression Within A Stored Proc

Jul 20, 2005

Is it possible? I have a request to create a stored proc that willdynamically add a range to a WHERE clause based on a numeric value of acomment type. If the incoming comment type request is say 10, thewhere clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR ifa 20 is passed in the clause would read IN(20,21.....)So I was thinking that a CASE expression within the proc would be thebest way to go, but have had no luck in finding an example or any otherrelated information regarding CASE exp in a proc.TIABill

View 3 Replies View Related

Transact SQL :: CASE Expression - NULL

Jun 19, 2015

DECLARE @I1 VARCHAR(5),
@I2 VARCHAR(5)
;
WITH cte
AS (SELECT 1 AS i,
'val1' AS j UNION ALL
SELECT 2,
'val2')

[code]....

Why @i1 is null but not @i2 ? I'm trying to assign values after grouping by the column.

View 17 Replies View Related

Transact SQL :: CASE Expression In WHERE Clause?

Nov 6, 2015

I have a scenario where the End User is going to select two parameters.  @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.

If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.

@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).

@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:

WHERE
 
 gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
 AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
 AND gl.Location = @Location

View 9 Replies View Related

Converting A String Expression To Proper Case

Oct 22, 2000

How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)

Just a couple of typical examples of how the data should appear ~

payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer

View 1 Replies View Related

Case Expression Plus Outer Join In Ole Db Source

Oct 26, 2006

I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.

In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b)
select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b
from table_1 left outer join table_2 on table_1.column_c = table_2.column_c


That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)

But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420
The column status returned was: "The value violated the integrity constraints for the column.".


And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.

So what is SSIS doing to screw up the results of the SELECT command???

View 6 Replies View Related

Error Using Case Expression In Order By Statement

Feb 22, 2008

I have been trying to get the following Selects to work using a case expression in the order by section.

I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.

If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.

Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.


Thanks
J
declare @date
set @date = '2001'



select internalID from section_data_v3

union

select internalID from section_data_v4

order by
case when (@date = '2001') then internalID end desc

View 5 Replies View Related

Like Comparison Operator In An Expression To Ignore Case

Aug 31, 2007

How do you make the "Like" comparison operator ignore case in an expression??

Expression looks like this:




Code SnippetParameters!CompanyFilterOp.Value = "%",Fields!company.Value like "*" & Parameters!Company.Value & "*"





BTW, the expression above is part of a switch expression, and is at the table level.

Data contains "General Industry" in Company column from database.

User enters "indust" in Company Parameter text box, and result is no data found.

User enters "Indust" in Company Parameter text box, and data is returned.

Thanks in advance for your time in responding.

View 3 Replies View Related

SELECT Query Including JOIN And CASE Expression

Aug 12, 2012

An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.

tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,

[code]...

I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.

If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:

ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple

this would suggest the original syntax is close to being accurate but I can not get it to work.

View 2 Replies View Related

Wrong Evaluation Of A Variable With Expression In Case It Is Used Simultaneously By Several Tasks

Aug 22, 2007



Hi guys,

I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.

The details are as following:

There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.

Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.

After series of consequent executions I have got following log file:

€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦



As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.

Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.



Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.

View 5 Replies View Related

Newbie Case/search Question In Derived Column Expression Syntax

Feb 6, 2008



Hi everyone,

I am a newbie to SSIS and looking for answer to a simple search/replace style question.

I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.

A bit like a case statement:

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,


The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.

Thanks for all your help,

regards,

Chris

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved