Can anyone explain why this isn't populating the two desired columns?

Question

Can anyone explain why this isn't populating the two desired columns?

chocthree

Ten Centuries

Points: 1276
More actions
June 7, 2018 at 6:00 am

#369404

Hi,
Please see the below query. When executing this query I find two derived columns [Candidate First Name] and [Candidate Second Name] are present, however both columns are not populated. I expect it to be a simple fix. Can anyone what I am doing wrong?
Thanks.
SELECT TOP 10
rc.EmailAddress AS [Email Address],
cnd.Name AS [Candidate Last : Candidate First],
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN LEFT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE LEFT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate First Name],
--------------------------
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN RIGHT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE RIGHT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate Last Name],
ce.[CanSid] AS [Candidate Number],
ck.UCI AS [Unique Identifier],
cnd.DOB,
q.ShortName AS [Exam],
q.Name AS [Exam Level],
NULL AS KAD,
cnt.CnuId AS [Centre No],
cnt.Name AS [Centre Name],
cnt.Active AS [Centre Status],
NULL AS [Centre's Country],
ce.ResultInfo AS [ResultInfo]
FROM [ESOLCR].[dbo].[CandidateEntries] ce
JOIN [ESOLCR].dbo.Candidates cnd ON ce.CanSid = cnd.CanSid
JOIN [ESOLCR].dbo.CandidateKeys ck ON ce.CanSid = ck.CanSid
JOIN [ESOLCR].dbo.RegisteredCandidate rc ON ck.UCI = rc.UCI
JOIN [ESOLCR].dbo.Centres cnt ON ce.CnuId = cnt.CnuId
JOIN [ESOLCR].dbo.Qualifications q ON ce.QuaSid = q.QuaSid

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

chocthree - Thursday, June 7, 2018 6:00 AM
Hi,
Please see the below query. When executing this query I find two derived columns [Candidate First Name] and [Candidate Second Name] are present, however both columns are not populated. I expect it to be a simple fix. Can anyone what I am doing wrong?
Thanks.
SELECT TOP 10
rc.EmailAddress AS [Email Address],
cnd.Name AS [Candidate Last : Candidate First],
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN LEFT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE LEFT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate First Name],
--------------------------
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN RIGHT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE RIGHT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate Last Name],
ce.[CanSid] AS [Candidate Number],
ck.UCI AS [Unique Identifier],
cnd.DOB,
q.ShortName AS [Exam],
q.Name AS [Exam Level],
NULL AS KAD,
cnt.CnuId AS [Centre No],
cnt.Name AS [Centre Name],
cnt.Active AS [Centre Status],
NULL AS [Centre's Country],
ce.ResultInfo AS [ResultInfo]
FROM [ESOLCR].[dbo].[CandidateEntries] ce
JOIN [ESOLCR].dbo.Candidates cnd ON ce.CanSid = cnd.CanSid
JOIN [ESOLCR].dbo.CandidateKeys ck ON ce.CanSid = ck.CanSid
JOIN [ESOLCR].dbo.RegisteredCandidate rc ON ck.UCI = rc.UCI
JOIN [ESOLCR].dbo.Centres cnt ON ce.CnuId = cnt.CnuId
JOIN [ESOLCR].dbo.Qualifications q ON ce.QuaSid = q.QuaSid

Here is a reformatted version. Please use code tags in future when you post T-SQL to save others' eyes!
SELECT TOP 10 [Email Address] = rc.EmailAddress , [Candidate Last : Candidate First] = cnd.Name , [Candidate First Name] = CASE WHEN CHARINDEX(':', cnd.Name) <> 0 THEN LEFT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1)) ELSE LEFT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name)))) END , [Candidate Last Name] = CASE WHEN CHARINDEX(':', cnd.Name) <> 0 THEN RIGHT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1)) ELSE RIGHT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name)))) END , [Candidate Number] = ce.CanSid , [Unique Identifier] = ck.UCI , cnd.DOB , Exam = q.ShortName , [Exam Level] = q.Name , KAD = NULL , [Centre No] = cnt.CnuId , [Centre Name] = cnt.Name , [Centre Status] = cnt.Active , [Centre's Country] = NULL , ResultInfo = ce.ResultInfo FROM ESOLCR.dbo.CandidateEntries ce JOIN ESOLCR.dbo.Candidates cnd ON ce.CanSid = cnd.CanSid JOIN ESOLCR.dbo.CandidateKeys ck ON ce.CanSid = ck.CanSid JOIN ESOLCR.dbo.RegisteredCandidate rc ON ck.UCI = rc.UCI JOIN ESOLCR.dbo.Centres cnt ON ce.CnuId = cnt.CnuId JOIN ESOLCR.dbo.Qualifications q ON ce.QuaSid = q.QuaSid;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I would check these expressions. You might be confused on what you're doing.
LEFT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1))
LEFT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name))))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 3

chocthree - Thursday, June 7, 2018 6:00 AM
Hi,
Please see the below query. When executing this query I find two derived columns [Candidate First Name] and [Candidate Second Name] are present, however both columns are not populated. I expect it to be a simple fix. Can anyone what I am doing wrong?
Thanks.
SELECT TOP 10
rc.EmailAddress AS [Email Address],
cnd.Name AS [Candidate Last : Candidate First],
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN LEFT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE LEFT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate First Name],
--------------------------
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN RIGHT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE RIGHT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate Last Name],
ce.[CanSid] AS [Candidate Number],
ck.UCI AS [Unique Identifier],
cnd.DOB,
q.ShortName AS [Exam],
q.Name AS [Exam Level],
NULL AS KAD,
cnt.CnuId AS [Centre No],
cnt.Name AS [Centre Name],
cnt.Active AS [Centre Status],
NULL AS [Centre's Country],
ce.ResultInfo AS [ResultInfo]
FROM [ESOLCR].[dbo].[CandidateEntries] ce
JOIN [ESOLCR].dbo.Candidates cnd ON ce.CanSid = cnd.CanSid
JOIN [ESOLCR].dbo.CandidateKeys ck ON ce.CanSid = ck.CanSid
JOIN [ESOLCR].dbo.RegisteredCandidate rc ON ck.UCI = rc.UCI
JOIN [ESOLCR].dbo.Centres cnt ON ce.CnuId = cnt.CnuId
JOIN [ESOLCR].dbo.Qualifications q ON ce.QuaSid = q.QuaSid

See the below illustrations for better understanding

CREATE TABLE namespace ( names VARCHAR(100) );

INSERT INTO namespace VALUES ('saravana kumar:muniyan');

INSERT INTO namespace VALUES ('nirmal:anbu');

SELECT CASE WHEN Charindex(':', names) <> 0 THEN LEFT(cnd.names, Len( LEFT(cnd.names, Charindex(':', cnd.names) - 1))) END AS firstname, CASE WHEN Charindex(':', names) <> 0 THEN RIGHT(cnd.names, Len(RIGHT(cnd.names, Len(cnd.names))) - Len(RIGHT(cnd.names, Charindex(':', cnd.names)))) END AS lastname FROM namespace cnd

Saravanan

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 4

Luis Cazares - Thursday, June 7, 2018 7:00 AM
I would check these expressions. You might be confused on what you're doing.
LEFT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1))
LEFT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name))))

Hi Luis ,
Your query fetches zero results. Can you kindly check?

Saravanan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

saravanatn - Thursday, June 7, 2018 7:26 AM
Luis Cazares - Thursday, June 7, 2018 7:00 AM
I would check these expressions. You might be confused on what you're doing.
LEFT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1))
LEFT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name))))
Hi Luis ,
Your query fetches zero results. Can you kindly check?

Luis didn't post a query - he posted a part of yours, suggesting that it may be the part which is causing your problem.
If you post up a script comprising CREATE TABLE and a few INSERTs to populate it, you will get a quicker, better answer.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chocthree Ten Centuries Points: 1276 More actions · Answer 6

chocthree

Ten Centuries

Points: 1276

June 7, 2018 at 7:39 am

#1993217

..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

ChrisM@Work - Thursday, June 7, 2018 7:30 AM
saravanatn - Thursday, June 7, 2018 7:26 AM
Luis Cazares - Thursday, June 7, 2018 7:00 AM
I would check these expressions. You might be confused on what you're doing.
LEFT(cnd.Name, CHARINDEX(':', LEN(cnd.Name) - 1))
LEFT(cnd.Name, CHARINDEX(' ', REVERSE(LEN(cnd.Name))))
Hi Luis ,
Your query fetches zero results. Can you kindly check?
Luis didn't post a query - he posted a part of yours, suggesting that it may be the part which is causing your problem.
If you post up a script comprising CREATE TABLE and a few INSERTs to populate it, you will get a quicker, better answer.
Edit: Haha! Oops. Still stands though 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chocthree Ten Centuries Points: 1276 More actions · Answer 8

Thanks all for your replies.
saravanatn - your post was very helpful. I understood it and applied the logic to cater for the space delimiters too. All works now.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

chocthree - Thursday, June 7, 2018 6:00 AM
Hi,
Please see the below query. When executing this query I find two derived columns [Candidate First Name] and [Candidate Second Name] are present, however both columns are not populated. I expect it to be a simple fix. Can anyone what I am doing wrong?
Thanks.
SELECT TOP 10
rc.EmailAddress AS [Email Address],
cnd.Name AS [Candidate Last : Candidate First],
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN LEFT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE LEFT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate First Name],
--------------------------
    CASE WHEN CHARINDEX( ':', cnd.Name ) <> 0
        THEN RIGHT( cnd.Name, CHARINDEX( ':', LEN(cnd.Name) - 1) )
        ELSE RIGHT( cnd.Name, CHARINDEX( ' ', REVERSE( LEN(cnd.Name) ) ) )
    END AS [Candidate Last Name],
ce.[CanSid] AS [Candidate Number],
ck.UCI AS [Unique Identifier],
cnd.DOB,
q.ShortName AS [Exam],
q.Name AS [Exam Level],
NULL AS KAD,
cnt.CnuId AS [Centre No],
cnt.Name AS [Centre Name],
cnt.Active AS [Centre Status],
NULL AS [Centre's Country],
ce.ResultInfo AS [ResultInfo]
FROM [ESOLCR].[dbo].[CandidateEntries] ce
JOIN [ESOLCR].dbo.Candidates cnd ON ce.CanSid = cnd.CanSid
JOIN [ESOLCR].dbo.CandidateKeys ck ON ce.CanSid = ck.CanSid
JOIN [ESOLCR].dbo.RegisteredCandidate rc ON ck.UCI = rc.UCI
JOIN [ESOLCR].dbo.Centres cnt ON ce.CnuId = cnt.CnuId
JOIN [ESOLCR].dbo.Qualifications q ON ce.QuaSid = q.QuaSid

The problem here is clear. It's this portion of your code (it appears twice, by the way):
REVERSE( LEN(cnd.Name) )
What that should probably be is:
LEN(REVERSE(cnd.Name))

The REVERSE function takes a string and reverses the order of characters present. If you pass in an integer value; in this case the LEN of cnd.Name; your going to get a character string that is the digits of the length of cnd.Name in reverse order, and I'm pretty sure that's NOT what you want.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)