NULL Values

Transact-SQL
1

Hi,

I have the following script which works fine but I want it to consider records only when PatientID is NULL from dbo.EmisPatientExtraction_RtnPatID..

SELECT     TOP (100) PERCENT PracID, CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID, PersonID, PatientGuidDigest, BirthYear, Gender, DeathDate, 
                      Ethnicity, EthnicityCode, PatientType, Registered, RegistrationStartDate, RegistrationEndDate, PatientStatus, TruncatedPostcode
FROM         (SELECT     PracID, PersonID, PatientGuidDigest, BirthYear, Gender, DeathDate, Ethnicity, EthnicityCode, PatientType, Registered, RegistrationStartDate, 
                                              RegistrationEndDate, PatientStatus, TruncatedPostcode, row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM         dbo.EmisPatientExtraction_RtnPatID WITH (tablock)) t
ORDER BY PracID, PatID

Thanks

2
  1. Since you're not using Row_Number, you don't need a subquery
  2. Add WHERE PatientID is NULL
3

I receive the error message - Invalid Column Name 'PatientID'

SELECT     TOP (100) PERCENT PracID, CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID, PersonID, PatientGuidDigest, BirthYear, Gender, DeathDate, 
                      Ethnicity, EthnicityCode, PatientType, Registered, RegistrationStartDate, RegistrationEndDate, PatientStatus, TruncatedPostcode
FROM         (SELECT     PracID, PersonID, PatientGuidDigest, BirthYear, Gender, DeathDate, Ethnicity, EthnicityCode, PatientType, Registered, RegistrationStartDate, 
                                              RegistrationEndDate, PatientStatus, TruncatedPostcode, row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM         dbo.EmisPatientExtraction_RtnPatID WITH (tablock)) t
WHERE PatientID IS NULL
ORDER BY PracID, PatID
4

Hey! You called it PatientID. If you don't have a PatientID column ( cannot tell since you didn't post your table schema), then use the correct column name!

5

PatientID is your column alias. You can't reference it in the WHERE clause. Use the original column name PracID instead

6

And get rid of that TOP (100) PERCENT - it is useless...and based on how the code is formatted I would recommend that you don't use the query designer and move to a new query window and write the query yourself.

7

I would be willing to bet that the TOP 100 PERCENT is their workaround for the limitation of views on SQL Server to order rows in the view definition. I have seen it done too many times.

8

highly likely