November 2, 2012 at 8:23 am
Jason,
the new columns that you created 'Compare1' and 'Compare2' are not using in where condition so, how the condition is creating to match those columns?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 9:08 am
We arnt interested in matching those in SQL at the moment this task is there to allow the developer to have a look at the data that is being retrieved.
If there is data that is being retrieved and the formats of Compare1 and Compare2 look like they should match then you move them back down into the Where clause and re-run with a Select statement. This tests if the where clause is returning data.
If you get a dataset returned then you can move on to modifying the select to create the update, sure that there will be some data matched.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 9:24 am
Jason,
I am getting the data when i run the query that you sent..
I have added condition in where statement like this:
where Compare1=Compare2
But it gives an error that:
Msg 207, Level 16, State 1, Line 29
Invalid column name 'compare1'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'compare2'.
November 2, 2012 at 9:35 am
kapil190588 (11/2/2012)
Jason,I am getting the data when i run the query that you sent..
I have added condition in where statement like this:
where Compare1=Compare2
But it gives an error that:
Msg 207, Level 16, State 1, Line 29
Invalid column name 'compare1'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'compare2'.
I do feel sorry for you mate... I guess I would be in the same position as you if I would be asked to fix ballet libretto...
Should also say that Jason tries very hard to help you.
You cannot use column aliases in WHERE clause (but you can do it in ORDER BY)
Before trying to match them you need to see what will be in these columns!
So remove your WHERE clause and put the following into the last line:
ORDER BY Compare1, Compare2
Now you should be able to see what kind of strings are formed and will they ever match.
As we can not see results on your screen please post few back!
November 2, 2012 at 9:36 am
Ok, progress, first does it look like Compare1 should equal compare2, can you post some sample data (5 rows) from the select you've just run?
The reason you got that error is becuase Compare1 and Compare2 do not exist until the query is compiled and run, so you cant use them in a where clause.
So we have to change the select statement to be
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,
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
WHERE
convert(NVARCHAR(50),L.locationID)+'-'+convert(NVARCHAR(50),P.Faccount)+'-'+convert(Nvarchar(50),P.Fpatient) = convert(Nvarchar(50),PL.LocationID)+'-'+convert(Nvarchar(50),PL.Chart_No)
As you can see we have copied the string concatinated columns down to the Where clause for Compare1 and Compare2, if you run this and get data returned then you should be able to alter the update statement you first posted using the where clause in the above statement.
BUT you will need to double check the results that these rows are the right ones to be updated.
Unfortunately I'm about to leave the office (4pm GMT) so wont be around until tomorrow hence the select statement above.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 9:40 am
Eugene Elutin (11/2/2012)
I do feel sorry for you mate... I guess I would be in the same position as you if I would be asked to fix ballet libretto...
Should also say that Jason tries very hard to help you.
You cannot use column aliases in WHERE clause (but you can do it in ORDER BY)
Before trying to match them you need to see what will be in these columns!
So remove your WHERE clause and put the following into the last line:
ORDER BY Compare1, Compare2
Now you should be able to see what kind of strings are formed and will they ever match.
As we can not see results on your screen please post few back!
Thanks Eugine, :-D,
Its a friday afternoon so its not too bad, besides not much going on when waiting for 120 million rows of data to load through an ETL process into a DW.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 10:19 am
Compare1 Compare2
1-1-1 1-1-1
1-1-2 1-1-2
I got these rows when I run that query...
November 2, 2012 at 10:19 am
Thanks for your suggestion Eugene
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply