Skip to Content
0

Error in Query

Feb 23, 2017 at 07:14 AM

67

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Taseeb Saeed Feb 23, 2017 at 08:07 AM
2

Hello,

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

Thanks,

Engr. Taseeb Saeed

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hello Tasseb,

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

Regards

Amol

0

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

0

Hello,

Yes exactly

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



Thanks,

Engr. Taseeb Saeed

0
Nagarajan K Feb 23, 2017 at 07:28 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Amol Bairagi Feb 23, 2017 at 07:38 AM
0

Hi Nagarajan,

Thanks for reply but same error system give.

Regards

Amol


error.png (170.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 23, 2017 at 07:55 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Amol Bairagi Feb 23, 2017 at 09:10 AM
0

Dear Nagarajan,

Could you please provide any solutions.

Regards

Amol B

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 23, 2017 at 09:21 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Amol Bairagi Feb 23, 2017 at 09:42 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 23, 2017 at 11:18 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded