Unknown column in 'on clause' - SQL Error (1054)

Navigation
0

First of all indicate that I have tried to find my answer here but I can not find any example that resembles mine.

I want to get the data from an activity table with its corresponding documents, but the ACT table is apparently not visible from within the Select I use in IFNULL .

Table TLACTIVIDADES collects the activities. X_ACTIVIDAD is PK and X_DOCUMENTO is UN FK with documents collected in TLDOCPDF and TLDOCURL Table TLDOCACT relates activities to documents, having 2 FK : X_ACTIVIDAD and X_DOCUMENTO , which relates tables TLACTIVIDADES Y ( TLDOCPDF or TLDOCURL ). It also has another TYPE column that indicates whether the type of document is PDF or URL.

The error that returns to me is

  

SQL Error (1054). Unknown column ACT.X_ACTIVITY in 'on clause'.

How can I make ACT visible from IFNULL ? When I work in Oracle this can be done, but in MySQL it does not allow it. Thank you very much in advance. 

    SELECT ACT.*, 
IFNULL((SELECT X_PDF FROM TLDOCPDF PDF
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = PDF.X_PDF
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD AND ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_PDF',
IFNULL((SELECT X_URL FROM TLDOCURL URL
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = URL.X_URL
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD AND ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_URL'    
FROM TLACTIVIDADES ACT    
WHERE ACT.X_EVENTO = 10 ORDER BY ACT.F_ACTIVIDAD ASC
    
asked by Jky 08.06.2018 в 09:09
source

2 answers

0

I just came up with the problem I had. I was trying to relate the tables when I really had to add a condition. This returns the results correctly. Thank you very much for your help. 

SELECT ACT.*, 
IFNULL((SELECT X_PDF FROM TLDOCPDF PDF
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = PDF.X_PDF
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD
    WHERE ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_PDF',
IFNULL((SELECT X_LOCATION FROM TLDOCLOC LOC
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = LOC.X_LOCATION
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD
    WHERE ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_LOCATION',
IFNULL((SELECT X_URL FROM TLDOCURL URL
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = URL.X_URL
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD
    WHERE ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_URL'    
FROM TLACTIVIDADES ACT  
WHERE ACT.X_EVENTO = 10 ORDER BY ACT.F_ACTIVIDAD ASC
    
answered by 08.06.2018 / 10:37
source
0

It should work by doing the JOIN to ACT in the condition if it is not null, since it needs the query of the internal select to be executed independently: 

SELECT ACTF.*, 
IFNULL((SELECT X_PDF FROM TLDOCPDF PDF
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = PDF.X_PDF
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD
    JOIN TLACTIVIDADES ACT ON ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_PDF',
IFNULL((SELECT X_URL FROM TLDOCURL URL
    JOIN TLDOCACT DOC ON DOC.X_DOCUMENTO = URL.X_URL
    JOIN TLACTIVIDADES ACT1 ON ACT1.X_ACTIVIDAD = DOC.X_ACTIVIDAD 
    JOIN TLACTIVIDADES ACT ON ACT.X_ACTIVIDAD = ACT1.X_ACTIVIDAD), 0) AS 'X_URL'    
FROM TLACTIVIDADES ACTF    
WHERE ACTF.X_EVENTO = 10 ORDER BY ACTF.F_ACTIVIDAD ASC
    
answered by 08.06.2018 в 10:15
"Select from (Select" Subquery a doctrine dql Genexus select distinct on an SDT