[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Microsoft, SQL, 2005, Query Help Needed

Please note, the sql is ran dynamically - the syntax in the where & having clauses will look odd to you.

I am getting the following syntax error and cant see where i missed it ... maybe I need more coffee ...

Msg 145, Level 15, State 1, Line 77
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
SET NOCOUNT ON
 
CREATE TABLE #PatientTemp
        (
          [PatientProfileId] [int] NOT NULL,
          [Prefix] [varchar](10) NULL,
          [First] [varchar](30) NULL,
          [Middle] [varchar](30) NULL,
          [Last] [varchar](30) NULL,
          [Suffix] [varchar](20) NULL,
          [Created] [datetime] NOT NULL,
          [SSN] [varchar](9) NULL,
          [Birthdate] [datetime] NULL,
          [Address1] [varchar](50) NULL,
          [Address2] [varchar](50) NULL,
          [City] [varchar](30) NULL,
          [State] [varchar](3) NULL,
          [Zip] [varchar](10) NULL,
          [Phone1] [varchar](15) NULL,
          [Sex] [VARCHAR](10) NULL
        ) 
 
INSERT
        #PatientTemp
        SELECT
                pp.PatientProfileId,
                pp.Prefix,
                pp.First,
                pp.Middle,
                pp.Last,
                pp.Suffix,
                CASE WHEN 2 = 1 THEN PP.Created
                     WHEN 2 = 2 THEN MIN(PV.Visit)
                     ELSE NULL
                END AS Created,
                pp.ssn,
                pp.birthdate,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                pp.Phone1,
                pp.Sex
        FROM
                PatientVisit pv
                INNER JOIN PatientProfile pp ON pp.PatientProfileId = pv.PatientProfileId
        GROUP BY
                pp.PatientProfileId,
                pp.Prefix,
                pp.First,
                pp.Middle,
                pp.Last,
                pp.Suffix,
                pp.Created,
                pp.ssn,
                pp.birthdate,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                pp.Phone1,
                pp.Sex
        HAVING
                ( (( 2 = 1 )
                  AND ( PP.Created >= ISNULL('09/01/2008' , '1/1/1900')
                        AND PP.Created < DATEADD(d , 1 , ISNULL('09/15/2008' , '1/1/3000')) )
	) )
                OR ( ( 2 = 2 )
                     AND ( (MIN(PV.Visit) >= ISNULL('09/01/2008' , '1/1/1900')
                           AND ( MIN(PV.Visit) ) < DATEADD(d , 1 , ISNULL('09/15/2008' , '1/1/3000'))) ) )
        ORDER BY
                pp.First,
                pp.Last
 
SELECT DISTINCT
        #PatientTemp.Last + ', ' + #PatientTemp.First AS [Member Name],
		#PatientTemp.PatientProfileId,
        CASE WHEN 2 = 1 THEN #PatientTemp.Created
             WHEN 2 = 2 THEN MIN(pv.Visit)
             ELSE NULL
        END AS Created,
        #PatientTemp.SSN,
        ISNULL(CONVERT(VARCHAR(20) , #PatientTemp.birthdate , 101) , '') AS [Birthdate],
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        CASE WHEN #PatientTemp.Sex = 'M' THEN '1'
             WHEN #PatientTemp.Sex = 'F' THEN '2'
             ELSE ISNULL(#PatientTemp.Sex , '')
        END AS [Sex],
        ic.Listname,
        ISNULL(pi.InsuredId , '') AS [InsuredID],
        CASE WHEN pi.InsCardEffectiveDate IS NULL THEN ''
             ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
        END AS [PrimaryInsEffDate],
        DATEADD(year , 1 , pi.InsCardEffectiveDate) AS [PrimaryInsTermDate]
        --pv.TicketNumber,
        --pv.Visit,
        --df.ListName AS Doctor,
        --fac.ListName AS Facility
FROM
        PatientVisit pv
        INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
        INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
        LEFT JOIN dbo.DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
        LEFT JOIN dbo.DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        LEFT JOIN PatientVisitResource pvr ON pvr.PatientVisitId = pv.PatientVisitId
        LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
        LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
                                          AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
WHERE
        ( ( NULL IS NOT NULL
            AND pv.DoctorId IN ( NULL ) )
          OR ( NULL IS NULL ) )
        AND ( ( NULL IS NOT NULL
                AND pv.FacilityId IN ( NULL ) )
              OR ( NULL IS NULL ) )
        AND  --Filter on insurance carrier
        ( ( NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL ) )
          OR ( NULL IS NULL ) )
        AND ( ( NULL IS NOT NULL
                AND pvr.ResourceId IN ( NULL ) )
              OR ( NULL IS NULL ) )
GROUP BY
        #PatientTemp.Last + ', ' + #PatientTemp.First,
        #PatientTemp.Created,
        #PatientTemp.PatientProfileId,
        #PatientTemp.Last,
        #PatientTemp.First,
        #PatientTemp.SSN,
        #PatientTemp.Birthdate,
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        #PatientTemp.Sex,
        ic.ListName,
        pi.InsuredId,
        pi.InsCardEffectiveDate
        	--pv.TicketNumber,
        	--pv.Visit,
        	--df.ListName,
       	--fac.ListName
HAVING
        ( (( 2 = 1 )
          AND ( #PatientTemp.Created >= ISNULL('09/01/2008' , '1/1/1900')
                AND #PatientTemp.Created < DATEADD(d , 1 , ISNULL('09/15/2008' , '1/1/3000')) )
	) )
        OR ( ( 2 = 2 )
             AND ( (MIN(pv.Visit) >= ISNULL('09/01/2008' , '1/1/1900')
                   AND ( MIN(pv.Visit) ) < DATEADD(d , 1 , ISNULL('09/15/2008' , '1/1/3000'))) ) )
ORDER BY
		#PatientTemp.Last + ', ' + #PatientTemp.First,
		#PatientTemp.PatientProfileId,
        #PatientTemp.Created,
        #PatientTemp.SSN,
        #PatientTemp.Birthdate,
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        #PatientTemp.Sex,
        ic.ListName,
        pi.InsuredId,
        pi.InsCardEffectiveDate,
       [PrimaryInsTermDate]
 
DROP TABLE #PatientTemp

Open in new window

0
Jeff S
Asked:
Jeff S
  • 2
  • 2
1 Solution
 
chapmandewCommented:
every field that appears in your ORDER BY clause must also appear in your SELECT list since you're using DISTINCT
0
 
Jeff SAuthor Commented:
I got that much .... I have looked it over and over and dont see one missing.
0
 
chapmandewCommented:
looks good.  try taking the distinct out...you don't need it since your grouping.
0
 
Jeff SAuthor Commented:
Doh! Thanks chapmandew.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now