Solved

Microsoft, SQL, 2005, Query Help Needed

Posted on 2008-10-02
4
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 7 45
Oracle Date 6 35
Please explain Equi-join 3 41
Any benefit to adding a Clustered index here? 4 33
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

738 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