cancel
Showing results for 
Search instead for 
Did you mean: 

query problem

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member204969
Active Contributor
0 Kudos

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]

Former Member
0 Kudos

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)

Former Member
0 Kudos

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

former_member204969
Active Contributor
0 Kudos

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]

Former Member
0 Kudos

no same problem, all results are displayed for each table. i don't understand this.

former_member204969
Active Contributor
0 Kudos

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]

Former Member
0 Kudos

oh dear, i see. ok it seems to work. i will test at my advertisement document and report.

Update at 01/10/2008 :

It works fine. Some problems must be solved, but they are not any big problems. Thanks at all for your help.

Answers (0)