We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORACLE STORED PROC QUESTION - invalid column name

Medium Priority
1,086 Views
Last Modified: 2012-05-11
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



Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
omg that was a stupid error, i was calling a difft proc with the same set of params.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.