Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Last Four Digits Search

    Hi guys I have a problem, I wanted to give the users the ability to enter the last four digits of a Social security number and find everyone with those four digits. The problem is they only find one, now when I do a search in the database myself (manually) I find more then one, what do I do or what am I over looking??


    ALTER FUNCTION [dbo].[SSLookupLastFour]
    (@ENTER_SSNLastFour char(4))
    RETURNS TABLE
    AS
    RETURN SELECT dbo.EmployeeGamingLicense.TM#, dbo.EmployeeGamingLicense.LASTNAME, dbo.EmployeeGamingLicense.FIRSTNAME, dbo.EmployeeGamingLicense.[SSN#],
    dbo.EmployeeDocuments.EmployeeDocs, dbo.EmployeeGamingLicense.CAT, dbo.EmployeeGamingLicense.JOBTITLE,
    dbo.EmployeeGamingLicense.FINALSUITDONE
    FROM dbo.EmployeeGamingLicense LEFT OUTER JOIN
    dbo.EmployeeDocuments ON dbo.EmployeeGamingLicense.TM# = dbo.EmployeeGamingLicense.TM#
    WHERE dbo.EmployeeGamingLicense.SSNLastFour = @ENTER_SSNLastFour

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    just a guess here. what does this return...
    Code:
    SELECT DISTINCT dbo.EmployeeGamingLicense.SSNLastFour 
    FROM dbo.EmployeeGamingLicense
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If other values for the SSNLastFour correspond to unmatched records in EmployeeDocuments, - then the result of 1 row is correct.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    or it could be your front end code that is showing the first record only ignoring the rest...

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank you Thrasy, that works, that returns all that have the same last four digits
    So do I just incorporate that into a stored procedure so the users can search??

    woohoo I got it
    Code:
     
    CREATE PROCEDURE SrchSSNLstFour
    @Enter_SSNLastFour varchar(4)
    AS
    SELECT DISTINCT LASTNAME, FIRSTNAME, TM#, SSN#, JOBTITLE
    FROM dbo.EmployeeGamingLicense
    WHERE (SSNLastFour = @Enter_SSNLastFour)

    YOUR THE BEST AND THRASY, AND YES I SHOULD HAVE LOOKED ON BOL WHERE IT WOULD HAVE BEEN CLEAR
    I SHOULD BE ASHAMED
    Last edited by desireemm; 08-22-08 at 12:32.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    no. it looks like something is wrong with your data and your field was improperly populated.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus
    no. it looks like something is wrong with your data and your field was improperly populated.

    Nevermind Thrasy scatch what I just said sorry
    Last edited by desireemm; 08-22-08 at 13:18.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am confused.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus
    no. it looks like something is wrong with your data and your field was improperly populated.

    Hi Thrasy could explain a little further?? what do you mean??

    Hope your having a good day

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    This a long shot but I bet you have the same 4 numbers in every record you have in the Last4SSN field of your table. That query I sent you earlier. How many records does it return?

    My last good day on record was sometime in 1997.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well if I type in a number like 1099 it will give me everyone with that number in this case there was only 2 records

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus
    This a long shot but I bet you have the same 4 numbers in every record you have in the Last4SSN field of your table. That query I sent you earlier. How many records does it return?

    My last good day on record was sometime in 1997.

    is that what your asking me or am I misunderstanding you??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •