Error in Query

[amol_bairagi]

Dear all,

I face an issue in Query generator.

When i execute the query in query generator, the error is like this

1). [Microsoft][SQL Server Native Client 10.0][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.

select ShortName 'customer code',

(select OCRD.cardname from ocrd where cardcode =shortname) 'Customer Name',

(select OCRD.U_region from ocrd where cardcode =shortname) 'Branch',

(Select oslp.slpname from oslp left outer join

ocrd on ocrd.slpcode=oslp.slpcode Where ocrd.cardcode=shortname)[Sales Employee],

(select OCRG.Groupname from OCRG where ocrg.GroupCode in

(Select ocrd.GroupCode from ocrd Where ocrd.cardcode=JDT1.shortname))[Customer Group],

(select OCRD.City from ocrd where cardcode =shortname) [City]

,(select top 1 name from ocst where Code in(select OCRD.State1 from ocrd where cardcode =shortname)) [State]

,

(Select C1.Balance from Ocrd C1 where C1.Password=ShortName)[Deposit New From Dealer],

OCRD.CreditLine[Credit Limit],(OCRD.Balance) "Total Outstanding",

(OCRD.CreditLine-(OCRD.Balance))[Limit Avaibility],

ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum in(6,12,13,19,10,15)

and paidsum<doctotal and docdate < dateadd(dd,-7,convert(date,getdate()))

AND DocStatus<>'C'

),0.00) " Overdue COD",

ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum not in(6,12,13,19,10,15)

and paidsum<doctotal and docdate < dateadd(dd,-35,convert(date,getdate()))

AND DocStatus<>'C'

),0.00) "Overdue NON COD"

from JDT1 left outer join OCRD on OCRD.CardCode=JDT1.ShortName

where JDT1.refDate <= GETDATE()

And OCRD.CardType='C'

group by JDT1.ShortName,OCRD.Balance,OCRD.CreditLine ,OCRD.OrdersBal

Does any one know where is the problem ?

Thanks in advance

[Former Member]

Hello,

Make sure to use Top 1 in each sub query. Thats it.

Thanks,

Engr. Taseeb Saeed

[amol_bairagi]

Hello Tasseb,

Could you please elaborate how to use top 1 in query.

Regards

Amol

[Johan_H]

Hi Amol,

For example:

(select OCRD.cardname from ocrd where cardcode =shortname) 'Customer Name'

becomes:

(select TOP 1 OCRD.cardname from ocrd where cardcode =shortname) 'Customer Name'

Regards,

Johan

[Former Member]

Hello,

Yes exactly

(select TOP 1 OCRD.cardname from ocrd where cardcode =shortname) 'Customer Name'

Thanks,

Engr. Taseeb Saeed

[kothandaraman_nagarajan]

Try this,

select ShortName 'customer code',

(select OCRD.cardname from ocrd where cardcode =shortname and Cardtype = 'c' ) 'Customer Name',

(select OCRD.U_region from ocrd where cardcode =shortname and Cardtype = 'c') 'Branch',

(Select oslp.slpname from oslp left outer join

ocrd on ocrd.slpcode=oslp.slpcode Where ocrd.cardcode=shortname)[Sales Employee],

(select OCRG.Groupname from OCRG where ocrg.GroupCode in

(Select ocrd.GroupCode from ocrd Where ocrd.cardcode=JDT1.shortname))[Customer Group],

,(select top 1 name from ocst where Code in(select OCRD.State1 from ocrd where cardcode =shortname)) [State]

,

(Select C1.Balance from Ocrd C1 where C1.Password=ShortName)[Deposit New From Dealer],

OCRD.CreditLine[Credit Limit],(OCRD.Balance) "Total Outstanding",

(OCRD.CreditLine-(OCRD.Balance))[Limit Avaibility],

ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum in(6,12,13,19,10,15)

and paidsum<doctotal and docdate < dateadd(dd,-7,convert(date,getdate()))

AND DocStatus<>'C'

),0.00) " Overdue COD",

ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum not in(6,12,13,19,10,15)

and paidsum<doctotal and docdate < dateadd(dd,-35,convert(date,getdate()))

AND DocStatus<>'C'

),0.00) "Overdue NON COD"

from JDT1 left outer join OCRD on OCRD.CardCode=JDT1.ShortName

where JDT1.refDate <= GETDATE()

And OCRD.CardType='C'

group by JDT1.ShortName,OCRD.Balance,OCRD.CreditLine ,OCRD.OrdersBal

[amol_bairagi]

Hi Nagarajan,

When i run these query in Sql server then error show on below line Item,

select ShortName 'customer code'

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Regards

Amol

[kothandaraman_nagarajan]

Are you able to run your query in SQL server? Then may be you can find which line returning more than one value.

OR

Logout from SAP b1 and then try to run same query again.

[amol_bairagi]

Dear Nagarajan,

Could you please provide any solutions.

Regards

Amol B

[kothandaraman_nagarajan]

Tested in my DB, getting result after running your query. From the error message, one of sub query returns more than one value. You need find which sub query returns more than one value.

[amol_bairagi]

Hi Nagarajan,

Thanks for reply but same error system give.

Regards

Amol

[kothandaraman_nagarajan]

Hi,

Try this query,

select ShortName 'customer code', (select OCRD.cardname from ocrd where cardcode =shortname) 'Customer Name', (Select oslp.slpname from oslp left outer join ocrd on ocrd.slpcode=oslp.slpcode Where ocrd.cardcode=shortname)[Sales Employee], (select OCRG.Groupname from OCRG where ocrg.GroupCode in (Select ocrd.GroupCode from ocrd Where ocrd.cardcode=JDT1.shortname))[Customer Group], (select OCRD.City from ocrd where cardcode =shortname) [City] ,(select top 1 name from ocst where Code in(select OCRD.State1 from ocrd where cardcode =shortname)) [State] , (Select C1.Balance from Ocrd C1 where C1.Password=ShortName)[Deposit New From Dealer], OCRD.CreditLine[Credit Limit],(OCRD.Balance) "Total Outstanding", (OCRD.CreditLine-(OCRD.Balance))[Limit Avaibility], ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum in(6,12,13,19,10,15) and paidsum<doctotal and docdate < dateadd(dd,-7,convert(date,getdate())) AND DocStatus<>'C' ),0.00) " Overdue COD", ISNULL((select sum(doctotal-paidsum) from OINV where cardcode=JDT1.shortname and groupnum not in(6,12,13,19,10,15) and paidsum<doctotal and docdate < dateadd(dd,-35,convert(date,getdate())) AND DocStatus<>'C' ),0.00) "Overdue NON COD" from JDT1 left outer join OCRD on OCRD.CardCode=JDT1.ShortName where JDT1.refDate <= GETDATE() And OCRD.CardType='C' group by JDT1.ShortName,OCRD.Balance,OCRD.CreditLine ,OCRD.OrdersBal

Regards,

Nagarajan