| Subject | AW: [firebird-support] Difficult condiion question |
|---|---|
| Author | checkmail |
| Post date | 2014-08-28T13:04:19Z |
I think I have it:
SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP
FROM TTEILE TT
LEFT JOIN TLAGER_SUM LS
ON TT.TEILENR = LS.TEILENR
AND LS.TYP = 1
WHERE TT.TYP=1
AND (LS.MENGE IS NOT NULL
AND LS.MENGE<tt.minb
OR TT.MINB>0 and ls.menge is null)
ORDER BY TT.TEILENR;
The and ls.typ = 1 should exclude all records from tlager_-sum there not typ 1? It was fine!!!!! Thank you
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Donnerstag, 28. August 2014 14:55
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Difficult condiion question
Hello SET,
first, thank you!
[Minb] should be tteile.minb – the minimum inventory amount.
Can you tell me what about the statements before the where conditions? Until now I don’t know about.
Your syntax returns me too many records, some of them has entries in the table tlager_sum, but the amount and the present typ (1) returns me this statement, it is null.
I have a table with stock = tlager_sum. Only records with typ 1 I need, many of them has two records for each material, typ 1 and typ 2. In my case I just need the records with typ 1 and now I have the problem, If there is an entry for typ 2, my code does not return this record. For example:
Tteile.teilenr = ‘PIECE1’, tteile.minb = 1000
In the table tlager_sum is a record, teilenr ‘PIECE1’, Menge (amount) = 100, typ = 2
In this case I would return this Teilenr because it is not present and the minb (minimum inventory amount) is set.
Thans J
>Hello,
>Hi Olaf, I don't quite understand your question, but it is possible that you just need to add 'AND TLAGER_SUM.TYP = 1' to your left join. Below is the sql after I tried to tidy it a bit and simplify to see if that helped me understand your question (by the way, I don't understand [minb], is that some kind of parameter?).
>the following sql statement is almost done, just one thing is problematic.
>
>In the table tlager_sum I have two types of amounts, one of typ 1, one of typ 2. I just need the one of typ1. But if the material not in the table listet
>or just für typ 2, and there is a minimum inventory in the tteile set, then should I get this record too.
>
>Expect for one constellation it works fine: It was set a minimum inventory and in the table tlager_sum It was present a record with an amount of typ 2 -
>then I don't get this record. How can I solve this problem in firebird? In Microsoft Access I can create a subquery how gets me only typ 1, whats in
>firebird the best way?
>SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB, TLAGER_SUM.TYP, TTEILE.TYP
>FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR
>WHERE (((TLAGER_SUM.MENGE)<[minb]) AND ((TLAGER_SUM.TYP)=1) AND ((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)>0) AND ((TTEILE.TYP)=1))
>ORDER BY TTEILE.TEILENR;
HTH,
Set
SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP
FROM TTEILE TT
LEFT JOIN TLAGER_SUM LS
ON TT.TEILENR = LS.TEILENR
AND LS.TYP = 1
AND LS.MENGE<[minb]
AND LS.TYP=1
WHERE TT.TYP=1
AND (LS.MENGE IS NOT NULL
OR TT.MINB>0)
ORDER BY TT.TEILENR;