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

  • 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

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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
  • 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

  • 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

  • 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

  • ..

  • 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

  • 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.

  • 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)

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

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