AnsweredAssumed Answered

I am sorry - Another Newbie NSQL Question

Question asked by MaxPemberton on Apr 15, 2010
Latest reply on Apr 19, 2010 by Dave
Hello,I have an SQL that works in AquaData Studio and I have tried, using the documents and forum posts, to wrap it up in a little NSQL blanket but after numerous attepmts I wonder if anyone can spot the obvious mistake I am making. I even stopped using table alias on this version in case that was causing the problem, so I apolgise if the strings are longer than they need to be...My aim is to get an audit trail visble on a portlet to some of the PMO users who are interested in the risk audit table.  ThanksMax.   SQL I AM USING: SELECT                       "CMN_AUDITS"."OBJECT_CODE",                       "CMN_AUDITS"."OBJECT_ID",                       "CMN_AUDITS"."ATTRIBUTE_CODE",                       "CMN_AUDITS"."RAW_VALUE_BEFORE",                       "CMN_AUDITS"."RAW_VALUE_AFTER",                       "CMN_AUDITS"."OPERATION_CODE",                       "CMN_AUDITS"."CREATED_DATE",                       "SRM_RESOURCES"."UNIQUE_NAME",                       "RIM_RISKS_AND_ISSUES"."NAME",                       "RIM_RISKS_AND_ISSUES"."CATEGORY_TYPE_CODE",                       "INV_INVESTMENTS"."NAME",                       "INV_INVESTMENTS"."CODE"FROM                       "MUN160P"."CMN_AUDITS" "CMN_AUDITS"                                             INNER JOIN "MUN160P"."RIM_RISKS_AND_ISSUES" "RIM_RISKS_AND_ISSUES"                                             ON "CMN_AUDITS"."OBJECT_ID" = "RIM_RISKS_AND_ISSUES"."ID"                                                                   INNER JOIN "MUN160P"."INV_INVESTMENTS" "INV_INVESTMENTS"                                                                   ON "INV_INVESTMENTS"."ID" = "RIM_RISKS_AND_ISSUES"."PK_ID"                                                                                         INNER JOIN "MUN160P"."SRM_RESOURCES" "SRM_RESOURCES"                                                                                         ON "SRM_RESOURCES"."USER_ID" = "CMN_AUDITS"."CREATED_BY"     NSQL?---------------------------------------------------------------------------------------------------------     SELECT  @SELECT:DIM:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.OBJECT_ID :OBJ_ID@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.OBJECT_CODE :OBJ@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.ATTRIBUTE_CODE :ATTR_NAME@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.RAW_VALUE_BEFORE :B_FORE@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.RAW_VALUE_AFTER :AFTER@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.OPERATION_CODE :ACTION_TYPE@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: CMN_AUDITS.CREATED_DATE :ACTION_DATE@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: SRM_RESOURCES.UNIQUE_NAME :WHODUNNIT@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: RIM_RISKS_AND_ISSUES.NAME :RISK_NAM@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: RIM_RISKS_AND_ISSUES. CATEGORY_TYPE_CODE :RISK_CAT@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: INV_INVESTMENTS.NAME :PRJ_TITLE@  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XOAUDIT: INV_INVESTMENTS.CODE :PRJ_NUMBR@     FROM  ( SELECT                       CMN_AUDITS.OBJECT_CODE,                     CMN_AUDITS.OBJECT_ID,                     CMN_AUDITS.ATTRIBUTE_CODE,                     CMN_AUDITS.RAW_VALUE_BEFORE,                     CMN_AUDITS.RAW_VALUE_AFTER,                     CMN_AUDITS.OPERATION_CODE,                     CMN_AUDITS.CREATED_DATE,                     SRM_RESOURCES.UNIQUE_NAME,                     RIM_RISKS_AND_ISSUES.NAME,                     RIM_RISKS_AND_ISSUES.CATEGORY_TYPE_CODE,                     INV_INVESTMENTS.NAME,                     INV_INVESTMENTS.CODEFROM                       CMN_AUDITS CMN_AUDITS                                             INNER JOIN RIM_RISKS_AND_ISSUES RIM_RISKS_AND_ISSUES                                             ON CMN_AUDITS. OBJECT_ID = RIM_RISKS_AND_ISSUES. ID                                                                   INNER JOIN INV_INVESTMENTS INV_INVESTMENTS                                                                   ON INV_INVESTMENTS.ID = RIM_RISKS_AND_ISSUES.PK_ID                                                                                         INNER JOIN SRM_RESOURCES SRM_RESOURCES                                                                                         ON SRM_RESOURCES.USER_ID = CMN_AUDITS.CREATED_BY    ) sql_code     WHERE               @FILTER@     --------------------------------------------------------------------------------------------  It gives me an error when I try to preview it of: NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "INV_INVESTMENTS"."CODE": invalid identifier SQL Text: SELECT CMN_AUDITS.OBJECT_ID OBJ_ID ,CMN_AUDITS.OBJECT_CODE OBJ ,CMN_AUDITS.ATTRIBUTE_CODE ATTR_NAME ,CMN_AUDITS.RAW_VALUE_BEFORE B_FORE ,CMN_AUDITS.RAW_VALUE_AFTER AFTER ,CMN_AUDITS.OPERATION_CODE ACTION_TYPE ,CMN_AUDITS.CREATED_DATE ACTION_DATE ,SRM_RESOURCES.UNIQUE_NAME WHODUNNIT ,RIM_RISKS_AND_ISSUES.NAME RISK_NAM ,RIM_RISKS_AND_ISSUES. CATEGORY_TYPE_CODE RISK_CAT ,INV_INVESTMENTS.NAME PRJ_TITLE ,INV_INVESTMENTS.CODE PRJ_NUMBR FROM ( SELECT CMN_AUDITS.OBJECT_CODE , CMN_AUDITS.OBJECT_ID , CMN_AUDITS.ATTRIBUTE_CODE , CMN_AUDITS.RAW_VALUE_BEFORE , CMN_AUDITS.RAW_VALUE_AFTER , CMN_AUDITS.OPERATION_CODE , CMN_AUDITS.CREATED_DATE , SRM_RESOURCES.UNIQUE_NAME , RIM_RISKS_AND_ISSUES.NAME , RIM_RISKS_AND_ISSUES.CATEGORY_TYPE_CODE , INV_INVESTMENTS.NAME , INV_INVESTMENTS.CODE FROM CMN_AUDITS CMN_AUDITS INNER JOIN RIM_RISKS_AND_ISSUES RIM_RISKS_AND_ISSUES ON CMN_AUDITS. OBJECT_ID = RIM_RISKS_AND_ISSUES. ID INNER JOIN INV_INVESTMENTS INV_INVESTMENTS ON INV_INVESTMENTS.ID = RIM_RISKS_AND_ISSUES.PK_ID INNER JOIN SRM_RESOURCES SRM_RESOURCES ON SRM_RESOURCES.USER_ID = CMN_AUDITS.CREATED_BY ) sql_code WHERE 1=? and 1=1 .

Outcomes