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")
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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
Answered 02/17/2012 by: Gearshock
Senior Yellow Belt

Please log in to comment
0
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:
-- ...
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:
Answered 02/17/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I think the error is being generated in the last line here:
FROM ASSET_DATA_11
LEFT JOIN ASSET_ASSOCIATION JR58
ON JR58.ASSOCIATED_ASSET_ID = ASSET.ID

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.
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.
Answered 02/17/2012 by: dchristian
Red Belt

Please log in to comment
0
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.
Answered 02/20/2012 by: Gearshock
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity