K1000 5.3 Warranty Report Errors
I've tried creating two warranty reports and was wondering if anyone else has encountered this error, or can at least give me an idea of what is wrong. I've got it set to read the "warranty exp" field that I created and report if there is <90 days on one report and <180 days on the other. And both pump out this error when run, no matter if I am trying txt, html, or csv. Is the system just not structuring the SQL properly or is something else amiss?
mysql error: [1054: Unknown column 'ASSET.ID' in 'on clause'] in EXECUTE("SELECT ASSET_DATA_11.FIELD_36 AS FIELD_36,ASSET_DATA_11.FIELD_35 AS FIELD_35,R58_A41.NAME AS R58_FIELD_41,R58.NAME AS FIELD_R58,RD58.FIELD_44 AS R58_FIELD_44,RD58.FIELD_45 AS R58_FIELD_45 FROM ASSET_DATA_11 LEFT JOIN ASSET_ASSOCIATION JR58 ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID AND JR58.ASSET_FIELD_ID=58 LEFT JOIN ASSET R58 ON R58.ID = JR58.ASSET_ID LEFT JOIN ASSET_DATA_5 RD58 ON RD58.ID=R58.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION R58_J41 ON R58_J41.ASSET_ID = R58.ID AND R58_J41.ASSET_FIELD_ID=41 LEFT JOIN ASSET R58_A41 ON R58_A41.ID = R58_J41.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_2 R58_AD41 ON R58_AD41.ID = R58_A41.ASSET_DATA_ID LEFT JOIN ASSET ON ASSET_DATA_11.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=11 WHERE ((DATE(ASSET_DATA_11.FIELD_36) >= NOW() AND DATE(ASSET_DATA_11.FIELD_36) < DATE_ADD(NOW(),INTERVAL 180 DAY))) ORDER BY FIELD_36")
mysql error: [1054: Unknown column 'ASSET.ID' in 'on clause'] in EXECUTE("SELECT ASSET_DATA_11.FIELD_36 AS FIELD_36,ASSET_DATA_11.FIELD_35 AS FIELD_35,R58_A41.NAME AS R58_FIELD_41,R58.NAME AS FIELD_R58,RD58.FIELD_44 AS R58_FIELD_44,RD58.FIELD_45 AS R58_FIELD_45 FROM ASSET_DATA_11 LEFT JOIN ASSET_ASSOCIATION JR58 ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID AND JR58.ASSET_FIELD_ID=58 LEFT JOIN ASSET R58 ON R58.ID = JR58.ASSET_ID LEFT JOIN ASSET_DATA_5 RD58 ON RD58.ID=R58.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION R58_J41 ON R58_J41.ASSET_ID = R58.ID AND R58_J41.ASSET_FIELD_ID=41 LEFT JOIN ASSET R58_A41 ON R58_A41.ID = R58_J41.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_2 R58_AD41 ON R58_AD41.ID = R58_A41.ASSET_DATA_ID LEFT JOIN ASSET ON ASSET_DATA_11.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=11 WHERE ((DATE(ASSET_DATA_11.FIELD_36) >= NOW() AND DATE(ASSET_DATA_11.FIELD_36) < DATE_ADD(NOW(),INTERVAL 180 DAY))) ORDER BY FIELD_36")
0 Comments
[ + ] Show comments
Answers (4)
Please log in to answer
Posted by:
Gearshock
12 years ago
Here's the actual code
SELECT ASSET_DATA_11.FIELD_36 AS FIELD_36,ASSET_DATA_11.FIELD_35 AS FIELD_35,R58_A41.NAME AS R58_FIELD_41,R58.NAME AS FIELD_R58,RD58.FIELD_44 AS R58_FIELD_44,RD58.FIELD_45 AS R58_FIELD_45 FROM ASSET_DATA_11 LEFT JOIN ASSET_ASSOCIATION JR58 ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID AND JR58.ASSET_FIELD_ID=58
LEFT JOIN ASSET R58 ON R58.ID = JR58.ASSET_ID
LEFT JOIN ASSET_DATA_5 RD58 ON RD58.ID=R58.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION R58_J41 ON R58_J41.ASSET_ID = R58.ID AND R58_J41.ASSET_FIELD_ID=41
LEFT JOIN ASSET R58_A41 ON R58_A41.ID = R58_J41.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_2 R58_AD41 ON R58_AD41.ID = R58_A41.ASSET_DATA_ID
LEFT JOIN ASSET ON ASSET_DATA_11.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=11 WHERE ((DATE(ASSET_DATA_11.FIELD_36) >= NOW() AND DATE(ASSET_DATA_11.FIELD_36) < DATE_ADD(NOW(),INTERVAL 180 DAY))) ORDER BY FIELD_36
Posted by:
GillySpy
12 years ago
In all cases that I see "ASSET" it is aliased. E.g. ASSET R58 so you would have to use R58.ID or join ASSET (as itself) earlier.
snippet from your query:
I see asset in there at least twice so you may be doing it more than just here:
snippet from your query:
-- ...
ASSET_ASSOCIATION JR58 ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID AND JR58.ASSET_FIELD_ID=58 LEFT JOIN ASSET R58 ON R58.ID
-- ...I see asset in there at least twice so you may be doing it more than just here:
Posted by:
dchristian
12 years ago
I think the error is being generated in the last line here:
Your attempting to join ASSET_DATA_11 to ASSET_ASSOCIATION using a field from ASSET (which has been declared yet).
Try moving the ASSET join up. Something like this.
Sorry I can't test the above SQL since all asset tables are different.
FROM ASSET_DATA_11
LEFT JOIN ASSET_ASSOCIATION JR58
ON JR58.ASSOCIATED_ASSET_ID = ASSET.IDYour attempting to join ASSET_DATA_11 to ASSET_ASSOCIATION using a field from ASSET (which has been declared yet).
Try moving the ASSET join up. Something like this.
SELECT ASSET_DATA_11.FIELD_36 AS FIELD_36,
ASSET_DATA_11.FIELD_35 AS FIELD_35,
R58_A41.NAME AS R58_FIELD_41,
R58.NAME AS FIELD_R58,
RD58.FIELD_44 AS R58_FIELD_44,
RD58.FIELD_45 AS R58_FIELD_45
FROM ASSET_DATA_11
LEFT JOIN ASSET
ON ASSET_DATA_11.ID = ASSET.ASSET_DATA_ID
AND ASSET.ASSET_TYPE_ID = 11
LEFT JOIN ASSET_ASSOCIATION JR58
ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID
AND JR58.ASSET_FIELD_ID = 58
LEFT JOIN ASSET R58
ON R58.ID = JR58.ASSET_ID
LEFT JOIN ASSET_DATA_5 RD58
ON RD58.ID = R58.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION R58_J41
ON R58_J41.ASSET_ID = R58.ID
AND R58_J41.ASSET_FIELD_ID = 41
LEFT JOIN ASSET R58_A41
ON R58_A41.ID = R58_J41.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_2 R58_AD41
ON R58_AD41.ID = R58_A41.ASSET_DATA_ID
WHERE (( DATE(ASSET_DATA_11.FIELD_36) >= NOW()
AND DATE(ASSET_DATA_11.FIELD_36) < DATE_ADD(NOW(), INTERVAL 180 DAY) )
)
ORDER BY FIELD_36 Sorry I can't test the above SQL since all asset tables are different.
Posted by:
Gearshock
12 years ago
Thanks very much guys, that code that you posted dchristian appears to have worked perfectly. I really need to take an SQL class. It doesn't seem too complex and seems like it might be useful in a variety of applications.
So my next question with this is; is this something that I need to report as a bug in the report creation wizard? I made that report using the wizard.
So my next question with this is; is this something that I need to report as a bug in the report creation wizard? I made that report using the wizard.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.