November 2, 2012 at 2:21 am
I have to update records in a table and there is no unique column on basis of whci I can put join
Update PP
Set PP.IsPatient = P.Fpatient,
PP.Gender = P.Fsex,
PP.MartialTypeID = P.Frelation,
PP.Lastvisit = P.Flastvisit,
PP.NxtVisit = P.Fnextvisit,
PP.LastTxNumber = P.Flasttxno,
PP.OldAcct = P.fOldAcct,
pp.ClaimGroup = P.fClaimGroup,
PP.ConsultDate = P.fConsultDate,PP.usedby=P.Fusedby,
PP.IsDeleted = P.Fdelete,
PP.CreatedDate = P.Fwhen,
PP.TrojanId = P.fTrojanId,
PP.PreferMtd =P .fPreferMtd,
PP.AllowText = P.fAllowText,
PP.KodakID = P.fKodak,
PP.TempHold = P.fTempHold,
PP.TempBy = P.fTempBy,
PP.TempWhen = P.fTempWhen,
PP.TOD = P.fTOD
From WCDentalSQL_VAL..Patient P
inner join LocationMaster L on L.code COLLATE Latin1_General_CI_AI = P.Flocation COLLATE Latin1_General_CI_AI
inner join PatientProfile PP ON PP.FirstName COLLATE Latin1_General_CI_AI = P.Ffirstname COLLATE Latin1_General_CI_AI
AND P.Flastname COLLATE Latin1_General_CI_AI = PP.LastName COLLATE Latin1_General_CI_AI
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
where(L.locationID+'-'+P.Faccount+'-'+P.Fpatient)=(PL.LocationID+'-'+PL.Chart_No)
I have a where condition on concatenation of some columns;
where(L.locationID+'-'+P.Faccount+'-'+P.Fpatient)=(PL.LocationID+'-'+PL.Chart_No)
but it is not working..
can anyone help me on this..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 3:12 am
Hi,
what exactly is not working?
November 2, 2012 at 3:19 am
Duplicate post removed.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 3:23 am
The questions is do the strings on either side actually match?
If you turn the Update into a SELECT, and add these as columns
(L.locationID+'-'+P.Faccount+'-'+P.Fpatient)
(PL.LocationID+'-'+PL.Chart_No)
Does (L.locationID+'-'+P.Faccount+'-'+P.Fpatient) =(PL.LocationID+'-'+PL.Chart_No)
You can also simplify this to have
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
AND L.LocationId=PL.LocationId
Where
(P.Faccount+'-'+P.Fpatient)=PL.Chart_No
You might also have a problem with the collations, as I see you are Indicating a common Collation on the JOIN statements, is there a reason for this?
Edit : to read better.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 3:59 am
Arthur,
where(L.locationID+'-'+P.Faccount+'-'+P.Fpatient)=(PL.LocationID+'-'+PL.Chart_No)
is not working.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 4:01 am
pls apply ltrim(rtrim()) function where class columns.....
November 2, 2012 at 4:02 am
Jason,
I replace line with your lines as :
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
AND L.LocationId=PL.LocationId
Where
(P.Faccount+'-'+P.Fpatient)=PL.Chart_No
But I am getting error as I was getting before-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '0-1 ' to data type int.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 4:25 am
Ok, this indicates that PL.ChartNo is a numeric is that correct?
In which case why would this LocationId+'-'+Chart_no (Possible "ASD-12345") ever equal Location_Id + '-' + P.Faccount+'-'+P.FPatient which has a possible combination of "ASD-546-123".
I would start with Writing the update as a Select and dropping the where clause adding in (L.locationID+'-'+P.Faccount+'-'+P.Fpatient) and (PL.LocationID+'-'+PL.Chart_No) as additional columns and checking if they actually match, in terms of format etc.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 4:38 am
Yes, PL.ChartNo is numeric...
LocationId+'-'+Chart_no will comes as 1-1 not ASD-12
Also,
how can I update as a Select and dropping the where clause adding in (L.locationID+'-'+P.Faccount+'-'+P.Fpatient) and (PL.LocationID+'-'+PL.Chart_No) as additional columns
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 4:45 am
Basically just remove the Update and SET and replace them with a Select
SELECT
Gender = P.Fsex,
MartialTypeID = P.Frelation,
Lastvisit = P.Flastvisit,
NxtVisit = P.Fnextvisit,
LastTxNumber = P.Flasttxno,
OldAcct = P.fOldAcct,
ClaimGroup = P.fClaimGroup,
ConsultDate = P.fConsultDate,PP.usedby=P.Fusedby,
IsDeleted = P.Fdelete,
CreatedDate = P.Fwhen,
TrojanId = P.fTrojanId,
PreferMtd =P .fPreferMtd,
AllowText = P.fAllowText,
KodakID = P.fKodak,
TempHold = P.fTempHold,
TempBy = P.fTempBy,
TempWhen = P.fTempWhen,
TOD = P.fTOD,
--New cols
(L.locationID+'-'+P.Faccount+'-'+P.Fpatient),
(PL.LocationID+'-'+PL.Chart_No)
From WCDentalSQL_VAL..Patient P
inner join LocationMaster L on L.code COLLATE Latin1_General_CI_AI = P.Flocation COLLATE Latin1_General_CI_AI
inner join PatientProfile PP ON PP.FirstName COLLATE Latin1_General_CI_AI = P.Ffirstname COLLATE Latin1_General_CI_AI
AND P.Flastname COLLATE Latin1_General_CI_AI = PP.LastName COLLATE Latin1_General_CI_AI
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 4:52 am
kapil_kk (11/2/2012)
Yes, PL.ChartNo is numeric...LocationId+'-'+Chart_no will comes as 1-1 not ASD-12
Also,
how can I update as a Select and dropping the where clause adding in (L.locationID+'-'+P.Faccount+'-'+P.Fpatient) and (PL.LocationID+'-'+PL.Chart_No) as additional columns
we dont have any idea what your data looks like, or its datatype.
But consdier this will a string in the format 'nnn-nnn-nnn' equal a string in the format 'nnn-nnn' where nnn are numbers, eg does '123-123-123' = '123-123'
Which is effectively what you are doing in the current where clause.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 6:13 am
Jason,
I have changed my query as:
SELECT
(
PP.IsPatient = P.Fpatient,
PP.Gender = P.Fsex,
PP.MartialTypeID = P.Frelation,
PP.Lastvisit = P.Flastvisit,
PP.NxtVisit = P.Fnextvisit,
PP.LastTxNumber = P.Flasttxno,
PP.OldAcct = P.fOldAcct,
pp.ClaimGroup = P.fClaimGroup,
PP.ConsultDate = P.fConsultDate,PP.usedby=P.Fusedby,
PP.IsDeleted = P.Fdelete,
PP.CreatedDate = P.Fwhen,
PP.TrojanId = P.fTrojanId,
PP.PreferMtd =P .fPreferMtd,
PP.AllowText = P.fAllowText,
PP.KodakID = P.fKodak,
PP.TempHold = P.fTempHold,
PP.TempBy = P.fTempBy,
PP.TempWhen = P.fTempWhen,
PP.TOD = P.fTOD,
(L.locationID+'-'+P.Faccount+'-'+P.Fpatient)as Compare1,
(PL.LocationID+'-'+PL.Chart_No) as Compare2)
From WCDentalSQL_VAL..Patient P
inner join LocationMaster L on L.code COLLATE Latin1_General_CI_AI = P.Flocation COLLATE Latin1_General_CI_AI
inner join PatientProfile PP ON PP.FirstName COLLATE Latin1_General_CI_AI = P.Ffirstname COLLATE Latin1_General_CI_AI
AND P.Flastname COLLATE Latin1_General_CI_AI = PP.LastName COLLATE Latin1_General_CI_AI
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
where Compare1=Comapre2
but still I am getting error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 7:23 am
Run this select without any alterations.
SELECT
Gender = P.Fsex,
MartialTypeID = P.Frelation,
Lastvisit = P.Flastvisit,
NxtVisit = P.Fnextvisit,
LastTxNumber = P.Flasttxno,
OldAcct = P.fOldAcct,
ClaimGroup = P.fClaimGroup,
ConsultDate = P.fConsultDate,PP.usedby=P.Fusedby,
IsDeleted = P.Fdelete,
CreatedDate = P.Fwhen,
TrojanId = P.fTrojanId,
PreferMtd =P .fPreferMtd,
AllowText = P.fAllowText,
KodakID = P.fKodak,
TempHold = P.fTempHold,
TempBy = P.fTempBy,
TempWhen = P.fTempWhen,
TOD = P.fTOD,
--New cols
(L.locationID+'-'+P.Faccount+'-'+P.Fpatient) Compare1,
(PL.LocationID+'-'+PL.Chart_No) Compare2
From WCDentalSQL_VAL..Patient P
inner join LocationMaster L on L.code COLLATE Latin1_General_CI_AI = P.Flocation COLLATE Latin1_General_CI_AI
inner join PatientProfile PP ON PP.FirstName COLLATE Latin1_General_CI_AI = P.Ffirstname COLLATE Latin1_General_CI_AI
AND P.Flastname COLLATE Latin1_General_CI_AI = PP.LastName COLLATE Latin1_General_CI_AI
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
What we want to do is find out why the data set isnt working, so we need to compare the new columns with each other to see what the issue is and this can really only be done by a manual check.
as the aim of this is to see what the contents of the column Compare1 and Compare2 are.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 7:40 am
Jason,
I run your without making any changes and get the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1-1 ' to data type int.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 7:55 am
Ok, so I've now put in an impicit cast on each column of Compare1 and Compare2, there was also an error on the Useby column which I'd missed.
SELECT
Gender = P.Fsex,
MartialTypeID = P.Frelation,
Lastvisit = P.Flastvisit,
NxtVisit = P.Fnextvisit,
LastTxNumber = P.Flasttxno,
OldAcct = P.fOldAcct,
ClaimGroup = P.fClaimGroup,
ConsultDate = P.fConsultDate,
usedby=P.Fusedby,
IsDeleted = P.Fdelete,
CreatedDate = P.Fwhen,
TrojanId = P.fTrojanId,
PreferMtd =P .fPreferMtd,
AllowText = P.fAllowText,
KodakID = P.fKodak,
TempHold = P.fTempHold,
TempBy = P.fTempBy,
TempWhen = P.fTempWhen,
TOD = P.fTOD,
--New cols
convert(NVARCHAR(50),L.locationID)+'-'+convert(NVARCHAR(50),P.Faccount)+'-'+convert(Nvarchar(50),P.Fpatient) Compare1,
convert(Nvarchar(50),PL.LocationID)+'-'+convert(Nvarchar(50),PL.Chart_No) Compare2
From WCDentalSQL_VAL..Patient P
inner join LocationMaster L on L.code COLLATE Latin1_General_CI_AI = P.Flocation COLLATE Latin1_General_CI_AI
inner join PatientProfile PP ON PP.FirstName COLLATE Latin1_General_CI_AI = P.Ffirstname COLLATE Latin1_General_CI_AI
AND P.Flastname COLLATE Latin1_General_CI_AI = PP.LastName COLLATE Latin1_General_CI_AI
inner join PatientLocDtl PL ON PL.PatientID = PP.PatientID
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply