RE: Using a calculated column in a WHERE clause
From: Mike Eaton (MikeEaton_at_discussions.microsoft.com)
Date: 07/16/04
- Next message: Joe Celko: "Re: object model to table design mapping problem"
- Previous message: Zip: "Using a parameter in a cursor"
- In reply to: Nitin Rana: "Using a calculated column in a WHERE clause"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 09:52:03 -0700
Thanks to both of you for your help. Tom's solution was a better fit as I need to be able to search for a full name (i.e. 'bob smith' - sorry for not providing a better example) and using the like on first_name and last_name individually I don't think would have worked 100%.
"Nitin Rana" wrote:
> Why don't you write:
> where (emp_name like '%bob%' OR t1.first_name LIKE '%
> Bob%' OR t1.last_name LIKE '%BOB%')
>
> Leave your select statement as it is as you still wanna
> see full name.
>
> -Nitin
>
> >-----Original Message-----
> >Hi there,
> >
> >The idea is that I need to be able to search two tables
> for a certain name using LIKE. I created SQL that
> generates an aggregate of first_name and last_name with
> the alias full_name, but I can't seem to be able to use
> it in the where clause. Here's what I've done so far:
> >
> >table_1
> > emp_id
> > first_name
> > last_name
> > ... more
> >
> >table_2
> > emp_id
> > emp_name (old data that still needs to be
> searched)
> > transaction_data
> > transaction_date
> > ... more
> >
> >SELECT t2.emp_id, t2.transaction_data,
> t2.transaction_date,
> > t1.first_name + ' ' + t1.last_name as full_name
> >FROM table_2 AS t2
> >LEFT JOIN table_1 AS t1 ON t2.emp_id = t1.emp_id
> >WHERE emp_name LIKE '%Bob%' OR full_name LIKE '%Bob%'
> >
> >Some of the employee names to be searched are in
> t2.emp_name while the rest are in the calculated column
> full_name.
> >
> >When I run this query without the WHERE clause, I get a
> result set with the calculated full_name that's just what
> I'm looking for. Now I want to be able to choose only
> those rows where the full_name or emp_name is like Bob.
> >
> >Any suggestions? I'm upgrading an existing database and
> am stuck with having to search in both tables until the
> legacy data drops out.
> >
> >Thanks in advance for your help.
> >.
> >
>
- Next message: Joe Celko: "Re: object model to table design mapping problem"
- Previous message: Zip: "Using a parameter in a cursor"
- In reply to: Nitin Rana: "Using a calculated column in a WHERE clause"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
