[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • Last Modified:

ORACLE STORED PROC QUESTION - invalid column name

SELECT * FROM ( SELECT ROWNUM rn,
(SELECT m.SESS_END_TM FROM USER_MET m WHERE m.ME_ID = A_GRP_ID) AS SESS_END_TM,
(SELECT m.BRWS_INFO FROM USER_MET m WHERE m.ME_ID = A_GRP_ID) AS BRWS_INFO ,
(SELECT m.IP_ADDR FROM USER_MET m WHERE m.ME_ID = A_GRP_ID) AS IP_ADDR ,
(SELECT m.MOBILE_VER FROM USER_MET m WHERE m.ME_ID = A_GRP_ID) AS MOBILE_VER ,
(SELECT MAX(CASE WHEN c.PARM_KEY = 'FLASH' THEN c.PARM_V END) FROM USER_MET_ADDL_INFO c WHERE c.ME_ID = A_GRP_ID ) AS FLASH ,
(SELECT MAX(CASE WHEN c.PARM_KEY = 'OS' THEN c.PARM_V END) FROM USER_MET_ADDL_INFO c WHERE c.ME_ID = A_GRP_ID ) AS OS ,
(SELECT MAX(CASE WHEN c.PARM_KEY = 'UA' THEN c.PARM_V END) FROM USER_MET_ADDL_INFO c WHERE c.ME_ID = A_GRP_ID ) AS UA ,
z.* FROM (
SELECT (select c.A_NAME from IIP_A_TYPES c WHERE c.A_TYPE_SK=a.A_TYPE_SK) AS A_NAME,

LOGIN_ID, E_ID, A_TM, INIT_BY, S_CDE, USER_TYPE, A_GRP_ID FROM MASTER_TABLE a
WHERE a.A_TM BETWEEN TO_DATE('04/01/2011', 'MM/DD/YYYY') AND
TO_DATE('04/22/2011', 'MM/DD/YYYY')
AND a.A_TYPE_SK = '100'
AND (a.A_REC_CDE = '0' OR a.A_REC_CDE = '1')
ORDER BY a.A_TM ASC) z
) WHERE rn <= '100' AND rn >= '1'

When i execute the above query in TOAD, i see 16 columns, but when i execute this through java i only get 10 columns and the highlighted columns are not showing up in the ResultsetMetaData

Output of ResultsetMetaData column names

RN
A_NAME
A_TYPE
A_SUB_TYPE
LOGIN_ID
E_ID
A_TM
INIT_BY
S_CDE
USER_TYPE

In-short im getting only the results from MASTER_TABLE , not the ones from USER_MET & USER_MET_ADDL_INFO. Anybody knows why?

The exception which i am getting is

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call A_PKG.GET_DETAIL_A(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: Invalid column name



0
johnportaldev
Asked:
johnportaldev
1 Solution
 
sdstuberCommented:
A_PKG.GET_DETAIL_A  - this isn't a SQL statement, it's pl/sql call

maybe the procedure doesn't have the same query you are executing in Toad
0
 
johnportaldevAuthor Commented:
omg that was a stupid error, i was calling a difft proc with the same set of params.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now