Toad World® Forums

Linked Table Properties


#1

I have created a table in Oracle. I then update that table by an INSERT INTO statement. When I link the table to MS Access, it does not retain the Oracle table properties. Am I doing something incorrect when I insert the data? Here is the SQL:

INSERT INTO NETREVMODQ

SELECT DISTINCT VISIT_CHARGES.SITE_ID,

VISIT_CHARGES.FACILITY_ID,

INSURANCE_MASTER.ENTITY,

MST_CHARGES.GL_DEPT,

VISIT_CHARGES.CHARGE_NO,

MST_CHARGES.CHARGE_DESC,

VISITS.PATIENT_CLASS,

INSURANCE_MASTER.PAYOR,

INSURANCE_MASTER.PAYOR_DESCRIPTION,

SUM (VISIT_CHARGES.CHARGE_QTY) AS QTY,

SUM (VISIT_CHARGES.CHARGE_AMT) AS CHRGS,

VISIT_CHARGES.REV_CODE

FROM (((FIN.VISIT_CHARGES VISIT_CHARGES

INNER JOIN FIN.MST_CHARGES MST_CHARGES

ON (VISIT_CHARGES.SITE_ID = MST_CHARGES.SITE_ID)

AND (VISIT_CHARGES.CHARGE_NO = MST_CHARGES.CHARGE_NO))

INNER JOIN FIN.VISITS VISITS

ON (VISIT_CHARGES.SITE_ID = VISITS.SITE_ID)

AND (VISIT_CHARGES.ACCOUNT_NO = VISITS.ACCOUNT_NO)

AND (VISIT_CHARGES.FACILITY_ID = VISITS.FACILITY_ID))

INNER JOIN FIN.VISIT_INSURANCE VISIT_INSURANCE

ON (VISIT_INSURANCE.SITE_ID = VISITS.SITE_ID)

AND (VISIT_INSURANCE.ACCOUNT_NO = VISITS.ACCOUNT_NO)

AND (VISIT_INSURANCE.FACILITY_ID = VISITS.FACILITY_ID))

INNER JOIN EDW.INSURANCE_MASTER INSURANCE_MASTER

ON (VISIT_INSURANCE.FACILITY_ID = INSURANCE_MASTER.EDW_ID)

AND (VISIT_INSURANCE.INSURANCE_CODE =

INSURANCE_MASTER.INSURANCE_CODE)

WHERE (VISIT_INSURANCE.INSURANCE_SEQ = 1)

AND (VISITS.DISCHARGE_DATE BETWEEN TO_DATE (‘2014-07-01 00:00:00’,

‘yyyy/mm/dd hh24:mi:ss’)

AND TO_DATE (‘2014-12-31 00:00:00’,

‘yyyy/mm/dd hh24:mi:ss’))

AND (VISIT_CHARGES.POSTING_DATE BETWEEN TO_DATE (

‘2014-07-01 00:00:00’,

‘yyyy/mm/dd hh24:mi:ss’


#2

What table properties do you mean? Do you get nothing when you execute select from Access?

Have you committed after Insert? Do you execute INSERT using TDP? Do you get data when you select from the Oracle table using a DIFFERENT Editor?

Regards

Aleksey


#3

The Access table properties all change to short text. The table properties in Oracle are mostly numeric. I am linking into the table and I do get the data, it is just not in the correct format.

I set the commit to auto when I run the insert into command.

I used TDP to create the table and I use TDP to insert the data.

I do not have a different editor, so I cannot test that.

Thanks,

Paul


#4

I reviewed the script to make the table and it was using NUMBER(*,0) for each of the integer fields. I changed that to NUMBER(28,10) for the integer fields and everything is correct when I link the table in Access.

Will changing the precision and scale create a problem for me?


#5

Hi Paul,

It looks like when you mention “table properties” your talk about column data types. Oracle and Access column types have to correspond to each other. You cannot store string in a column with numeric type. In your case changing the precision and/or scale should not create a problem until your data fits into the new type.

It’s not relevant now because you solve your issue but just in case when I asked for the different editor I meant opening another SQL Editor Windows in Toad.

Sorry that I did not explain it clearly.

Regards

Aleksey


#6

Yes, you are correct, I was talking about column data types. The strange thing that was happening was the column data type in Oracle (according to TDP) was integer. I would then insert the data using an insert into command. When I linked it in Access the column data type would change to short text.

Well, at least it has been fixed. Thanks for your help.

Paul