Hi

I am trying to run a SQL query that has the following condition:

select
tczz016.item
from
tczzz016

where
((tczzz016._index3={90,"FIBRE"})
and
(tczzz016._index3={120,"CAN"})

Now I know there is data that qualifies for this where statement. But it returns no data. If I use an OR instead of the AND in the statement it returns me the firstly for those items that qualify for the first condition followed by the items that qualify for the second condition.

Is there a problem because by using two conditions in the statement that relate to the same index it finds nothing and cancel each other out? Does anyone know of a better way of putting this?

Thanks

"Or" is what you should be using, because you want all records where index3 matches your selection. This easiest comparision I can come up with is on the item master. Ex.

Select tiitm001.*
where tiitm001.item = "X" and tiitm001.item = "Y"

You would never find a record. But on using or you should get two records. So "OR" should get you what you are looking for.

Mark

Hi mark_h

I see what you mean. The problem is that the number 90 refers to a line number and the "FIBRE" is the response to the line. So for each item they have responses for each line. Thus I would want all items where the response was FIBRE to line number 90 and CAN to line 120. They are the same fields but different records about the same item.

Not sure how I would go about this then. I need all items where the response to line number 90 is FIBRE and to line 120 is CAN. Any ideas how I would go about this?

How about something like this:

select tczzz016.item
from tczzz016
where tczzz016._index3 = {90, "FIBRE"}
and tczzz016.item IN
(select tczzz016.item
from tczzz016
where tczzz016._index3 = {120, "CAN"})


I don't think you need to alias the table in this case, but you might try it if the first attempt fails.

I was thinking that the "or" statement would return all records where the line was 90, the response was "Fibre", it would also return all records where the line was 120 and the response was "Can". So the way I picture the records is like below

Item line response
1 80 none
1 90 fibre
1 120 can
2 90 fibre
2 120 can

In this case I would expect it to return 4 records - item 1 twice and item 2 twice. If you only wanted two records then you could use the group by statement.

This is assuming I have the record structure correct in a simplified version. If I got it wrong could you post a few sample records.

Mark

Let us say 'FIBER' or 'CAN' is the field fld1.
Let us say the numbers are fld2.

I think what you need is:



where ( (fld1 = "FIBER" and fld2 = 90)
OR (fld1 = "CAN" and fld2 =120))



I am afraid you will not be able to use the index.

If the "in" clause didn't work, use the following:

select tczzz016.item
from tczzz016
where tczzz016._index3 = {90, "FIBRE"}
and exists (
select '1'
from tczzz016 a
where a._index3 = {120, "CAN"}
and a.item = tczzz016.item
)