Author |
Topic |
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 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.VENDNAMEfrom APOBLjoin APVEN on APOBL.IDVEND=APVEN.VENDORID where DATEINVC between 20190309 and 20200309 and VENDNAME like 'e%'--------------------------BYSRISHA |
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 07:59:09
|
error is came Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:03:51
|
[code]--May be this?select A.IDVEND ,A.AMTINVCHC ,A.IDINVC ,A.DATEINVC ,B.openingbalance ,C.VENDNAMEfrom APOBL AJOIN (select sum (AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < '20200309' group by idvend) B on A.idvend = B.idvendjoin APVEN c on A.IDVEND= C.VENDORID where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'[/code]--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 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--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:08:09
|
[code]--You can do like this alsoselect DISTINCT A.IDVEND ,A.AMTINVCHC ,A.IDINVC ,A.DATEINVC ,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor ,C.VENDNAMEfrom APOBL Ajoin APVEN c on A.IDVEND= C.VENDORID where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'[/code]--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 08:10:00
|
May be this?select A.IDVEND ,A.AMTINVCHC ,A.IDINVC ,A.DATEINVC ,B.openingbalance ,C.VENDNAMEfrom APOBL AJOIN (select sum (APOBL.AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvendjoin APVEN c on A.IDVEND= C.VENDORID where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'this query using crystal reportbut error isArithmetic overflow error--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:12:12
|
Look at the red markquote: Originally posted by srisha May be this?select A.IDVEND ,A.AMTINVCHC ,A.IDINVC ,A.DATEINVC ,B.openingbalance ,C.VENDNAMEfrom APOBL AJOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvendjoin APVEN c on A.IDVEND= C.VENDORID where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'this query using crystal reportbut error isArithmetic overflow error--------------------------BYSRISHA
--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 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.VENDNAMEfrom APOBL AJOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvendjoin APVEN c on A.IDVEND= C.VENDORIDwhere (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:22:39
|
That is the error caused by your DATE input.In which format you had date value for column DATEINVCquote: 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.VENDNAMEfrom APOBL AJOIN (select sum (APOBL.AMTINVCHC )openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvendjoin APVEN c on A.IDVEND= C.VENDORIDwhere (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'--------------------------BYSRISHA
--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 08:27:34
|
integer--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:32:11
|
quote: Originally posted by srisha integer--------------------------BYSRISHA
How you represent date value as integer ?Use proper data type20190309 means 2019-03-09 ?--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 08:35:23
|
k but i given only value passing parameter only--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 08:55:17
|
[code]DECLARE @FromDate DATE = '20190309', @ToDate DATE = '20190310' -- These are DATE type paramsselect DISTINCT A.IDVEND ,A.AMTINVCHC ,A.IDINVC ,A.DATEINVC ,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor ,C.VENDNAMEfrom APOBL Ajoin APVEN c on A.IDVEND= C.VENDORID where (CAST(A.DATEINVC AS VARCHAR(8))between @FromDate and @ToDate)and VENDNAME like 'e%'[/code]NOTE: Use exact type data types for DATE Time values......Alter your column (DATEINVC) type to DATE if you have permissions--Chandu |
|
|
srisha
Starting Member
38 Posts |
Posted - 2013-06-20 : 09:12:10
|
Msg 206, Level 16, State 2, Line 22Operand type clash: date is incompatible with decimal--------------------------BYSRISHA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-21 : 05:56:39
|
quote: Originally posted by srisha Msg 206, Level 16, State 2, Line 22Operand type clash: date is incompatible with decimal--------------------------BYSRISHA
Have you seen my reply (Posted - 06/20/2013 : 08:55:17)--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|