Select Statement

1

Hi,

I have the following query;
ORIGINAL CODE -
Query A


SELECT     TOP (100) PERCENT PracID, CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID
FROM         (SELECT     PracID,  row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM        EMISPatientExtraction WITH (tablock)) t
ORDER BY PracID, PatID

Results

PracID          PatientID

20001           120001
20001           220001 
                      ..
                      ..
20001            24520001
20002            120002
20002            220002
                       ..
                       ..
20002            35620002
20003            NULL 
20003            NULL

Now, I have another query (qryMaxValue) that reads the MAX PatID i.e.,
Query B

SELECT     TOP (100) PERCENT (MAX(dbo.EmisPatient1.PatientID) / 100000 + 1) * 1 AS PatID, dbo.EmisCreatePatList1.PracID
FROM         dbo.EmisCreatePatList1 LEFT OUTER JOIN
                      dbo.EmisPatient1 ON dbo.EmisCreatePatList1.PracID = dbo.EmisPatient1.PracID
GROUP BY dbo.EmisCreatePatList1.PracID
ORDER BY dbo.EmisCreatePatList1.PracID

that gives the results;

PracID          PatID

20001            245
20002            356
20003            NULL

Query C

SELECT     TOP (100) PERCENT PracID, PersonID, PatientGuidDigest
FROM         (SELECT     PracID, PersonID, PatientGuidDigest, row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM         dbo.EmisPatientExtraction_RtnPatID_NULLs WITH (tablock)) t
ORDER BY PracID, PatID

Now, I want to join these 2 queries (Query B & Query C) so that the result will be for the NEXT PatientIDs in the query below;

PracID          PatID

20001            24620001
20001            24720001
20001            24820001 ......
20002            35720002
20002            35820002....
20003            120003
20003            220003....

Thank you so much

2

I tried this;

SELECT     TOP (100) PERCENT PracID, CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID, PersonID, PatientGuidDigest
FROM         (SELECT     PracID, PersonID, PatientGuidDigest, row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM         dbo.EMISPatientExtraction_RtnPatID_NULLs LEFT OUTER JOIN
                      dbo.QryMaxvalue ON dbo.EMISPatientExtraction_RtnPatID_NULLs.PracID = dbo.QryMaxValue.PracID) t
ORDER BY PracID, PatID

I receive the following error ;

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'PracID'.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'pracid'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'pracid'.

Any help please..

Many thanks

3

You need to put the ALIAS for the table in front of the Column Name in (both of) the ORDER BY statements

They probably are NOT ambiguous, but because they are "made visible" more than once within your code then the ORDER BY requires an ALIAS.

A more simple example would be

SELECT SomeColumn, *
FROM MyTable AS T
ORDER BY SomeColumn

Clearly SomeColumn only occurs once, and to you and me is UNambiguous :slightly_smiling: but because I have SomeColumn in the SELECT list and I have "*" (which will include it, a second time, in the Select list) then SQL doesn't know which of those two is the one I mean to have in my ORDER BY. They will, of course, both have the same value ... SQL just isn't smart enough to figure that out without you using the Table ALIAS

...
ORDER BY T.SomeColumn
4

Ok - will this solve my primarily problem - please shade some light on the query in (1) ..

Many thanks

5

Any help please!!