Problem with nested Select Statement

I am trying to get a list of rows from 1 view and 1 table (that is not
included in the View). The View is called "vAAWO" and the table is
called "PullSaveData". The current stored procedure I have is:

Select [WO Number], [Body], [Part Number], [Complete Date], [WO
Quantity], --Select some fields from the vAAWO view
(
Select [SaveString] from [PullSaveData] --Select the
SaveString field from the PullSaveData table
where ([vAAWO].[WO NUMBER] = [PullSaveData].[WONumber])
--"Join" the two tables by the WO Number field
AND (CharIndex ([vAAWO].[Part Number],
[PullSaveData].[SaveString]) <> 0) --Search for the Part number in the
SaveString Field
) as "PulledString"
>From [vAAWO]
Where [Work Center] Like '34f' -- Show only the items for the passed
Work Center

I appoligize for the "messiness" of this code. This procedure actually
works somewhat. My problem is the nested Select statement, which does
two things.

The first line ([vAAWO].[WO NUMBER] = [PullSaveData].[WONumber]) gets
the records that have the same Work Order number in the two objects (my
view & separate table).
The second line (CharIndex ([vAAWO].[Part Number],
[PullSaveData].[SaveString]) <> 0) looks to see if the "Part Number"
exists in the "SaveString" field.

The problem is that this is returning a recordset as if I typed "OR"
instead of "AND" in my nested Select statement. I get all the records
that match EITHER of the criteria of my first and second lines shown
above.

Can anyone tell me why this is occurring? Obviously, if there is a
better way to do this, I am all ears. Just know that I have tried a
couple of other methods, like joining the separate table in my view
(which got several other extra records).

I would GREATLY appreciate any suggestions that anyone might have.
Thank you!

It's occurring because you aren't actually doing a JOIN on the table,
and excluding the rows you don't want. What about something like this:

SELECT
wo.[WO Number],
wo.[Body],
wo.[Part Number],
wo.[Complete Date],
wo.[WO Quantity],
psd.SaveString AS [PulledString]
FROM [vAAWO] wo
INNER JOIN [PullSaveData] psd
ON wo.[WO NUMBER] = psd.[WONumber]
AND CHARINDEX (wo.[Part Number], psd.[SaveString]) > 0
WHERE wo.[Work Center] = '34f'

-Dave

Kirk wrote:
> I am trying to get a list of rows from 1 view and 1 table (that is not
> included in the View). The View is called "vAAWO" and the table is
> called "PullSaveData". The current stored procedure I have is:
>
> Select [WO Number], [Body], [Part Number], [Complete Date], [WO
> Quantity], --Select some fields from the vAAWO view
> (
> Select [SaveString] from [PullSaveData] --Select the
> SaveString field from the PullSaveData table
> where ([vAAWO].[WO NUMBER] = [PullSaveData].[WONumber])
> --"Join" the two tables by the WO Number field
> AND (CharIndex ([vAAWO].[Part Number],
> [PullSaveData].[SaveString]) <> 0) --Search for the Part number in the
> SaveString Field
> ) as "PulledString"
>>From [vAAWO]
> Where [Work Center] Like '34f' -- Show only the items for the passed
> Work Center
>
> I appoligize for the "messiness" of this code. This procedure actually
> works somewhat. My problem is the nested Select statement, which does
> two things.
>
> The first line ([vAAWO].[WO NUMBER] = [PullSaveData].[WONumber]) gets
> the records that have the same Work Order number in the two objects (my
> view & separate table).
> The second line (CharIndex ([vAAWO].[Part Number],
> [PullSaveData].[SaveString]) <> 0) looks to see if the "Part Number"
> exists in the "SaveString" field.
>
> The problem is that this is returning a recordset as if I typed "OR"
> instead of "AND" in my nested Select statement. I get all the records
> that match EITHER of the criteria of my first and second lines shown
> above.
>
> Can anyone tell me why this is occurring? Obviously, if there is a
> better way to do this, I am all ears. Just know that I have tried a
> couple of other methods, like joining the separate table in my view
> (which got several other extra records).
>
> I would GREATLY appreciate any suggestions that anyone might have.
> Thank you!
>


--
-Dave Markle

http://www.markleconsulting.com/blog