October 31, 2018 at 10:23 am
Thank you,it is working
also trying to this select statement to email add this code:
WHEN a. [Person Email] is NULL
THEN (SELECT Email FROM [dbo].[PersonEmail] INNER JOIN dbo.vw_Person ON [dbo].[PersonEmail].[name]= dbo.vwPerson.[Person Name] WHERE vw_Person_.[Person Email] IS null )
get errorI
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
October 31, 2018 at 10:28 am
It's just saying that the PersonEmail column has a different collation from the Person Name column. Use a COLLATE clause on one of them to match it to the collation of the other, or use a COLLATE DATABASE DEFAULT clause on both of them.
John
November 1, 2018 at 8:49 am
Thank you,it works only if I run it outside of my statement,but if I run the full code
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
November 1, 2018 at 9:02 am
When you ran it outside your statement you should have seen what the problem is. The subquery returns more than one value of Email. Which of those values do you want it to use? Try using a TOP 1 clause to restrict the subquery result to a single value.
John
November 1, 2018 at 9:18 am
if do select top 1, I get 1st email for person correct and the rest get email of that person, I need people have their emails mapped correctly.
November 1, 2018 at 9:23 am
Krasavita - Thursday, November 1, 2018 9:18 AMif do select top 1, I get 1st email for person correct and the rest get email of that person, I need people have their emails mapped correctly.
Post your code, nicely formatted, using the if code blocks to keep the code formatted.
November 1, 2018 at 9:36 am
WHEN a.[Person Email] IS NULL AND a.[person name] IS NOT null THEN
( SELECT TOP 1 Email COLLATE DATABASE_DEFAULT
FROM [dbo].[vw_PersonEmail]
INNER JOIN dbo.R1_Person
ON [dbo].[vw_PersonEmail].[name] COLLATE DATABASE_DEFAULT = dbo.R1_Person.[Person Name]
WHERE R1_Person_10032018.[Person Email] IS NULL)
November 1, 2018 at 9:39 am
Krasavita - Thursday, November 1, 2018 9:36 AMWHEN a.[Person Email] IS NULL AND a.[person name] IS NOT null THEN
( SELECT TOP 1 Email COLLATE DATABASE_DEFAULT
FROM [dbo].[vw_PersonEmail]
INNER JOIN dbo.R1_Person
ON [dbo].[vw_PersonEmail].[name] COLLATE DATABASE_DEFAULT = dbo.R1_Person.[Person Name]
WHERE R1_Person_10032018.[Person Email] IS NULL)
All the code. Also, format it and use the IF CODE blocks to keep the code formatted.
November 1, 2018 at 9:57 am
You don't even have consistent table names in there. Sometimes you have 10032018 in the table name and sometimes you don't. If you don't care about your code, nobody else is going to. Never mind - try this. The correlated subquery probably isn't the best from a performance point of view, but it may just get you over the line. I recommend you get someone in to look at this, since you seem to be struggling, and the database design appears to be far from optimal.
WHEN a.[Person Email] IS NULL AND a.[person name] IS NOT NULL THEN (
SELECT TOP 1 Email COLLATE DATABASE_DEFAULT
FROM dbo.vw_PersonEmail v
WHERE a.[Person Name] = v.name COLLATE DATABASE_DEFAULT
)
John
Edit: if there's a one-to-one mapping between "a" and vw_PersonEmail, then you don't need the TOP 1. If you do need to keep it, however, you need an ORDER BY clause in your subquery so that you know what you're sorting on when you choose your top value.
November 1, 2018 at 10:24 am
Thank you for your help
November 9, 2018 at 11:30 am
Hello
need to fix data for Persan name can you please help
| DSPN | Region | Country | PersonName | Person Email_old | Country Program |
| Bad Data | |||||
| 67946740127 | CARO | japan | andgela Toluhi karagi | andgela.toluhi@jus.org karagi.akinmade@jus.org | japan |
| good data | |||||
| 67946740127 | CARO | japan | andgela Toluhi | andgela.toluhi@jus.org | japan |
| 67946740127 | CARO | japan | karagi | karagi.akinmade@jus.org | japan |
| Bad Data | |||||
| 67506740142 | LACRO | Haiti | andgela Toluhi Olufunbi Olaogun | andgela.toluhi@jus.org olufunbi.olaogun@jus.org | japan |
| Good data | |||||
| 67506740142 | LACRO | Haiti | andgela Toluhi | andgela.toluhi@jus.org | japan |
| 67506740142 | LACRO | Haiti | Olufunbi Olaogun | olufunbi.olaogun@jus.org | japan |
| Bad data | |||||
| 15506740131 | CARO | japan | andgela Toluhi Sasha Usman Adia, Oro-yevge | andgela.toluhi@jus.org Sasha.usman@jus.org oro-yevge.adia@jus.org | japan |
| Good data | |||||
| 85036740131 | CARO | japan | andgela Toluhi | andgela.toluhi@jus.org s | japan |
| 85036740131 | CARO | japan | Sasha Usman Adia | Sasha.usman@jus.org | japan |
| 85036740131 | LACRO | Haiti | Oro-yevge | oro-yevge.adia@jus.org | japan |
November 9, 2018 at 11:49 am
Krasavita - Friday, November 9, 2018 11:30 AMHello
need to fix data for Persan name can you please help
DSPN Region Country PersonName Person Email_old Country Program Bad Data 67946740127 CARO japan andgela Toluhi karagi andgela.toluhi@jus.org karagi.akinmade@jus.org japan good data 67946740127 CARO japan andgela Toluhi andgela.toluhi@jus.org japan 67946740127 CARO japan karagi karagi.akinmade@jus.org japan Bad Data 67506740142 LACRO Haiti andgela Toluhi Olufunbi Olaogun andgela.toluhi@jus.org olufunbi.olaogun@jus.org japan Good data 67506740142 LACRO Haiti andgela Toluhi andgela.toluhi@jus.org japan 67506740142 LACRO Haiti Olufunbi Olaogun olufunbi.olaogun@jus.org japan Bad data 15506740131 CARO japan andgela Toluhi Sasha Usman Adia, Oro-yevge andgela.toluhi@jus.org Sasha.usman@jus.org oro-yevge.adia@jus.org japan Good data 85036740131 CARO japan andgela Toluhi andgela.toluhi@jus.org s japan 85036740131 CARO japan Sasha Usman Adia Sasha.usman@jus.org japan 85036740131 LACRO Haiti Oro-yevge oro-yevge.adia@jus.org japan
First you only wanted the first email address, now you want to match email addresses to names? I give up. You don't even seem able to follow simple guidelines for post questions even when you are pointed directly to an article that would help you do so. You don't give the complete picture of what you are trying to accomplish, just little pieces so it is like pulling hens teeth to figure out what it is you are you trying to accomplish.
November 9, 2018 at 12:01 pm
You haven't given enough information for anyone to come up with a reasonable solution. In fact, it looks like your "Good data" isn't so good. Based on the emails, I suspect that "Sasha Usman Adia" should, in fact, just be "Sasha Usman" and that Oro-yevge should be Oro-yevge Adia. Also, there is no way that anybody can get LACRO HAITI from your bad data for oro-yevge.adia@jus.org.
PS: I suspect that there isn't going to be any way to automate this cleanup. You may just have to do it manually unless you can find it in a better form somewhere upstream of this data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply