| Author |
 Topic  |
|
|
srisha
Starting Member
India
38 Posts |
 Posted - 06/20/2013 : 07:50:07
|
select APOBL.IDVEND,APOBL.AMTINVCHC,APOBL.IDINVC,APOBL.DATEINVC,
(select sum (APOBL.AMTINVCHC)from APOBL where APOBL.DATEINVC < 20200309 group by apobl.idvend)openingbalance,
APVEN.VENDNAME
from APOBL
join APVEN on APOBL.IDVEND=APVEN.VENDORID where DATEINVC between 20190309 and 20200309
and VENDNAME like 'e%'
--------------------------
BY
SRISHA |
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 07:59:09
|
error is came
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.
--------------------------
BY
SRISHA |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:03:51
|
--May be this?
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < '20200309' group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'
--
Chandu |
Edited by - bandi on 06/20/2013 08:06:43 |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 08:04:07
|
select APOBL.DATEINVC,APOBL.IDVEND,APOBL.DESCINVC,(select sum(APOBL.AMTINVCHC)over (partition by apobl.idvend)from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND where CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06')as amt,(select sum(APOBL.AMTINVCHC)over (partition by apobl.idvend) from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND where CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06' and CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06' ) as amt1,APVEN.VENDNAME from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND
this also same error
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:08:09
|
--You can do like this also
select DISTINCT A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor
,C.VENDNAME
from APOBL A
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'
--
Chandu |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 08:10:00
|
May be this?
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (APOBL.AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'
this query using crystal report
but
error is
Arithmetic overflow error
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:12:12
|
Look at the red mark
quote:
Originally posted by srisha
May be this?
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'
this query using crystal report
but
error is
Arithmetic overflow error
--------------------------
BY
SRISHA
--
Chandu |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 08:19:05
|
same thing
arithmetic overflow error converting expression to data type date time
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:22:39
|
That is the error caused by your DATE input.
In which format you had date value for column DATEINVC
quote:
Originally posted by srisha
same thing
arithmetic overflow error converting expression to data type date time
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (APOBL.AMTINVCHC )openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'
--------------------------
BY
SRISHA
--
Chandu |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 08:27:34
|
integer
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:32:11
|
quote:
Originally posted by srisha
integer
--------------------------
BY
SRISHA
How you represent date value as integer ?
Use proper data type
20190309 means 2019-03-09 ?
--
Chandu |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 08:35:23
|
k but i given only value passing parameter only
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/20/2013 : 08:55:17
|
DECLARE @FromDate DATE = '20190309', @ToDate DATE = '20190310' -- These are DATE type params
select DISTINCT A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor
,C.VENDNAME
from APOBL A
join APVEN c on A.IDVEND= C.VENDORID
where (CAST(A.DATEINVC AS VARCHAR(8))between @FromDate and @ToDate)and VENDNAME like 'e%'
NOTE: Use exact type data types for DATE Time values......
Alter your column (DATEINVC) type to DATE if you have permissions
--
Chandu |
Edited by - bandi on 06/20/2013 09:32:56 |
|
|
|
srisha
Starting Member
India
38 Posts |
Posted - 06/20/2013 : 09:12:10
|
Msg 206, Level 16, State 2, Line 22
Operand type clash: date is incompatible with decimal
--------------------------
BY
SRISHA |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 06/21/2013 : 05:56:39
|
quote:
Originally posted by srisha
Msg 206, Level 16, State 2, Line 22
Operand type clash: date is incompatible with decimal
--------------------------
BY
SRISHA
Have you seen my reply (Posted - 06/20/2013 : 08:55:17)
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
| |
Topic |
|
opening balance