Solved

Microsoft, SQL, 2005, Query Help Needed

Posted on 2008-10-02
4
184 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
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 …

832 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