Appending Oracle SQL to table

[James C.]

I have the following code....I am trying to get it to automatically append to
a table that I already have setup within my db. This is Oracle SQL, so i
don't know how to get it to append. Any ideas. Right now it creates a
pass-through query and then displays the results. I want it to automatically
dump the results in my table. I tried INSERT INTO, but it doesn't seem to
work with Oracle.

------------------------------------------------------
(CODE)

Public Function createCBR()

Dim db As Database
Dim qd As QueryDef

Set db = CurrentDb

SDate = OracleDateFormat(Forms!frmMAIN!txtDateStart)

db.QueryDefs.Delete "CBR_SQL"
Set qd = db.CreateQueryDef("CBR_SQL")

qd.Connect = "Connection Script....."

sql = "SELECT C.DEPARTURE_MONTH, B.CD_CODE, B.MARKET,
B.CARRIER,B.BOOKINGS,C.SYSTEMSHARE, B.HpQsiShare " & _
"FROM " & _
"(SELECT DEPARTURE_MONTH, MARKET,
(SUM(HP_SYSTEM_EXPECTED_BOOKINGS)/SUM(TOTAL_NET_BOOKINGS)) SYSTEMSHARE " & _
"FROM AWARDS.SPA_AGENCY_AWARD_MKT " & _
"WHERE TOTAL_NET_BOOKINGS > 0 " & _
"AND DEPARTURE_MONTH = '" & SDate & "' " & _
"GROUP BY MARKET, DEPARTURE_MONTH) C, " & _
"(SELECT TRUE_DEPARTURE_MONTH, CD_CODE, MARKET,
DECODE(TRUE_MARKETING_CARRIER,
'HP','HP','AA','AA','AS','AS','CO','CO','DL','DL','NW','NW','UA','UA','US','US','WN','WN','OA')
CARRIER, SUM(TRUE_NET_BOOKING_COUNT) BOOKINGS, HpQsiShare " & _
"FROM CRS.MONTHLY_SUMMARY_P, MARS.ARC_OVR_ACCT, " & _
"(SELECT ALPHA, (SUM(QSI)/SUM(MARKET_QSI)) HpQsiShare " & _
"FROM MARS.HP_MARKETS, crs.QSI_SUMMED_V, crs.QSI " & _
"WHERE MARKET = ALPHA " & _
"AND CRS.QSI_SUMMED_V.ORG = CRS.QSI.ORG " & _
"AND CRS.QSI_SUMMED_V.DES = CRS.QSI.DES " & _
"AND CRS.QSI_SUMMED_V.YYMO = CRS.QSI.YYMO " & _
"AND CARRIER = 'HP' " & _
"AND CRS.QSI_SUMMED_V.YYMO = '" & SDate & "' " & _
"GROUP BY ALPHA) A " & _
"WHERE AGENCY_NUMBER_7 = AGENCY_NUMBER " & _
"AND MARKET = ALPHA " & _
"AND YEAR_MONTH = '01-JAN-05' " & _
"AND CD_CODE IN
('AMEX','AXRP','CTNW','CTNA','WTP1','WTPA','UNWD','NAVG') " & _
"AND TRUE_DEPARTURE_MONTH = '" & SDate & "' " & _
"GROUP BY CD_CODE, MARKET, HpQsiShare, DECODE(TRUE_MARKETING_CARRIER,
'HP','HP','AA','AA','AS','AS','CO','CO','DL','DL','NW','NW','UA','UA','US','US','WN','WN','OA'), TRUE_DEPARTURE_MONTH) B "
sql = sql & "WHERE C.MARKET = B.MARKET " & _
"AND C.DEPARTURE_MONTH = B.TRUE_DEPARTURE_MONTH"

qd.sql = sql

qd.ReturnsRecords = True
qd.Close

db.Close

 

[James C.]

Nevermind, figured it out.

 

[Tim Ferguson]

. This is Oracle SQL, so i
don't know how to get it to append. Any ideas.

Sorry: I don't have the time to decode the long, unformatted, and
uncommented code. Still, I think the way to do what you want is


' contrary to the Access Query Window, SQL is human readable!
' You may need ISO syntax rather than Jet although this simple
' example is the same in both
'
' Note correct use of the INTO clause to create a make-table
' query.
'
' Failing that, use
' INSERT INTO Destination (FieldList)
'
isoSQL = "SELECT FieldList " & vbNewLine & _
"INTO NewTable " & vbNewLine & _
"FROM SourceData " & vbNewLine & _
"WHERE Criterion =1"

' Always check what you are sending until you have the
' debugging nailed down tight: you may prefer a Debug.Print
' with a Stop statement in order to inspect it at your
' leisure
'
Debug.Assert vbYes = MsgBox(isoSQL, vbYesNo,"Is This OK?")

' now carry out the procedure. I could not see anywhere in
' your code that you actually execute the statement. You can
' use a QueryDef if that's what floats your boat, but simply
' executing the thing works more simply.
'
db.Execute isoSQL, dbFailOnError


Hope that helps



Tim F