Solved

Microsoft, SQL, 2005, Query Help Needed

Posted on 2008-10-02
4
180 Views
Last Modified: 2010-03-19
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
Comment
Question by:Jeff S
  • 2
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22624524
every field that appears in your ORDER BY clause must also appear in your SELECT list since you're using DISTINCT
0
 
LVL 7

Author Comment

by:Jeff S
ID: 22624536
I got that much .... I have looked it over and over and dont see one missing.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22624560
looks good.  try taking the distinct out...you don't need it since your grouping.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 22624606
Doh! Thanks chapmandew.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
combine an MS SQL string in Idera DM 9 49
SQL 2012 R2 Express report problem 2 83
Restrict result set 1 35
How can I exclude some wording in a like statement? 39 66
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now