dynamic sql where clause - how to remove last 'or'

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

SQL Server 2008 Forums

Transact-SQL (2008)

dynamic sql where clause - how to remove last 'or'

Author

Topic

mikedu
Starting Member

9 Posts

Posted - 2011-10-19 : 15:15:38

I am creating a dynamic where clause on my sql query (see below) that I am running in an SSIS package. It works great except for my the last line, I have an 'or' statement after each line, and I want to remove it from the last line. Any ideas?

select [dim_patient].[ptfirstname] , [dim_patient].[ptlastname]
into ccFinal
from [fact_invoice] [fact_invoice] WITH (NOLOCK) left join [dim_patient] [dim_patient] WITH (NOLOCK) ON [fact_invoice].[InvFKPtPk]= [dim_patient].[PtPK] left join [dim_date] [dim_date_dim_patient_DOB] WITH (NOLOCK) ON [dim_patient].[PtBirthDtId]= [dim_date_dim_patient_DOB].[Date_Id] left join [dim_fsc] [dim_fsc_fact_invoice_InvFscID] WITH (NOLOCK) ON [fact_invoice].[InvFscID]= [dim_fsc_fact_invoice_InvFscID].[FscPk] left join [dim_fsc] [dim_fsc_fact_invoice_InvOrigFscID] WITH (NOLOCK) ON [fact_invoice].[InvOrigFscID]= [dim_fsc_fact_invoice_InvOrigFscID].[FscPk] left join [dim_date] [dimdate_fact_invoice_InvServiceDt] WITH (NOLOCK) ON [fact_invoice].[InvServiceDt]= [dimdate_fact_invoice_InvServiceDt].[date_id]

Where '([dim_patient].[ptlastname] like ''' + SUBSTRING(patientname, 1, CHARINDEX(',', patientname) - 1)+'%'' and [dim_patient].[ptssn] = ''' + socsecno + ''' AND [dimdate_fact_invoice_InvServiceDt].[act_date] BETWEEN '''+ right('0' + rtrim(month(srvfrom)),2) + '/' + right('0' + rtrim(day(srvfrom)),2) + '/' + rtrim(year(srvfrom)) +''' AND ''' + right('0' + rtrim(month(srvthru)),2) + '/' + right('0' + rtrim(day(srvthru)),2) + '/' + rtrim(year(srvthru)) +''') or '

Below is an example of the output, so I need the 'or' on each row except for the last row...

([dim_patient].[ptlastname] like 'lname%' and [dim_patient].[ptssn] = '123-45-6789' AND [dimdate_fact_invoice_InvServiceDt].[act_date] BETWEEN '12/08/2010' AND '12/10/2010') or

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-10-19 : 17:11:54

quick trick

Make the first clause

WHERE
1 = 1

then each additional clause starts with an OR rather than ends with one.

ends up looking like


WHERE
    1 = 1
    OR foo = bar
    OR woo < 10
    ....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mikedu
Starting Member

9 Posts

Posted - 2011-10-20 : 11:35:16

That worked, thanks much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources