on 09-26-2008 11:10 AM
hi there again,
as i asked i write a query for udf´s in business partners like this one :
SELECT T0.[CardCode],T0.[U_altekdnr], T2.[Descr], T3.[Descr], T1.[Country] FROM .[OCRD] T0 INNER JOIN UFD1 T2 ON T2.FldValue = T0.U_gruppe INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode AND T1.Address = 'Standard RE' INNER JOIN UFD1 T3 ON T3.FldValue = T0.U_kundenart WHERE T0.U_gruppe = AND T0.U_kundenart =
The problem is that i get each CardCord entry repeated for each table. e.g. CardCode 1 is twice in OCRD, once in CRD1 and twice in UFD1, the result is 5 lines with CardCode 1.
But i only want one result line. The statement DISTINCT does not help. Any ideas ?
regards
Try something like this:
SELECT T0.CardCode,T0.U_altekdnr,
(Select T2.Descr From UFD1 T2 Where T2.FldValue = T0.U_gruppe),
(Select T3.Descr From UFD1 T3 Where T3.FldValue = T0.U_kundenart),
T1.Country
FROM dbo.OCRD T0 INNER JOIN UFD1 T2 ON T2.FldValue = T0.U_gruppe
WHERE T0.U_gruppe = [%1] AND T0.U_kundenart = [%2]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
jep, that was my first idea, too. But the problem is, that the subquery means to display more than one result.But there should not be more than one entry. eg.:
ocrd -> CardCode = 1, U_gruppe = A, U_kundenart = C
If you display all records you get the CardCode entry and each entry which is joind by UFD.
[Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'Benutzerdefinierte Werte' (CSHS)
try it as
select @descr1 = ''
select @descr2 = ''
select @gruppe = T0.U_gruppe from dbo.OCRD T0 where T0.U_gruppe = [%1]
select @kundenart = T0.U_kundenart from dbo.OCRD T0 where T0.U_kundenart = [%2]
select @descr1 = @descr1 + ' ' + coalesce(descr,'') from ufd1 where FldValue = @gruppe
select @descr1 = @descr1 + ' ' + coalesce(descr,'') from ufd1 where FldValue = @kundenart
SELECT T0.CardCode,T0.U_altekdnr,
@descr1,
@descr2,
T1.Country
FROM dbo.OCRD T0 INNER JOIN UFD1 T2 ON T2.FldValue = T0.U_gruppe
WHERE T0.U_gruppe = @gruppe AND T0.U_kundenart =@kundenart
Then try this modified one:
SELECT T0.CardCode,T0.U_altekdnr,
(Select Top 1 T2.Descr From UFD1 T2 Where T2.FldValue = T0.U_gruppe),
(Select Top 1 T3.Descr From UFD1 T3 Where T3.FldValue = T0.U_kundenart),
T1.Country
FROM dbo.OCRD T0 INNER JOIN UFD1 T2 ON T2.FldValue = T0.U_gruppe
WHERE T0.U_gruppe = [%1] AND T0.U_kundenart = [%2]
I think a Distinct keyword is missing. Try this:
SELECT distinct T0.CardCode,T0.U_altekdnr,
(Select Top 1 T2.Descr From UFD1 T2 Where T2.FldValue = T0.U_gruppe),
(Select Top 1 T3.Descr From UFD1 T3 Where T3.FldValue = T0.U_kundenart),
T0.Country
FROM dbo.OCRD T0 INNER JOIN UFD1 T2 ON T2.FldValue = T0.U_gruppe
WHERE T0.U_gruppe = [%1] AND T0.U_kundenart = [%2]
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.