New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Problem when selecting a view who has subquery [CORE1915] #2348
Comments
Commented by: Sean Leyne (seanleyne) I don't see where "vDtaEntSaiMov " is defined in the View... |
Commented by: Weverton Gomes (weverton) "vDtaEntSaiMov" is the first field of view. Maybe you didn't see because it is in capital letters on DDL (VDTAENTSAIMOV). |
Commented by: Sean Leyne (seanleyne) What are the data types for the "M.dtaentsaimov" and "M.dtaemimov" columns? |
Commented by: Weverton Gomes (weverton) Sean, the type of columns is DATE. Dmitry, yes I'm sure, because we have used this for a long time. I think before FB executed the query on view results and, there, we have the field and don't the subquery anymore. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Submitted by: Weverton Gomes (weverton)
Duplicates CORE1095
I created a view who has a subquery for the field 'VDTAENTSAIMOV':
CREATE VIEW VIEMOVCTBMOV1 (
VDTAENTSAIMOV,
VCODEMPMAT,
VCODEMP,
VSEQMOVCBT,
VSEQMOV,
VMESANOEXEMOV,
VCODLANPAD,
VAGRLANPAD,
VANOEXECNT,
VVLRCTBMOVCTB,
VCIFFOBFREMOV,
VCNTLCX,
VSEQMATMOVCTB,
VNUMMOV,
VNUMTITMOVPGRREC,
VDTAPAGMOV
)
AS
select
(SELECT CASE WHEN M.tipserval = 'E'
THEN M.dtaentsaimov
ELSE M.dtaemimov
END
FROM TBLMOV M
WHERE M.CODEMP = C.CODEMP
AND M.SEQMOV = C.SEQMOV
AND M.MESANOEXEMOV = C.MESANOEXEMOV),
C.codempmat,
C.codemp,
C.seqmovcbt,
C.seqmov,
C.mesanoexemov,
C.codlanpad,
(SELECT P.agrlanpad
FROM TBLLANPAD P
WHERE P.CODEMP = C.CODEMP
AND P.ANOEXECNT = C.ANOEXECNT
AND P.CODLANPAD = C.CODLANPAD),
C.anoexecnt,
C.vlrctbmovctb,
C.ciffobfremov,
C.cntlcx,
COALESCE(C.seqmatmovctb,C.seqmovcbt),
(SELECT M1.nummov
FROM TBLMOV M1
WHERE M1.CODEMP = C.CODEMP
AND M1.SEQMOV = C.SEQMOV
AND M1.MESANOEXEMOV = C.MESANOEXEMOV),
C.NUMTITMOVPGRREC,
C.dtapgtmov
FROM TBLMOVCTB C;
When I try to execute the query below, system gives me the following error "Unsupported field type specified in BETWEEN predicate.":
SELECT DISTINCT V.vSeqMatMovCtb, V.vCodEmpMat
FROM VIEMOVCTBMOV1 V
WHERE V.vCodEmp = :viCodEmp
AND (V.vDtaEntSaiMov BETWEEN '01.01.2008' AND '31.01.2008'
OR V.vDtaPagMov BETWEEN '01.01.2008' AND '31.01.2008')
AND V.vAgrLanPad <> 'O'
If I exchange the subquery for a normal field, the query above works normally. I already has tried put a CAST AS DATE on subquery, but didn't work too. This works fine on Firebird 1.5 and 2.0.
The text was updated successfully, but these errors were encountered: