Hi, Tim!
>
>
> The driving_site deal worked out great, but I wonder if you might have a
> recommendation regarding derived tables in the same context. Maybe I'm
> missing something major here, but the sql below works quite well in SQL
> Server via linked server (of course, what you see below is modified for
> Oracle syntax with the links).
>
> Unless there's another way to write the sql, I have to reference the
server
> multiple times due to the derived table usage. I tried rewriting the sql
so
> that I only hit the server once (getting all the fields I need), and then
> build the query layers on that, but that doesn't work (at least, not the
way
> I tried it). Derived tables work great, but once you build a query on top
> of it, it is no longer able to be referenced (so far as I can tell).
>
> The one thing I have thought of is making a temporary table and build the
> queries on that, but I would like to avoid having to go that route.
> Besides, I'm still annoyed by the fact that this runs fine in SQL Server
but
> doesn't easily translate in Oracle, which leads me to believe there must
be
> another way.
>
> I'm not asking for the SQL to be rewritten, and it's kind of messy to
follow
> anyway, so I'm mainly after some conceptual direction here. As you can
see
> below, I had to reference the driving_site four times, and that's
obviously
> not what I want to do. But again, by going the other route with only a
> single reference, I lose the derived table reference after building a
second
> derived table on it. In other words, it appears that a derived table can
> only be reference once... if a query has been built on top of it.
>
>
> Any thoughts would be greatly appreciated!
>
>
> Thanks,
>
>
> Rick
>
>
>
>
>
>
> SELECT DISTINCT
> iq3.custid,
> iq3.GCI,
> iq3.STATEMENTDATE,
> iq3.SAVEDATE,
> stmt.NETSALES,
> stmt.CUSTOMERNAME,
> CITY,
> STATE,
> NAICS,
> STATEMENTMONTHS,
> stmt.STATEMENTYEAR,
> accountant,
> Acctsrecother,
> acctsrectrade,
> aftertaxexpense,
> aftertaxincome,
> cash,
> afttaxnoncashexp,
> amortexpense,
> auditmethod,
> baddebtexpense,
> baddebtreserve,
> capexact,
> cashandequivs,
> changeininv,
> commonstock,
> costofgoodssold,
> deprecexpense,
> grossprofit,
> acctspaytrade,
> dividends,
> dividendscommon,
> ebida,
> ebitda,
> fingoodsinv,
> fixedassets,
> fundeddebt,
> intangibles,
> interestexpense,
> longtermdebt,
> marketablesec,
> netfixedassets,
> netprofit,
> nettradeacctsrec,
> profitbeforetax,
> rawmaterialsinv,
> retainedearnings,
> seniorfundeddebt,
> statementtype,
> tangiblenetworth,
> totalassets,
> totalinventory,
> totalliabs,
> totalnetworth
>
> FROM
>
>
> (
> SELECT /*+DRIVING_SITE(FCSUMMARYCUST)*/ cust.CUSTID,MAX(Q.STATEMENTDATE)
AS
> STATEMENTDATE,MAX(Q.SAVEDATE) AS SAVEDATE,MAX(Q.GCI) AS GCI
> FROM
>
> (
>
>
> SELECT /*+DRIVING_SITE(FCSUMMARYCUST)*/ iq2.GCI,MAX(iq2.STATEMENTDATE) AS
> STATEMENTDATE,MAX(SAVEDATE) AS SAVEDATE
>
> FROM
>
> (
>
> SELECT /*+DRIVING_SITE(FCSUMMARYCUST)*/
> iq1.CUSTID,iq1.STATEMENTDATE,cust.GCI,SAVEDATE
>
> FROM
>
> (SELECT /*+DRIVING_SITE(FCSUMMARYCUST)*/ CUSTID,MAX(STATEMENTDATE) AS
> STATEMENTDATE FROM [Email address protected] stmt
> WHERE TO_DATE(STATEMENTDATE,'YYYY-MM-DD') <=
> TO_DATE('2004-03-31','YYYY-MM-DD')
> AND STATEMENTMONTHS = 12
> AND STATEMENTTYPE = 'Annual'
> GROUP BY CUSTID) iq1,[Email address protected] cust,[Email address protected]
> security
> WHERE iq1.CUSTID = cust.CUSTID
> AND iq1.CUSTID = security.CUSTOMERID
> AND LENGTH(GCI) = 9
> AND GCI <> 'Dupl. GCI'
> AND GCI <> 'dupl. gci'
> AND GCI <> 'Duplicate') iq2
> GROUP BY iq2.GCI) Q,[Email address protected] stmt,[Email address protected]
> cust,[Email address protected] security
> WHERE Q.STATEMENTDATE = stmt.STATEMENTDATE
> AND Q.GCI = cust.GCI
> AND Q.SAVEDATE = security.SAVEDATE
> AND cust.CUSTID = stmt.CUSTID
> GROUP BY cust.CUSTID) iq3,[Email address protected]
> stmt,[Email address protected] cust,[Email address protected] security
> WHERE iq3.CUSTID = stmt.CUSTID
> AND iq3.STATEMENTDATE = stmt.STATEMENTDATE
> AND iq3.CUSTID = cust.CUSTID
> AND iq3.GCI = cust.GCI
> AND iq3.CUSTID = security.CUSTOMERID
> AND iq3.SAVEDATE = security.SAVEDATE
> AND STATEMENTMONTHS = 12

>


Oracle LazyDBA home page