Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 24th, 2003, 07:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default GROUP BY and ORDER BY question

I have a stored procedure that has two SQL statements connected by a UNION ALL however I am needing to add an ORDER BY to each SQL statement in order to give the user an option to sort the data based on a @sort_by parameter passed from an ASP page when a column name is clicked. Any HELP would be appreciated. Thank you!!

Here is the SQL statement (it is pretty long, sorry):

DECLARE @date datetime
DECLARE @date2 datetime
DECLARE @date3 datetime
SET @date=convert(datetime,convert(varchar(10),getdate (),101))
--current date with no time so get's 12:00AM
SET @date2=datepart(m,@date)
SET @date3=datepart(yyyy,@date)

BEGIN
SELECT DISTINCT hu.hcin_user_id as 'Physician_hcin_user_id', MIL.Hcin_user_id as 'Surrogate_hcin_user_id',
        rtrim(hu2.lastname) + ', ' + rtrim(hu2.firstname) as 'Surrogate_User',
        wl.Access_Date, wl.Access_Page, wl.Access_Opertn, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
        wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
    FROM hcin_user hu
    LEFT OUTER join hosp_status_link HSL
    ON HSL.Hcin_user_ID = hu.Hcin_user_Id -- get hosp affiliations of drs.
    LEFT OUTER join Hcin_Module_Info HMI
    ON HMI.Hcin_user_id = hu.hcin_user_id -- get physician hcin_user_id of persons mf id
    AND HMI.Description = 'PHIS_ID'
    LEFT OUTER Join mainframe_ID_lookup MIL
    ON hmi.value = MIL.Mainframe_id --get hcin_user_Ids of surrogates
    LEFT OUTER join hcin_user hu2
    ON mil.hcin_user_id = hu2.hcin_user_id --get names of surrogates
    LEFT OUTER join web_log wl
    ON wl.hcin_user_id = mil.hcin_user_id --get weblog activity
    LEFT OUTER join hospital_link_ods hlo
    ON wl.hospital_id = hlo.hospital_id
    LEFT OUTER JOIN clinical_reporting_relationships crr
    ON wl.relationship_id = crr.relatnshp_id --get relationship information
    WHERE hu.isphysician = 1
    AND substring(MIL.Mainframe_ID,4,3) <> 'ALL' -- ignore "All" mainframe ID's
    AND HSL.status_ID not in (1,15) -- status is not denied
    AND hu.helpdesk_clientid <> 10900 -- eliminate pss staff
    AND hu.accountStatus not in (-1, -3, -6) -- eliminate users disabled by pss, accnt expired or disabled by phys from caregate
    AND datepart(month, wl.access_date) = @date2
    AND datepart(year, wl.access_date) = @date3
    AND hu.hcin_user_id = 469
    AND crr.Descrptn IS NOT NULL
    AND wl.report_name <> 'patient selection'
    GROUP BY mil.hcin_user_id, hu.hcin_user_id, hu2.lastname, hu2.firstname, wl.access_date, wl.access_page, wl.access_opertn,
        wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id, wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn

    ORDER BY wl.access_date


    UNION ALL

    --Presby Physicians and their surrogates.

    SELECT DISTINCT crm.hcin_user_id as 'Physician_hcin_user_id',
        crm.surrogate_id as 'Surrogate_hcin_user_id', rtrim(hu.lastname) + ', ' + rtrim(hu.firstname) as 'Surrogate_User',
        wl.Access_Date, wl.Access_Page, wl.Access_Opertn, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
        wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
    FROM clinical_reporting_main crm
    INNER Join Hcin_user hu
    ON hu.hciN_user_id = crm.surrogate_id
    INNER join web_log wl
    ON wl.hcin_user_id = crm.surrogate_id
    LEFT OUTER join hospital_link_ods hlo
    ON wl.hospital_id = hlo.hospital_id
    LEFT OUTER JOIN clinical_reporting_relationships crr
    ON wl.relationship_id = crr.relatnshp_id
    WHERE accountstatus = 0 -- active accounts only

    AND datepart(month,wl.access_date) = @date2
    AND datepart(year, wl.access_date) = @date3
    AND crm.hcin_user_id = 469
    AND crr.Descrptn IS NOT NULL
    GROUP BY crm.surrogate_id, crm.hcin_user_id, wl.patient_id, hu.lastname, hu.firstname, wl.access_date,
        wl.access_page, wl.access_opertn, wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id,
        wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn
    ORDER BY wl.access_date

END
 
Old June 24th, 2003, 08:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sorry, but I didn't say that I am getting the following error when trying to submit this SP:

Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'UNION'.

Again, any help would be appreciated. Thank you.
 
Old June 24th, 2003, 08:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The ORDER BY clause is the last clause processed in a SELECT statement. There can only be one ORDER BY clause in a SELECT. The rows sorted are those rows after the UNION (and everything else) is performed.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old June 24th, 2003, 08:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff, thanks for your time and input. That got it.
 
Old June 24th, 2003, 08:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can not order the results of the individual result sets of the SQL statements in a UNION statement, you can however order the entire result set. This makes sense really, because why would you want to order results and then add more results to it? Surly you want to order all of the results once they have been combined. All you need to do is remove the first ORDER BY wl.access_date and leave the last one.

Regards
Owain Williams
 
Old June 24th, 2003, 08:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for the duplicate answer, Jeff got there while I was typing my reply :D

Regards
Owain Williams
 
Old June 24th, 2003, 08:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Jeff got there while I was typing my reply
In the computer business, this is known as a 'race condition'.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Sort order Question Tere Crystal Reports 1 February 14th, 2005 03:18 PM
Group question Tere Crystal Reports 1 October 19th, 2004 11:08 AM
Group By Question nmccamy Access 1 February 18th, 2004 09:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.