Error creating SQL Command - Database Expert

[Former Member]

I am creating a command however when I go to save the SQL I get two errors.

"Failed to retrieve data from database" and "Failed to execute SQL Statement"

I am working on an Oracle database.

Why would these errors be occuring?

[Former Member · Accepted solution]

Hi,

can you send the query.

Regards,

Sathish

[Former Member · Accepted solution]

SELECT

"CI_ACCT_PER"."ACCT_ID", "CI_ACCT_PER"."ACCT_REL_TYPE_CD", "CI_ACCT_PER"."PER_ID", "CI_ACCT_PER"."MAIN_CUST_SW", "CI_ACCT_PER"."FIN_RESP_SW", "CI_ACCT"."ACCT_ID",

"CI_PER"."PER_OR_BUS_FLG", "CI_PER"."CITY", "CI_PER"."POSTAL", "CI_PER"."GEO_CODE", "CI_PER"."STATE", "CI_PER"."COUNTRY", "CI_PER_NAME"."PER_ID", "CI_PER_NAME"."ENTITY_NAME", "CI_PER_NAME"."NAME_TYPE_FLG", "CI_PER_NAME"."PRIM_NAME_SW",

"CI_SA"."SA_ID", "CI_SA"."SA_TYPE_CD", "CI_SA"."SA_STATUS_FLG", "CI_SA_REL"."SA_REL_ID", "CI_SA_REL"."SA_ID", "CI_SA_REL"."SA_REL_STATUS_FLG", "CI_SA_REL"."SA_REL_USAGE_FLG", "CI_SA_REL"."SPR_CD", "CI_SA_REL"."SA_REL_TYPE_CD", "CI_SA"."CHAR_PREM_ID",

"CI_SPR"."SA_ID", "CI_SPR"."PER_ID",

"CI_PREM"."ADDRESS1", "CI_PREM"."PREM_ID",

"CI_SA_RS_HIST"."EFFDT", "CI_SA_RS_HIST"."RS_CD"

FROM ((((((("CISADM"."CI_SA" "CI_SA" LEFT OUTER JOIN "CISADM"."CI_ACCT" "CI_ACCT" ON "CI_SA"."ACCT_ID"="CI_ACCT"."ACCT_ID")

LEFT OUTER JOIN "CISADM"."CI_SA_REL" "CI_SA_REL" ON "CI_SA"."SA_ID"="CI_SA_REL"."SA_ID")

LEFT OUTER JOIN "CISADM"."CI_PREM" "CI_PREM" ON "CI_SA"."CHAR_PREM_ID"="CI_PREM"."PREM_ID")

LEFT OUTER JOIN "CISADM"."CI_SA_RS_HIST" "CI_SA_RS_HIST" ON "CI_SA"."SA_ID"="CI_SA_RS_HIST"."SA_ID")

LEFT OUTER JOIN "CISADM"."CI_ACCT_PER" "CI_ACCT_PER" ON "CI_ACCT"."ACCT_ID"="CI_ACCT_PER"."ACCT_ID")

LEFT OUTER JOIN "CISADM"."CI_PER" "CI_PER" ON "CI_ACCT_PER"."PER_ID"="CI_PER"."PER_ID")

LEFT OUTER JOIN "CISADM"."CI_PER_NAME" "CI_PER_NAME" ON "CI_PER"."PER_ID"="CI_PER_NAME"."PER_ID")

LEFT OUTER JOIN "CISADM"."CI_SPR" "CI_SPR" ON "CI_SA_REL"."SPR_CD"="CI_SPR"."SPR_CD"

WHERE "CI_PER_NAME"."PRIM_NAME_SW"='Y'

AND "CI_ACCT_PER"."MAIN_CUST_SW"='Y'

AND "CI_ACCT_PER"."FIN_RESP_SW"='Y'

AND "CI_SA_REL"."SA_REL_STATUS_FLG"='A'

and "CI_SA_RS_HIST"."EFFDT"<date(2009,1,1)

ORDER BY "CI_SA_REL"."SPR_CD", "CI_SA"."SA_ID", "CI_ACCT_PER"."ACCT_ID", "CI_ACCT_PER"."PER_ID"

[Former Member · Accepted solution]

Hi,

if you are using orcale add command

Replace the following part "CI_SA_RS_HIST"."EFFDT"<date(2009,1,1) with

"CI_SA_RS_HIST"."EFFDT"<sysdate or "CI_SA_RS_HIST"."EFFDT"< TO_DATE('01-JAN-2009')

Regards,

Sathish

[Former Member · Accepted solution]

Thank you so much that is brilliant. I do feel a bit daft now tho.