Is this line correct in the query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is this line correct in the query?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-02-14 : 08:46:27

the line here "AND (NULLIF(@THEUNIT, '') IS NULL OR LTRIM(RTRIM(P.UNIT_CODE)) = @THEUNIT)"

is this correct? in the app if I try to pass @TheUnit, it passes no records at all.

@FACILITYKEY varchar(1000),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@THEUNIT VARCHAR(25)
as
BEGIN
SELECT M.ORDER_KEY, P.NURSING_UNIT, RTRIM(P.LAST_NAME) + ', ' + RTRIM(P.FIRST_NAME) AS NAME, P.PATIENT_ID, P.MRN, C.ORDER_TYPE_ABBR,
PTC.ORD_GET_LABEL(M.ORDER_KEY, 0, 0, 0, 0, 10, 0, 0, 0, 0, 1) AS ORDER_LABEL,

M.CREATED_ON, M.START_DATE, M.END_DATE, CAST (OEN.DATEONLY(END_DATE)-OEN.DATEONLY(START_DATE) + 1 AS NUMERIC) as DAY_SUPPLY
FROM PTC.ORD_M_ORDER M
INNER JOIN OEN.GEN_M_PATIENT_MAST P ON M.PAT_NUMBER = P.PAT_NUMBER
INNER JOIN PTC.ORD_C_ORDER_TYPE C ON C.ORDER_TYPE_KEY = M.ORDER_TYPE_KEY
WHERE OEN.DATEONLY(END_DATE)- OEN.DATEONLY(START_DATE) < '1900/03/01'

AND ORDER_STATUS IN (4,7,8)
AND M.FACILITY_KEY IN (SELECT VALUE FROM OEN.COMMA_TO_TABLE(@FACILITYKEY))
AND [START_DATE] BETWEEN @STARTDATE AND @ENDDATE
AND (NULLIF(@THEUNIT, '') IS NULL OR LTRIM(RTRIM(P.UNIT_CODE)) = @THEUNIT)
AND CAST(M.ORDER_FLAGS AS BIGINT) & 64 <> 64 AND P.DISCHARGE_DATE IS NULL
ORDER BY P.UNIT_CODE, NAME, C.ORDER_TYPE_ABBR;
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 09:35:57

it looks syntactically correct.
The reason it doesnt return records may be because correct value is not getting passed to it

ALso there are few other things you could improve on

like
OEN.DATEONLY(END_DATE)- OEN.DATEONLY(START_DATE) < '1900/03/01'

can be rewritten using open intervals to avoid use of function over date column so that it can use an index if available on date field

also IN logic and be replaced with JOIN or EXISTS which might perform better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources