ORDER BY / SELECT DISTINCT error

[jeffsturgeon2002]

Why am I getting this error message? I have tried many different things and cant seem to fix this one. Any help or insight is greatly appreciated.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

/*Insurance Termination Report*/

SELECT DISTINCT
 	ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,'') AS PatientName,
	PatientId,
	CONVERT(varchar,pv.visit,101)AS DOS,
	pv.TicketNumber, 
	ic.listname,CONVERT(varchar,pi.inscardterminationdate,101) AS InsTermDate,
	df.listname AS Facility,
	CONVERT(varchar,pp.Birthdate,101) AS Birthdate,
	ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS SSN,
	ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	ISNULL(pp.city,'')+ ', '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ],
	IsNull(dbo.formatphone(pp.Phone1,1),'')AS PatPhone1,
	ISNULL(pp.phone1type,'')AS Phone1Type,
	IsNull(dbo.formatphone(pp.Phone2,1),'')AS PatPhone2,
	ISNULL(pp.phone2type,'')AS Phone2Type,  
	ml.description 

FROM 	PatientVisit pv
	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
	LEFT JOIN patientinsurance pi on pp.patientprofileid = pi.patientprofileid and isnull(pi.inactive,0)= 0
	LEFT JOIN insurancecarriers ic on pi.insurancecarriersid = ic.insurancecarriersid
	LEFT JOIN medlists ml on ml.medlistsid = ic.carriertypemid
	LEFT JOIN DoctorFacility df ON pv.facilityId = df.DoctorFacilityId

WHERE 	-- Filter on Patient
	(NULL IS NULL or pp.patientprofileid in (NULL)
	)
	AND  -- Filter on Carrier
	(NULL IS NULL or pi.insurancecarriersid in (NULL)
	)
	AND 
	(NULL IS NULL or pi.inscardterminationdate between NULL AND dateadd(mm,1,convert(datetime,NULL))
	)
	AND 
	(
	(0=0 AND pi.inscardterminationdate IS NOT NULL)
	OR 0=1
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Date
	(
	(1 = 1 AND (pv.Entered >= ISNULL(NULL, '1/1/1900') AND pv.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))OR
	(1 = 2 AND (pvp.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND pvp.DateOfServiceFrom <                                                                					DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
	)

ORDER BY 
	case 1
	when 1 then ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,'')
	when 2 then ml.description
	when 3 then convert(varchar(200), pi.inscardterminationdate,21)
	end
,ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,''),ml.description,convert(varchar(200), pi.inscardterminationdate,21)

Jeff

SELECT * FROM users WHERE clue > 0

 

[jeffsturgeon2002]

 AND

working still?

Jeff

SELECT * FROM users WHERE clue > 0

 

[SQLBill]

Yes

Make sure that you have the box "Process TGML" checked.

-SQLBill

Posting advice: FAQ481-4875

 

[jeffsturgeon2002]

This should help read this better ...

[COLOR=red]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.[/color]

/*Insurance Termination Report*/

SELECT DISTINCT
     ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,'') AS PatientName,
    PatientId,
    CONVERT(varchar,pv.visit,101)AS DOS,
    pv.TicketNumber, 
    ic.listname,CONVERT(varchar,pi.inscardterminationdate,101) AS InsTermDate,
    df.listname AS Facility,
    CONVERT(varchar,pp.Birthdate,101) AS Birthdate,
    ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS SSN,
    ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
    ISNULL(pp.city,'')+ ', '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ],
    IsNull(dbo.formatphone(pp.Phone1,1),'')AS PatPhone1,
    ISNULL(pp.phone1type,'')AS Phone1Type,
    IsNull(dbo.formatphone(pp.Phone2,1),'')AS PatPhone2,
    ISNULL(pp.phone2type,'')AS Phone2Type,  
    ml.description 

FROM     PatientVisit pv
    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
    LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
    LEFT JOIN patientinsurance pi on pp.patientprofileid = pi.patientprofileid and isnull(pi.inactive,0)= 0
    LEFT JOIN insurancecarriers ic on pi.insurancecarriersid = ic.insurancecarriersid
    LEFT JOIN medlists ml on ml.medlistsid = ic.carriertypemid
    LEFT JOIN DoctorFacility df ON pv.facilityId = df.DoctorFacilityId

WHERE     -- Filter on Patient
    (NULL IS NULL or pp.patientprofileid in (NULL)
    )
    AND  -- Filter on Carrier
    (NULL IS NULL or pi.insurancecarriersid in (NULL)
    )
    AND 
    (NULL IS NULL or pi.inscardterminationdate between NULL AND dateadd(mm,1,convert(datetime,NULL))
    )
    AND 
    (
    (0=0 AND pi.inscardterminationdate IS NOT NULL)
    OR 0=1
    )
    AND  --Filter on facility
    (
    (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
    (NULL IS NULL)
    )
    AND  --Filter on Date
    (
    (1 = 1 AND (pv.Entered >= ISNULL(NULL, '1/1/1900') AND pv.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))OR
    (1 = 2 AND (pvp.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND pvp.DateOfServiceFrom <                                                                                    DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
    )

ORDER BY 
    case 1
    when 1 then ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,'')
    when 2 then ml.description
    when 3 then convert(varchar(200), pi.inscardterminationdate,21)
    end
,ISNULL(pp.first,'')+ ' ' + ISNULL(pp.middle,'')+ ' ' + ISNULL(pp.last,''),ml.description,convert(varchar(200), pi.inscardterminationdate,21)

Jeff

SELECT * FROM users WHERE clue > 0

 

[r937]

are you saying you do not understand the error message?

it's telling you that if you want to use DISTINCT, then every expression in the ORDER BY has to be present in the SELECT

i can see one expression in your ORDER BY which isn't in the SELECT -- the CASE

by the way, the CASE doesn't make sense to me -- 1 is always going to equal 1, so why do you have WHEN conditions for 1=2 and 1=3 ?????

another expression in the ORDER BY that isn't in your SELECT is the last one

r937.com | rudy.ca