Match Against Three Fields - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Match Against Three Fields

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2009-03-24 : 20:21:21

I don't know how to start with this sql statement-
I have three fields fundcode, fundcodeseg, and reports to fund.
All are char(10) and have similar data-
ex: a010000091, a01000010001, etc.
Eventually this will be a stored procedure built to retrieve results from a filter screen with several drop down boxes.
From my specs, I will need to allow the user to either select from a drop-down OR enter a string into a textfield and then match on that pattern-
Can anyone help?
Either way,how can I select these three fields and match on an entered parameter?

Thanks in Advance.
John

matty
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 05:19:47

Use LIKE operator.

WHERE fundcode LIKE 'a010000091%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 14:11:08

use a parameter to hold selected dropdown value and in procedure use it like this in where

WHERE fundcode LIKE @Yourparameter + '%'

jtrapat1
Starting Member

43 Posts

Posted - 2009-03-25 : 20:23:42

OK_ I got my statement involving the match on three fields
My next question is: How do i include this section as part of a bigger sql statement-
for example, at the bottom of this message is the full select-
since one of these three fund fields will show up on the report, how would I select it to pass in a variable?

--include fundcode section here

(SELECT m161ident, dcol
FROM (
SELECT m161ident,m161fundcode AS dcol FROM osc_m161
UNION ALL
SELECT m161ident,[M161FundCodeSegregation] FROM osc_m161
UNION ALL
SELECT m161ident,m161reportstofund FROM osc_m161) temp)
--------------------
final :

SELECT DISTINCT a.M161TransactionCode,
a.M161DepartmentCode,a.M161CostCenter,a.M161Variable,
a.M161FiscalYear, a.M161ObjectGroupCharged,a.M161OriginatingAgencyCode,
a.M161BatchNumber,a.M161DocumentNumber,
a.M161ContractOriginatingAgency,a.M161LineNumber,
a.M161FullPartialIndicator,a.M161POContractNumber,
convert(decimal(12,2), a.M161TransactionAmount) as amt,
a.M161TransactionAmountSign,
convert(varchar(10), cast(Left(a.m161lastupdated, 2) + '/' + substring(a.m161lastupdated, 3, 2) + '/' + right(a.m161lastupdated,2) as datetime), 121) as M161LastUpdated,
a.m161transactioncode + ' - ' + rtrim(b.[Description]) as tcode,
--include fundcode section here

(SELECT m161ident, dcol
FROM (
SELECT m161ident,m161fundcode AS dcol FROM osc_m161
UNION ALL
SELECT m161ident,[M161FundCodeSegregation] FROM osc_m161
UNION ALL
SELECT m161ident,m161reportstofund FROM osc_m161) temp)

FROM [OSC_m161] a
INNER JOIN
[OSCTransactionCodes] b
ON b.[TransactionCode]=a.[M161TransactionCode]
INNER JOIN
[APPXAgencyCodes] c
ON c.AgencyCode=a.[m161originatingagencycode]
WHERE a.M161TransactionCode=@transcode
AND
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) >= @startdate
AND
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) <= @enddate
--optional parameters
AND ((@agency_code IS NULL) OR (agency_code = @agency_code))
--
AND ((@fundcode IS NULL) OR (dcol = @fundcode))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:33:49

pass in a variable from where? did nt understand that you mean you need to generate string of codes from first select?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources