?
Solved

Microsoft, SQL, 2005, Query Help Needed

Posted on 2008-10-02
4
Medium Priority
?
190 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 2000 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

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

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…
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 …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

771 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