question

MikeDU avatar image
MikeDU asked

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

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 '
dynamic-sqlwhere
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image SirSQL commented ·
How are you dynamically generating your query?
2 Likes 2 ·
MikeDU avatar image MikeDU commented ·
By running an ssis package. I import the data into a stage table, then executing a sql task I want to find all records that meet the above select statement and insert into a final table to be exported. It works fine when I run in mgmt studio, but on the last line there is an "or" and that is what makes the SSIS package bomb...see below for what I want the final two rows to look like: ([dim_patient].[ptlastname] like 'test%' and [dim_patient].[ptssn] = '123-45-6789' AND [dimdate_fact_invoice_InvServiceDt].[act_date] BETWEEN '12/08/2010' AND '12/10/2010') or ([dim_patient].[ptlastname] like 'testR%' and [dim_patient].[ptssn] = '987-65-4321' AND [dimdate_fact_invoice_InvServiceDt].[act_date] BETWEEN '12/08/2010' AND '12/10/2010')
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
you could use a variable, and when you go to add the sql Command variable as an expression, use something like below, just adjust the number of places until the or is gone. SUBSTRING( @[User::sqlCommand] , 1, LEN( @[User::sqlCommand] )-5 )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
You can use SQL function SUBSTRING to truncate last 2 string - DECLARE @SQL VARCHAR(100) SET @SQL='SELECT * FROM PRODUCT_MASTER or' SET @SQL=SUBSTRING(@SQL,1,LEN(@SQL)-2) EXEC (@SQL)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.