Solved

Microsoft, SQL, 2005, Query Help Needed

Posted on 2008-10-02
4
178 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
Comment Utility
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
Comment Utility
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
Comment Utility
looks good.  try taking the distinct out...you don't need it since your grouping.
0
 
LVL 7

Author Comment

by:Jeff S
Comment Utility
Doh! Thanks chapmandew.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

743 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

15 Experts available now in Live!

Get 1:1 Help Now