Select statement syntax - SQL Server Forums

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

Select statement syntax

Author

Topic

anonmon
Starting Member

6 Posts

Posted - 2010-12-04 : 20:18:03

I've seen several reports on our MS 2000 reporting services server with a (+) within the WHERE area of a SELECT statement. What purpose does it that (+) serve?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-04 : 20:27:55

it is either an addition operator, or a concatenation operator. would need to see the query to tell you.

If the data is numeric, it is addition:

SELECT QtyOnHand + QtyOnOrder FROM...

If it is textual, then it is a concatenator:

SELECT fname + ' ' + lname as fullName....

No different if it's in the where clause...

SELECT someStuff FROM yourTable
WHERE fname + ' ' + lname = 'John Doe'

anonmon
Starting Member

6 Posts

Posted - 2010-12-05 : 03:25:29

Here is the full query...

SELECT SPRIDEN.SPRIDEN_ID, SPRIDEN.SPRIDEN_LAST_NAME, SPRIDEN.SPRIDEN_FIRST_NAME, SPRIDEN.SPRIDEN_CHANGE_IND,
SATURN.SLRRASG.SLRRASG_BLDG_CODE, SATURN.SLRRASG.SLRRASG_ROOM_NUMBER, MAX(SATURN.SPRHOLD.SPRHOLD_HLDD_CODE)
AS HOLDS, SATURN.SFBETRM.SFBETRM_TERM_CODE, f_term_charges(SATURN.SFBETRM.SFBETRM_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE) AS TRMCHARGES, f_term_payments(SATURN.SFBETRM.SFBETRM_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE) AS TRMPAYMENTS, f_account_balance(SATURN.SFBETRM.SFBETRM_PIDM) AS ACCTBALANCE,
f_financial_aid_memos(SATURN.SFBETRM.SFBETRM_PIDM, SATURN.SFBETRM.SFBETRM_TERM_CODE) AS MEMOEDAID,
f_authorized_payments(SATURN.SFBETRM.SFBETRM_PIDM, SATURN.SFBETRM.SFBETRM_TERM_CODE) AS AUTHORIZEDPYMTS,
f_balance_other_terms(SATURN.SFBETRM.SFBETRM_PIDM, '201050') AS PRIORBAL, SATURN.SFBETRM.SFBETRM_AR_IND,
SATURN.SPRHOLD.SPRHOLD_REASON, SPRADDR.SPRADDR_ATYP_CODE, SPRADDR.SPRADDR_STREET_LINE1,
SPRADDR.SPRADDR_STREET_LINE2, SPRADDR.SPRADDR_CITY, SPRADDR.SPRADDR_STAT_CODE, SPRADDR.SPRADDR_ZIP,
SPRADDR.SPRADDR_STATUS_IND
FROM SPRIDEN, SATURN.SFBETRM, SPRADDR, SATURN.SLRRASG, SATURN.SPRHOLD
WHERE SPRIDEN.SPRIDEN_PIDM = SATURN.SFBETRM.SFBETRM_PIDM AND f_calc_registration_hours(SPRIDEN.SPRIDEN_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE, 'TOTAL', 'CREDIT') > 0 AND SATURN.SFBETRM.SFBETRM_PIDM = SPRADDR.SPRADDR_PIDM (+) AND
SPRIDEN.SPRIDEN_PIDM = SATURN.SLRRASG.SLRRASG_PIDM (+) AND SPRIDEN.SPRIDEN_PIDM = SATURN.SPRHOLD.SPRHOLD_PIDM (+) AND
(SPRIDEN.SPRIDEN_CHANGE_IND IS NULL) AND (SATURN.SLRRASG.SLRRASG_TERM_CODE (+) = '201110') AND
(SATURN.SLRRASG.SLRRASG_ASCD_CODE (+) = 'AC') AND (SATURN.SFBETRM.SFBETRM_TERM_CODE = '201110') AND
(SATURN.SPRHOLD.SPRHOLD_TO_DATE (+) > SYSDATE)
GROUP BY SPRIDEN.SPRIDEN_ID, SPRIDEN.SPRIDEN_LAST_NAME, SPRIDEN.SPRIDEN_FIRST_NAME, SPRIDEN.SPRIDEN_CHANGE_IND,
SATURN.SLRRASG.SLRRASG_BLDG_CODE, SATURN.SLRRASG.SLRRASG_ROOM_NUMBER, SATURN.SFBETRM.SFBETRM_TERM_CODE,
f_term_charges(SATURN.SFBETRM.SFBETRM_PIDM, SATURN.SFBETRM.SFBETRM_TERM_CODE),
f_term_payments(SATURN.SFBETRM.SFBETRM_PIDM, SATURN.SFBETRM.SFBETRM_TERM_CODE),
f_account_balance(SATURN.SFBETRM.SFBETRM_PIDM), f_financial_aid_memos(SATURN.SFBETRM.SFBETRM_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE), f_authorized_payments(SATURN.SFBETRM.SFBETRM_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE), f_balance_other_terms(SATURN.SFBETRM.SFBETRM_PIDM, '201050'),
SATURN.SFBETRM.SFBETRM_AR_IND, SATURN.SPRHOLD.SPRHOLD_REASON, SPRADDR.SPRADDR_ATYP_CODE,
SPRADDR.SPRADDR_STREET_LINE1, SPRADDR.SPRADDR_STREET_LINE2, SPRADDR.SPRADDR_CITY, SPRADDR.SPRADDR_STAT_CODE,
SPRADDR.SPRADDR_ZIP, SPRADDR.SPRADDR_STATUS_IND
HAVING (SPRADDR.SPRADDR_STATUS_IND IS NULL) OR
(NOT (SPRADDR.SPRADDR_STATUS_IND = 'I'))
ORDER BY SPRIDEN.SPRIDEN_LAST_NAME, SPRIDEN.SPRIDEN_FIRST_NAME
------------------

I'm fine until the (+)

Thanks

anonmon
Starting Member

6 Posts

Posted - 2010-12-05 : 03:29:30

WHERE SPRIDEN.SPRIDEN_PIDM = SATURN.SFBETRM.SFBETRM_PIDM AND f_calc_registration_hours(SPRIDEN.SPRIDEN_PIDM,
SATURN.SFBETRM.SFBETRM_TERM_CODE, 'TOTAL', 'CREDIT') > 0 AND SATURN.SFBETRM.SFBETRM_PIDM = SPRADDR.SPRADDR_PIDM (+) AND
SPRIDEN.SPRIDEN_PIDM = SATURN.SLRRASG.SLRRASG_PIDM (+) AND SPRIDEN.SPRIDEN_PIDM = SATURN.SPRHOLD.SPRHOLD_PIDM (+) AND
(SPRIDEN.SPRIDEN_CHANGE_IND IS NULL) AND (SATURN.SLRRASG.SLRRASG_TERM_CODE (+) = '201110') AND
(SATURN.SLRRASG.SLRRASG_ASCD_CODE (+) = 'AC') AND (SATURN.SFBETRM.SFBETRM_TERM_CODE = '201110') AND
(SATURN.SPRHOLD.SPRHOLD_TO_DATE (+) > SYSDATE)

Kristen
Test

22859 Posts

Posted - 2010-12-05 : 04:50:10

Its the old style syntax for an OUTER JOIN

Horrid! deprecated (and no longer supported in SQL2008 I think, or maybe just some of its forms are no longer present)

anonmon
Starting Member

6 Posts

Posted - 2010-12-09 : 15:14:52

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-10 : 12:31:07

Isn't the (+) syntax Oracle?

I think the old MS SQL syntaxt for an outer join is *= and =*

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-10 : 12:57:17