Return all,like,one record based on multiple parameters 1

[Jcfx]

I used this thread as a starting point for my where clause:
thread183-1185537
<by the way this is an awesome group for examples>

Here is my current code. This is for a report run from sql reporting services web server.

@BeginDate datetime
,@EndDate DateTime
,@Vendor varchar (15)= NULL
,@Check varchar (21)= NULL
,@Account varchar(40) = NULL
as
SELECT    
 v_CombinedGeneralLedger.ORDOCNUM
, v_CombinedGeneralLedger.ORPSTDDT
, v_CombinedGeneralLedger.ORMSTRID
, v_CombinedGeneralLedger.DEBITAMT
, v_CombinedGeneralLedger.CRDTAMNT
, v_AllPayableTransactions.DOCAMNT
, v_CombinedGeneralLedger.ORTRXSRC
, PM30300.APFRDCNM
, v_CombinedGeneralLedger.ORMSTRNM
, (Rtrim(GL00100.ACTNUMBR_1) + '-' + Rtrim(GL00100.ACTNUMBR_2) + '-' + Rtrim(GL00100.ACTNUMBR_3)) AS ACCOUNT
, GL00100.ACTDESCR 
, v_CombinedGeneralLedger.SERIES
, v_AllPayableTransactions.VOIDED
,CONVERT(VARCHAR(30),@BeginDate,101) BeginDate
,CONVERT(VARCHAR(30),@EndDate,101) EndDate
FROM         dbo.v_CombinedGeneralLedger AS v_CombinedGeneralLedger
 INNER JOIN
			  dbo.v_AllPayableTransactions AS v_AllPayableTransactions
 ON 
   v_CombinedGeneralLedger.ORDOCNUM = v_AllPayableTransactions.DOCNUMBR
 INNER JOIN
    dbo.GL00100 AS GL00100
 ON v_CombinedGeneralLedger.ACTINDX = GL00100.ACTINDX
 LEFT OUTER JOIN
    dbo.PM30300 AS PM30300 ON v_CombinedGeneralLedger.ORDOCNUM = PM30300.APTODCNM
WHERE
(v_CombinedGeneralLedger.ORPSTDDT between @BeginDate and @EndDate)
AND ((Rtrim(GL00100.ACTNUMBR_1) + '-' + Rtrim(GL00100.ACTNUMBR_2) + '-' + Rtrim(GL00100.ACTNUMBR_3)) =
	Case When IsNull(@Account, '') = ''
                   Then (Rtrim(GL00100.ACTNUMBR_1) + '-' + Rtrim(GL00100.ACTNUMBR_2) + '-' + Rtrim(GL00100.ACTNUMBR_3))
                   Else @Account
                   End )
AND  (v_CombinedGeneralLedger.ORMSTRID = 
	Case When IsNull(@Vendor, '') = ''
                   Then v_CombinedGeneralLedger.ORMSTRID
                   Else @Vendor
                   End )
AND (PM30300.APFRDCNM = 
	Case When IsNull(@Check, '') = ''
                   Then PM30300.APFRDCNM
                   Else @Check
                   End )
AND (v_AllPayableTransactions.VOIDED <> 1) 
AND (v_CombinedGeneralLedger.SERIES = '4')

This works perfectly if someone chooses some or all, but they want to be able to use wild cards or partial account numbers.

For example they can either leave the vendor blank and get all vendors or they can enter a complete vendor id and get that specific vendor, but they can not ask for a range of similar named vendors.

using the most legible part of the code I've tried

AND (PM30300.APFRDCNM [b]LIKE[/b] 
	Case When IsNull(@Check, '') = ''
                   Then PM30300.APFRDCNM
                   Else @Check
                   End )

and I've tried

(declaring another variable at the beginning)

AND (PM30300.APFRDCNM [b]like @var
 
@var = [/b]	Case When IsNull(@Check, '') = ''
                   Then PM30300.APFRDCNM
                   Else @Check
                   End )

neither one works.

What changes do I need to make or how else can I approach this?

Thanks for any assistance, I've been learning alot from you folks.



Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

Try....

[tt][blue]
AND (IsNull(@Check, '') = ''
Or
PM30300.APFRDCNM LIKE @Check + '%'
)
[/blue][/tt]

If the @Check variable is NULL or an empty string, then APFRDCNM is effectively removed from the filter criteria. If @Check is anything other than NULL or empty string, then perform a like comparison.

notice this part: LIKE @Check + '%'

The percent (%) symbol is the wildcard search in SQL.

You may want to change it to: LIKE '%' + @Check + '%'

Lookup LIKE in books on line for more details.

Suppose a piece of data is 'Apple'

Like 'App%' will return this record.
Like '%ppl%' will also return this record.

Like 'ppl%' will NOT because the beginning part of the string does not have ppl (there's an A in there).

Hope this helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcfx]

Helps alot, thanks again.



Julie
CRXI CE10 / RS2005 Sql DB