[RESOLVED] COALESCE Script
Aug 13, 2007
I have a table NUMBERS in which I have Column1 and Column2. Column1 will always be a unique number, yet Column2 may have duplicates.
What I would like to do is return all the data in Columns 1 and 2, yet have Column1 return as a comma seperated value.
Column1 Column2
1 15
2 16
3 16
4 17
Return:
Column1 Column2
1 15
2,3 16
4 17
Any ideas will be great!
Thanks
View 14 Replies
ADVERTISEMENT
Sep 16, 2007
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
View 1 Replies
View Related
Nov 5, 2007
I have a pulldown menu which has like 4 options
producta productb productc and all
I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc
Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC
and I am trying to use coalesce but unable to get my result.
I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.
If I do the following with no max funciton, i see all the values but i just want max from each branch.
DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results
where branch in ('ProductA','Product B','ProductC')
select @buildList
Please let me know how to do this.
View 8 Replies
View Related
Apr 29, 2008
I have a stored procedure which receives a dynamically built WHERE clause. This is then appended to the sql query within like....'select * from table' +@where_clause.
I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this. I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
In my application the user can enter 1-to-many textboxes as search criteria. What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'"
Else
sqlwhere = " where lname like '" & tx_lastname.Text & "'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'"
Else
sqlwhere = " where fname like '" & tx_firstname.Text & "'"
End If
End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated.
View 15 Replies
View Related
May 11, 2008
A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it? 1 SELECT (SELECT Location
2 FROM Location_Table
3 WHERE (Property_Table.LocationID = LocationID)) AS Location,
4 (SELECT TypeOfProperty
5 FROM Type_Table
6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7 FROM Property_Table
8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
View 8 Replies
View Related
Jun 21, 2007
I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?
WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME
View 2 Replies
View Related
Jan 29, 2004
Hi.
I have a piece of a store procedure I don't quite understand, as follows:
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))
What exactly is the COALESCE function doing here with the parameter?
View 9 Replies
View Related
May 14, 2008
How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there
cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],
View 10 Replies
View Related
Oct 4, 2013
I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.I came across a piece of code posted in the forum about the different uses of coalesce.
use adventureworks
DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')
[code]...
View 4 Replies
View Related
Oct 17, 2005
what the hell does that mean!? how do i use it and where?
View 20 Replies
View Related
Mar 11, 2008
Here is my statement
COALESCE (Reg_HomeAdd1, N'') + N', ' + COALESCE (Reg_HomeAdd2, N'') + N', ' + COALESCE (Reg_HomeAdd3, N'') + N', ' + COALESCE (Reg_HomeAdd4, N'')
if one of the fields is null how do I stop two commas showing
ie
The Nook,West Street,,Townsville
View 3 Replies
View Related
Sep 18, 2006
I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.
Below is my sample:
SELECT *
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY q.StudentID
Having
(SUM(Coalesce(q.AbsenceValue),0) = 0.00)
COALESCE(SUM(q.AbsenceValue) = 0.00,0)
I have tried using the coalesce statement a couple of ways with no resolution, pls help!!
View 5 Replies
View Related
Sep 27, 2006
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
View 1 Replies
View Related
Feb 6, 2008
Hi everybody,
I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated.
devmetz
View 4 Replies
View Related
Jun 19, 2008
Hi All
I have a problem in making out why Coalese is considered to be better than ISNULL .According to my investigation Coalesce Returns the data type of expression with the highest data type precedence.If for eg I have declared that I want to return the value only upto 3 char then why will I use Coalesce and override my requirement.Anyone with clear concept about this plz explain.
DECLARE @v1 VARCHAR(3)DECLARE @i1 INT
SELECT ISNULL(@i1, 15.00) /2,
COALESCE(@i1 , 15.00) /2,
ISNULL(@v1, 'Teaser #2'),
COALESCE(@v1, 'Teaser #2')
The result will be
7
7.500000
Tea
Teaser #2
thanks in advance
View 8 Replies
View Related
Aug 7, 2001
Hi guys,
Do you have any idea why COALESCE function gives timeout in SQL2000 and works in SQL7.0
for ex.
in SQL7.0 this statement works fine in one of my SP
ROUND(COALESCE(ABS((SELECT SUM(Amount)))))
but in SQL2000 my SP is not working and my program gives timeout. But, if I change to ISNULL it works fine. Any clues on this issue?
This problem was there in SQL65 with ISNULL and then we have changed to COALESCE. Now, again repeated in 2000.
View 1 Replies
View Related
Sep 8, 2004
hi,
my question is about using coalesce function in SQLServer. This function brings up the first not null value. my problem is i cannot get the corresponding field name. this is what i use
SELECT COALESCE(Stop1, Stop2, Stop3, ...., Stop10)
FROM ...
WHERE ProjectID = #URL.ProID#
it returns,
(no column name)
----------------
1 somebody@somthing.com
the fields in the DB goes like stop1, stop2, stop3,...
so, i need to get which stop i am .thanks in advance.
View 1 Replies
View Related
Jun 10, 2008
I'm still getting error messsage that state "encountered dividing by zero:
Is there a problem with my statement?
SELECT COALESCE (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END) * 1.0 / (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ (SUM(CASE WHEN Advoc_ResDrec = 'No' THEN 1.0 ELSE 0.0 END) - (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)))), 0)
AS ResDrec_MbrQ
FROM limiaca.TPhones_Oct
View 1 Replies
View Related
Feb 9, 2007
hello,
from one of my solution, i havent had time untill now to ask detail what is this coalesce and nullif? Is nullif just like the isnull function? while coalesce is to replace null? can someone explain base on this eg??
d.LocID>= coalesce(nullif(@LocFrom, ''), d.LocID) and
d.LocID<= coalesce(nullif(@LocTo, ''), d.LocID) and
~~~Focus on problem, not solution~~~
View 9 Replies
View Related
Feb 27, 2007
(COALESCE (MaterialNumber, '') + ' - ' + COALESCE (MaterialName, '') )AS materialDescription
I get an error saying cant covert varchar topsoil to type integer
materialnumber = number or null
materialname = name
how do i get
100 - Dirt
- Top Soil
View 3 Replies
View Related
Mar 11, 2008
Please tell me what's wrong in this query. I am using SQL 2005
It gives too many arguments specified
CREATE PROCEDURE spocsearch
(
@nm nvarchar(30), @ID nvarchar(7), @custid nvarchar(10)
)
AS
SELECT nm, ID, custid, custcode
FROM Customer
WHERE
(ID = COALESCE(@ID,ID) OR ID IS NULL) AND
(nm = COALESCE(@nm,nm ) OR nm IS NULL) AND
(custid = COALESCE(@custid ,custid ) OR custid IS NULL)
View 4 Replies
View Related
Aug 20, 2007
Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.
I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.
SELECT DISTINCT @Staging_Off= COALESCE(@Staging_Off + ',', '')+ Offering
FROM xyz
WHERE xyz.Offering NOT IN( SELECT DISTINCT Offering.Offering
FROM Offering Join xyzON Offering.Offering= xyz.Offering
AND Offering.SourceSystem= @SourceSystem
)
View 1 Replies
View Related
Nov 4, 2004
I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.
In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.
FYI I'm using SQL Server 7.0 which does not supports UDF's
Earlier response appreciated
Thanks and Regards
Chandru
View 3 Replies
View Related
May 16, 2012
I'm preparing a report that will display provider number, provider name, and a single field that will show all the counties that specific provider serves. I realize from researching the coalesce posts that this can be done. However, when I try to retrieve data in the same select statement as my coalesce, I get the error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."The listing of the counties must be specific to the provider, so my original code was:
DECLARE @Counties varchar(1000)
SET @Counties = ''
SELECT a.PROV_ID, a.PROV_NAME,
@Counties=coalesce(@Counties,'') + b.COUNTY + ','
from ECBH.dbo.tbl_PROVIDERS a inner join ECBH.dbo.tbl_Provider_Serv_Regions c
on a.PROV_ID = c.PROV_ID
inner join ECBH.dbo.tbl_Regions b
on b.REGION_ID = c.REGION_ID
where c.PROV_ID = a.PROV_ID
and a.MASTER = 1
I thought about creating a table to hold the coalesced values (need to coalesce two other fields as well), but wouldn't an insert to a table fail for the same reason?The counties table does not relate to the provider table, but does relate to a provider_county table (which in turn relates to the provider table).
View 1 Replies
View Related
Feb 6, 2014
If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
Code:
Select
Count(NumOnsite),
originatingCity,
Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
From readytoshipOffsite
Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))
View 11 Replies
View Related
Feb 7, 2007
i have 2 columns which are decimal
one is alway empty and the other always has a value -- i just need which ever one has a value -- i keep getting Error converting data type varchar to numeric
(COALESCE(TicketLoadQty, '') + ' ' + COALESCE(TicketTimeQty, '')) as theQuantity
how do i get around this
View 1 Replies
View Related
Feb 3, 2008
Hi all
If you look at BOL for [COALESCE] function we have:
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression..is an expression of any type.
Return Types
Returns the same value as expression.
-------------------------------------------
Ok as it claimes , arguments can be of any type also the Return value, BUT when i execute the following command:
Print COALESCE('An String',12) -- just for example
and in generel when you have an string argument in this function you will receive the following error:
Syntax error converting the varchar value 'An String' to a column of data type int.
Could anyonel help me?
Thanks in advance.
Regards.
View 5 Replies
View Related
Feb 4, 2008
WHATS the basic diff. between Is Null and Coalesce can someone help me regarding this
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
View 3 Replies
View Related
Mar 25, 2008
I've inherited a terribly designed database. When cells in the tables have nothing in them, rather than being NULL, they're just empty. So now I can't use COALESCE...
Is there a way for COALESCE to check if a cell is empty instead of NULL? And if not, is there a way to get around this?
View 1 Replies
View Related
Jul 23, 2005
Hi everybody,VARCHAR has a higher precedence than CHAR. If you have a querySELECT COALESCE(c1,c2) FROM T1where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the returntype to be varchar(10) similiar to UNION. However it turns out tobe CHAR(10). Does anyone know why that is so?Thanks,Steffen
View 3 Replies
View Related
Sep 28, 2006
If I have an SQL query which returns an aggregate of several decimal fieldslike so:(sum(COALESCE(myDecimal1, 0)+sum(COALESCE(myDecimal2, 0)+sum(COALESCE(myDecimal3, 0)) as MyTotalI get an rounded integer in MyTotal.However, if I do the following:sum(COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)) as MyTotalI get a (proper) decimal value.Does anyone know why the first case returns an Integer?- Don
View 4 Replies
View Related
Jul 20, 2005
I am using a clever product called SQL Expert Pro to optimizeproblematic SQL statements. It works by generating syntacticallyidentical variations of the original SQL, finding all unique executionplans, and batch testing them.Anyway, it generally turns out that a very odd change dramaticallyimproves performance (for a PeopleSoft database on SQL Server 7). Runtime goes from 52 seconds to 8 seconds.The change is to replace a join condition in a where clause with anodd equivalent COALESCE construct, e.g. replace WHERE C.PAY_ID =D.PAY_ID with WHERE C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID).Has anyone seen this sort of behavior before? Why would it beadvantageous to COALESCE on the same field twice?The original and COALESCE'd versions are shown below. Does the factthat this is a nine-table join over large tables have anything to dowith it?Original Query (52 sec)SELECT F.ACCT_ID,I.ENTITY_NAME,A.TNDR_SOURCE_CD,C.PAY_EVENT_ID,C.NON_CIS_NAME,C.NON_CIS_REF_NBR,C.NON_CIS_COMMENT,C.PAY_AMT,D.PAY_SEG_AMT,E.ACCOUNTING_DT,':1',':2',':3',E.FREEZE_OPRID,E.FREEZE_DTTM,E.FT_TYPE_FLGFROM PS_CI_TNDR_CTL A,PS_CI_PAY_TNDR B,PS_CI_PAY C,PS_CI_PAY_SEG D,PS_CI_SA F,PS_CI_SA_TYPE G,PS_CI_ACCT_PER H,PS_CI_PER_NAME I,PS_CW_FT EWHERE A.TNDR_CTL_ID = B.TNDR_CTL_IDAND A.TNDR_SOURCE_CD LIKE 'STK%'AND B.PAY_EVENT_ID = C.PAY_EVENT_IDAND C.PAY_ID = D.PAY_IDAND D.PAY_SEG_ID = E.SIBLING_IDAND E.ACCOUNTING_DT BETWEEN '2003-10-01' AND '2003-10-31'AND E.FT_TYPE_FLG IN ('PS', 'PX')AND NOT EXISTS (SELECT 'X'FROM PS_CW_INTERFACE_ID JWHERE J.PAYOR_ACCT_ID = F.ACCT_ID)AND E.SA_ID = F.SA_IDAND F.SA_TYPE_CD = G.SA_TYPE_CDAND G.DEBT_CL_CD = 'NCIS'AND F.ACCT_ID = H.ACCT_IDAND H.PER_ID = I.PER_IDORDER BY 2Optimized Query (8 sec)SELECT F.ACCT_ID,I.ENTITY_NAME,A.TNDR_SOURCE_CD,C.PAY_EVENT_ID,C.NON_CIS_NAME,C.NON_CIS_REF_NBR,C.NON_CIS_COMMENT,C.PAY_AMT,D.PAY_SEG_AMT,E.ACCOUNTING_DT,':1',':2',':3',E.FREEZE_OPRID,E.FREEZE_DTTM,E.FT_TYPE_FLGFROM PS_CI_TNDR_CTL A,PS_CI_PAY_TNDR B,PS_CI_PAY C,PS_CI_PAY_SEG D,PS_CI_SA F,PS_CI_SA_TYPE G,PS_CI_ACCT_PER H,PS_CI_PER_NAME I,PS_CW_FT EWHERE A.TNDR_CTL_ID = COALESCE(B.TNDR_CTL_ID,B.TNDR_CTL_ID)AND A.TNDR_SOURCE_CD LIKE 'STK%'AND B.PAY_EVENT_ID = COALESCE(C.PAY_EVENT_ID, C.PAY_EVENT_ID)AND C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID)AND D.PAY_SEG_ID = COALESCE(E.SIBLING_ID, E.SIBLING_ID)AND COALESCE(E.ACCOUNTING_DT, E.ACCOUNTING_DT) BETWEEN '2003-10-01'AND '2003-10-31'AND COALESCE(E.FT_TYPE_FLG, E.FT_TYPE_FLG) IN ('PS', 'PX')AND NOT EXISTS (SELECT 'X'FROM PS_CW_INTERFACE_ID JWHERE COALESCE(J.PAYOR_ACCT_ID, J.PAYOR_ACCT_ID) =F.ACCT_ID)AND E.SA_ID = COALESCE(F.SA_ID,F.SA_ID)AND F.SA_TYPE_CD = COALESCE(G.SA_TYPE_CD,G.SA_TYPE_CD)AND G.DEBT_CL_CD = 'NCIS'AND F.ACCT_ID = COALESCE(H.ACCT_ID ,H.ACCT_ID)AND H.PER_ID = COALESCE(I.PER_ID ,I.PER_ID)ORDER BY 2
View 2 Replies
View Related
Sep 23, 2007
Hi All,
It seems to me I am missing some thing while using update with COALESCE...Please help me...
I have two tables called @table1 and @table2
@table1
------------------------
ID | filelis
---------------------
1 |
2 |
3 |
------------------------
@table2
------------------------
ID | file
---------------------
1 | a.txt
1 | b.txt
1 | c.txt
2 | a.exe
2 | b.exe
2 | c.exe
3 | a.alto
3 | b.alto
3 | c.alto
------------------------
I need to get file name from @table2 and update in @table1 with coma separated for each ID
the updated @table1 should be below
------------------------
ID | filelis
---------------------
1 | a.txt, b.txt, c.txt
2 | a.exe, b.exe, c.exe
3 | a.alto, b.alto, c.alto
------------------------
HERE IS THE SCRIPT for above two tables
==================================
declare @table1 table
(
ID INT,
files varchar(MAX)
)
INSERT INTO @table1 (ID,files) values(1,'')
INSERT INTO @table1 (ID,files) values(2,'')
INSERT INTO @table1 (ID,files) values(3,'')
declare @table2 table
(
ID INT,
[file] varchar(255)
)
INSERT INTO @table2 (ID,[file]) values(1,'a.txt')
INSERT INTO @table2 (ID,[file]) values(1,'b.txt')
INSERT INTO @table2 (ID,[file]) values(1,'c.txt')
INSERT INTO @table2 (ID,[file]) values(2,'a.exe')
INSERT INTO @table2 (ID,[file]) values(2,'b.exe')
INSERT INTO @table2 (ID,[file]) values(2,'c.exe')
INSERT INTO @table2 (ID,[file]) values(3,'a.alto')
INSERT INTO @table2 (ID,[file]) values(3,'b.alto')
INSERT INTO @table2 (ID,[file]) values(3,'c.alto')
select * from @table1
select * from @table2
=================================================
I have tried like this but no luck..Thanks in Advance.
update @table1
set files = COALESCE(t2.[file] + ',','') + t2.[file]
from @table1 t1,@table2 t2 where t1.ID = t2.ID
View 5 Replies
View Related
