Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: NEED HELP WITH MY SQL QUERY COMMAND Post Reply Post New Topic
Author Message
skreczko
Newbie
Newbie


Joined: 18 Oct 2010
Online Status: Offline
Posts: 7
Quote skreczko Replybullet Topic: NEED HELP WITH MY SQL QUERY COMMAND
    Posted: 23 Feb 2011 at 5:00am

Hello everyone,

I wrote this query, but I keep getting this error. Does anyone know what Im doing wrong?

 

Failed to retrieve data from database.

Details 42S22:[IBM][CLI Driver][DB2/6000} SQL0206N " DATAAUDIT.KEY_REPRESENTATION" is not valid in the context where it is used. SQLSTATE = 42703

DATAbase Vendor Code:-206]

 

 

My Query

SELECT

DATAAUDIT.KEY_REPRESENTATION

 , CAST(SUBSTRING(DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION)+1,(charindex('|',DATAAUDIT.KEY_REPRESENTATION) - charindex('=',DATAAUDIT.KEY_REPRESENTATION)-1)) as BIGINT) as MEMBER_NBR

, CAST(SUBSTRING(DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION)+1)+1,LEN(DATAAUDIT.KEY_REPRESENTATION)) AS BIGINT)  as LOAN_NBR

, DATAAUDIT.FOCUS_TELLER_ID

, LOAN. RATE

, LOAN. Loan_Type

, PRODUCT.PRODUCT_NAME

, LOAN.Rate_Delta as Manual_RATE

, DATAAUDIT.ACTIVITY_ID

, DATAAUDIT. TABLE_NAME

, DATAAUDIT. ADDTIMESTAMP

, DATAAUDIT.COLUMN_NAME

, DATAAUDIT.BEFORE_REPRESENTATION

, DATAAUDIT.AFTER_REPRESENTATION

, DATAAUDIT.IS_FINANCIAL

, DATAAUDIT.WORKSTATION

, DATAAUDIT.ACTION_TYPE

 

FROM DB2INST1.DATAAUDIT,

 DB2INST1.LOAN,

 DB2INST1.ACCOUNT,

 DB2INST1.PRODUCT

Where  CAST(SUBSTRING(DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION)+1,(charindex('|',DATAAUDIT.KEY_REPRESENTATION) - charindex('=',DATAAUDIT.KEY_REPRESENTATION)-1)) as BIGINT) = Loan.MEMBER_NBR and

CAST(SUBSTRING(DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION,charindex('=',DATAAUDIT.KEY_REPRESENTATION)+1)+1,LEN(DATAAUDIT.KEY_REPRESENTATION)) AS BIGINT)  = Loan.LOAN_NBR and

Loan.MEMBER_NBR = ACCOUNT.MEMBER_NBR and

Loan.LOAN_NBR = ACCOUNT.ACCOUNT_NBR and

ACCOUNT.PRODUCT_CODE = PRODUCT.PRODUCT_CODE and

ACCOUNT.PRODUCT_CATEGORY_CODE = PRODUCT.PRODUCT_CATEGORY_CODE

and ACCOUNT.PRODUCT_CLASS_CODE = PRODUCT.PRODUCT_CLASS_CODE and

Table_Name = 'Loan'

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 03 Mar 2011 at 3:50am
Can you run this query in a tool outside of Crystal.  I'm not sure what's available for DB2, but for Oracle I would use Toad or for SQL Server I would use SQL Server Management Studio.  This is a database error, so there's something invalid in your query that is not necessarily Crystal related.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.