sql query not working after upgrading db connect from 1.1.5 to 3.1.4

[jiaqya]

Below is the query, trying to join output of a query to a table, it worked in 1.1.5 but not working with 3.1.4

SELECT * FROM (SELECT TO_CHAR(D.SYS_DATE,'YYYY-MM-DD HH24:MI:SS') AS LOG_DATE,
TO_NUMBER(TO_CHAR(D.ROLLUP_DATE, 'yyyymmddhh24miss')) AS LAST_UPDATE,
S.SERVER_NAME AS S_NAME,
S.SERVER_NAME,D.FILESYSTEM,
D.MOUNTED_ON, D.KBYTES
FROM SERVER_DATA.SERVER S, SERVER_DATA.SERVER_DFK_CURRENT D
WHERE D.SERVER_NAME = S.SERVER_NAME)
LEFT OUTER JOIN SERVER_DATA.ORACLE_INFO O ON S_NAME = O.SERVER_NAME

error : java.lang.IllegalStateException: Column name conflicted, please set shortnames option to false and retry
No results found.

[paulbannister · Accepted solution]

Hi There,

It might be as simple as cleaning up the SQL and being a bit more specific with the column names and use the format [TABLE_NAME].[COLUMN_NAME] instead of just the column name, there may be a column in both tables in the query of the same name

[jiaqya]

Hi Paul, thanks for input, but im not too good at sql . so need help.
could you suggest on what i should be doing based on example ..

SELECT * FROM (SELECT TO_CHAR(D.SYS_DATE,'YYYY-MM-DD HH24:MI:SS') AS LOG_DATE,
TO_NUMBER(TO_CHAR(D.ROLLUP_DATE, 'yyyymmddhh24miss')) AS LAST_UPDATE,
S.SERVER_NAME AS S_NAME,
S.SERVER_NAME,D.FILESYSTEM,
D.MOUNTED_ON, D.KBYTES
FROM SERVER_DATA.SERVER S, SERVER_DATA.SERVER_DFK_CURRENT D
WHERE D.SERVER_NAME = S.SERVER_NAME)
LEFT OUTER JOIN SERVER_DATA.ORACLE_INFO O ON S_NAME = O.SERVER_NAME

[paulbannister]

You know what, it might be as simple as adding in an alias for the wrap:

SELECT * FROM (SELECT TO_CHAR(D.SYS_DATE,'YYYY-MM-DD HH24:MI:SS') AS LOG_DATE,
TO_NUMBER(TO_CHAR(D.ROLLUP_DATE, 'yyyymmddhh24miss')) AS LAST_UPDATE,
S.SERVER_NAME AS S_NAME,
S.SERVER_NAME,D.FILESYSTEM,
D.MOUNTED_ON, D.KBYTES
FROM SERVER_DATA.SERVER S, SERVER_DATA.SERVER_DFK_CURRENT D
WHERE D.SERVER_NAME = S.SERVER_NAME) t
LEFT OUTER JOIN SERVER_DATA.ORACLE_INFO O ON S_NAME = O.SERVER_NAME

Note the simple "t" after the ")"

[jiaqya]

Thanks for the idea, the below query did the trick for me..

SELECT t.FILESYSTEM,t.KBYTES,t.LAST_UPDATE,t.LOG_DATE,t.MOUNTED_ON,t.SERVER_NAME,t.S_NAME,O.GROUPNAME FROM (SELECT TO_CHAR(D.SYS_DATE,'YYYY-MM-DD HH24:MI:SS') AS LOG_DATE,
TO_NUMBER(TO_CHAR(D.ROLLUP_DATE, 'yyyymmddhh24miss')) AS LAST_UPDATE,
S.SERVER_NAME AS S_NAME,
S.SERVER_NAME,D.FILESYSTEM,
D.MOUNTED_ON, D.KBYTES
FROM SERVER_DATA.SERVER S, SERVER_DATA.SERVER_DFK_CURRENT D
WHERE D.SERVER_NAME = S.SERVER_NAME) t
LEFT OUTER JOIN SERVER_DATA.ORACLE_INFO O ON t.SERVER_NAME = O.SERVER_NAME

[paulbannister]

No problem, its always the little things that trip us up!