Skip to content
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

Closed
firebird-automations opened this issue May 26, 2008 · 10 comments
Closed

Problem when selecting a view who has subquery [CORE1915] #2348

firebird-automations opened this issue May 26, 2008 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

I don't see where "vDtaEntSaiMov " is defined in the View...

@firebird-automations
Copy link
Collaborator Author

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

What are the data types for the "M.dtaentsaimov" and "M.dtaemimov" columns?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE1095 [ CORE1095 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This issue duplicates CORE1095.

Are you 100% sure it works on v1.5 and v2.0? I'm asking because subqueries were never allowed for BETWEEN.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants