Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 11th, 2003, 03:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default HELP - IF ELSE & CASE statement

I've got a 2-fold problem if anyone has the time to look at it:

[u]Problem 1</u> (use of the IF ELSE)I am trying to sort based on the value that is passed on to this stored procedure but I am getting an error stating: ERROR 156 INCORRECT SYNTAX NEAR THE KEYWORD "ORDER" for each "ORDER BY STATEMENT". Here is that code:

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',
        cast(wl.Access_Date as smalldatetime) as Access_Date, 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) = @arch_month
    AND datepart(year, wl.access_date) = @arch_year
    AND hu.hcin_user_id = @hcin_user_id
    AND crr.Descrptn IS NOT NULL
-- AND wl.report_name <> 'patient selection'
-- AND wl.report_name <> 'orders list' -- eliminate the orders list, it is a list of links to result pages (lab, rad, cvs, etc.)
    AND wl.rpt_file_name = 'header request' -- will filter nearly all of the extraneous web_log entries (like... 'search request', 'patient access verified', 'add patient: SUCCESS (0)', etc.)
-- GROUP BY mil.hcin_user_id, hu.hcin_user_id, hu2.lastname, hu2.firstname, wl.access_date,
-- wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id, wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn

    UNION

--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',
cast(wl.Access_Date as smalldatetime) as Access_Date, 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) = @arch_month
AND datepart(year, wl.access_date) = @arch_year
AND crm.hcin_user_id = @hcin_user_id
AND crr.Descrptn IS NOT NULL

IF @sort_by IS NULL
    ORDER BY Surrogate_User
ELSE
        IF @sort_by = 'datetime'
    ORDER BY Surrogate_User, wl.access_date
ELSE
    IF @sort_by = 'pt_name'
    ORDER BY Surrogate_User, wl.patient_last, wl.patient_first
ELSE
    IF @sort_by = 'hosp'
    ORDER BY Surrogate_user, hlo.ods_enterprsfacilitycode

Now [u]Problem 2</u>

In my CASE Statement below I am getting this error:

INCORRECT SYNTAX NEAR '='

Here is that code:

CREATE TABLE #TEMP2 (
    surrogate_user varchar(125),
    surr_user bit)

INSERT INTO #TEMP2

    SELECT
        surrogate_user =
        CASE
            WHEN surrogate_user IS NOT NULL THEN surr_user = 1
            ELSE surr_user = 0
        END

    FROM #TEMP1
    WHERE ((datepart(weekday, wl.access_date) IN (2,3,4,5,6)
            AND datepart(hour, wl.access_date) NOT BETWEEN '7' AND '17'
            OR datepart(weekday, wl.access_date) NOT IN (2,3,4,5,6)))

    SELECT *
    FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ON tmp1.surrogate_user = tmp2.surrogate_user

ANY HELP OR DIRECTION WOULD BE APPRECIATED. THANK YOU!!!
Reply With Quote
  #2 (permalink)  
Old August 11th, 2003, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

SELECT is a single SQL statement, albeit a complex one. The ORDER BY clause is one of the (many) clauses which make up this single SELECT statement.

IF/ELSE is not SQL. It is part of whatever procedural language the database vendor provides to define stored procedures and/or query batches. This language provides a procedural syntax to control the execution of SQL statements.

Thus, you cannot use such statements 'in the middle' of the SQL statement of which your (attempted) ORDER BY clause is a part.

It is not possible to define a variable list of columns in the ORDER BY clause. You can fake it, though. Simply provide a constant value as a 'place holder' in those cases where no sort column is desired. Even though you are also having issues with the CASE statement, that statement is precisely the means to define the variable sort order you seek. Your ORDER BY clause should look something like:
Code:
    ORDER BY Surrogate_User,
        CASE @Sortby
            WHEN NULL THEN 0
            WHEN 'datetime' THEN wl.access_date
            WHEN 'pt_name' THEN wl.patient_last
            WHEN 'hosp' THEN hlo.ods_enterprsfacilitycode END,
        CASE @Sortby
            WHEN NULL THEN 0
            WHEN 'datetime' THEN 0
            WHEN 'pt_name' THEN wl.patient_first
            WHEN 'hosp' THEN 0 END
CASE is an expression which returns a single value. I'm not sure what you are trying to do in your second issue, but your INSERT statement should probably look something like:
Code:
INSERT INTO #TEMP2 (surrogate_user, surr_user)
    SELECT surrogate_user,
        CASE 
            WHEN surrogate_user IS NOT NULL THEN 1
            ELSE 0 END
    FROM #TEMP1 ...
P.S. It looks like your 'surr_user' column violates first normal form, as it simply is a restatement of the "NULLness" of the 'surrogate_user' column.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old September 11th, 2003, 06:49 PM
Authorized User
 
Join Date: Jun 2003
Location: Brentwood, N.Y, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What if I was to write the following statement
Select ColumnName From Tablename where ColumnName = True
What I'm trying to do is is the value is tru the to Insert it into the table if it's False then to Stop all action.
You said we can't use the The statement so what would we use if the value is true????
So right now this is what I have
Select ColumnName From Tablename where ColumnName = True
What would my next line be????
I'm useing VB.NET
Thank you
Reply With Quote
  #4 (permalink)  
Old September 12th, 2003, 06:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm sorry, I'm not sure I can parse your question.

Not all RDBMS's have a boolean data type (e.g. SQL Server), so testing for TRUE may not work.

Quote:
quote:What would my next line be????
I have no idea, as I can't tell what you are trying to accomplish. What are you trying to INSERT? Perhaps if you restate your question ...

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


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case statement cole SQL Language 3 May 8th, 2005 03:02 PM
case statement Hudson40 Access VBA 1 February 11th, 2005 11:31 AM
case statement troubles!! ronny Classic ASP Databases 2 April 10th, 2004 07:32 PM
Using A CASE Statement fastcorvette Access 5 December 24th, 2003 01:39 PM
case statement jakeone Beginning PHP 10 August 19th, 2003 03:03 PM



All times are GMT -4. The time now is 10:06 AM.


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