-2

I am wanting to see ONLY the documents where 3 criteria's are met but the 3 criteria's have multiple criteria's.

Here is my code:

SELECT
t0.DocNum

FROM 
dbo.OIPF t0 inner join IPF2 t1 ON t0.DocEntry = t1.DocEntry
inner join IPF1 t2 on t0.DocEntry = t2.DocEntry

WHERE 
(t1.OhType = 'W' and t1.CostSum > 0) and
(t1.OhType = 'F' and T1.CostSum > 0) and
(t1.OhType = 'Q' and T1.CostSum > 0)

This gives me zero results. But to me that is how I would make sure that all 3 of these cost types have totals greater than 0.

Could any one help me on this?

Thanks!!

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
KTate
  • 57
  • 1
  • 7
  • 1
    And the question is? – bksi Aug 05 '15 at 18:25
  • My question is, how would you restructure the query to be able to say" WhatDocument has type A cost > $0 and type B cost is > $0 and Type C is > $0 – KTate Aug 05 '15 at 18:32
  • 1
    Your query do exactly what you say. If you give more information about the tables. then there can be found more things that can causes issues – bksi Aug 05 '15 at 18:32

3 Answers3

1

You have 3 different criteria. Try or but not and.

SELECT
t0.DocNum

FROM 
dbo.OIPF t0 inner join IPF2 t1 ON t0.DocEntry = t1.DocEntry
inner join IPF1 t2 on t0.DocEntry = t2.DocEntry

WHERE

(t1.OhType = 'W' and t1.CostSum > 0) or
(t1.OhType = 'F' and T1.CostSum > 0) or
(t1.OhType = 'Q' and T1.CostSum > 0);
Alex Bender
  • 846
  • 13
  • 26
  • 1
    OP needs all 3 criteria to be met. This query wouldn't do that – bksi Aug 05 '15 at 18:37
  • @bksi there is no object which can fulfill the clause (t1.OhType = 'W' AND t1.OhType = 'F') hence AND will not work in that case. Sorry for the slow response lol – Alex Bender Jun 29 '22 at 21:41
0
SELECT
t0.DocNum
FROM dbo.OIPF t0 
inner join IPF2 t1 ON t0.DocEntry = t1.DocEntry
--inner join IPF1 t2 ON t0.DocEntry = t2.DocEntry
WHERE t1.OhType in ('W' , 'F', 'Q') and t1.CostSum > 0

What you are trying to do is equivalent to this. Also, i am not sure why the table IPF1 is being joined on, if it is not being used.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
0

If I understand your question correctly, Alex_Bender's answer would be correct.

Another simpler way to express it which may be simpler to understand would be like this:

SELECT
t0.DocNum

FROM 
dbo.OIPF t0 inner join IPF2 t1 ON t0.DocEntry = t1.DocEntry
inner join IPF1 t2 on t0.DocEntry = t2.DocEntry

WHERE t1.OhType IN ('W', 'F', 'Q')
AND t1.CostSum > 0
sstan
  • 35,425
  • 6
  • 48
  • 66
  • This would return result if one of the criteria is met. The OP needs all 3 to be met. – bksi Aug 05 '15 at 18:36
  • 1
    @bski: Clearly, a row can never be true for all 3 criteria at the same time. So, I have to conclude that it's simply a matter of OP not expressing his intent very clearly. That's all. – sstan Aug 05 '15 at 18:39
  • 1
    That's why i asked him for details before put any answer ;) – bksi Aug 05 '15 at 18:39