Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

inline view or other alternatives?

Status
Not open for further replies.
Maven4Champ

Maven4Champ

Technical User
Jun 16, 2004
154
Greetings, I am writing a sql statement that pulls data from one database and will use that data to update another database. With that said, I am having trouble getting the statement to return expected results. My problem is this. An ASSETID can be stored in a CREDITASSET table or a QUOTEDASSET table. I need to join to both these tables which stores another key which joins to other EQUIPMEMNT tables that returns basic equipment information on the asset. My problem is, the asset id is either in CREDITASSET or QUOTEDASSET but not in both, thus I need an outer join on the two tables where they connect to EQUIPMENT. This obviously returns an error from TOAD stating that one table can have at the most, one outer join.

I have done some research and found an alternative is using inline views however I attempted this and failed miserably (I am a self-admitted n00b). With that said, here is my SQL statement - any help that can be provided is much appreciated. The lines where EQUIPMENT.ASSETID = QUOTEDASSET.ASSETID and then the next line are where my problem is it. How do I achieve the desired results with good performance yet still retrive records from either table.

SELECT DISTINCT
DBSCHEMA.CREDITAPPLICATION.CREDITAPPLICATIONID,
DBSCHEMA.CREDITAPPSTATUS.NAME,
DBSCHEMA.DEALER.DEALERCODE,
DBSCHEMA.DEALER.DEALERNAME,
DBSCHEMA.REGIONALOFFICE.NAME,
DBSCHEMA.DIVISION.DESCRIPTION,
DBSCHEMA.DIVISION.NAME,
DBSCHEMA.PARTY.PARTYID,
DBSCHEMA.PARTY.CCAN,
DBSCHEMA.PARTYNAME.NAME,
DBSCHEMA.DECISIONREQUEST.REQUESTEDBY,
DBSCHEMA.DECISIONREQUEST.TIMESTAMP,
DBSCHEMA.DECISIONREQUEST.EQUIPMENTPRICE,
DBSCHEMA.DECISIONREQUEST.FINANCEDAMOUNT,
DBSCHEMA.DECISIONREQUEST.DOWNPAYMENT,
DBSCHEMA.FINANCETYPE.NAME,
DBSCHEMA.INDUSTRYTYPE.NAME,
DBSCHEMA.DECISIONSTATUS.NAME,
(CASE
WHEN (DBSCHEMA.DECISIONREQUEST.AUTO = 1)
THEN 'Y'
ELSE ''
END
) ATTRIB_09,
DBSCHEMA.DECISIONREQUEST.TERM,
DBSCHEMA.CREDITDECISION.DECISIONBY,
DBSCHEMA.CREDITDECISION.TIMESTAMP,
DBSCHEMA.CREDITDECISIONCOMMENT.COMMENTTEXT,
DBSCHEMA.CREDITDECISION.OTHERADDITIONALCONDITIONTYPE,
DBSCHEMA.EQUIPMENTTYPE.NAME,
DBSCHEMA.ASSETMODEL.NAME,
DBSCHEMA.ASSETUNIT.SERIALNUMBER,
DBSCHEMA.EQUIPMENT.YEAR,
DBSCHEMA.MANUFACTURER.NAME
FROM
DBSCHEMA.CREDITAPPLICATION,
DBSCHEMA.CREDITAPPSTATUS,
DBSCHEMA.DEALER,
DBSCHEMA.REGIONALOFFICE,
DBSCHEMA.DECISIONREQUEST,
DBSCHEMA.INDUSTRYTYPE,
DBSCHEMA.FINANCETYPE,
DBSCHEMA.PARTY,
DBSCHEMA.PARTYNAME,
DBSCHEMA.DECISIONSTATUS,
DBSCHEMA.CREDITDECISIONCOMMENT,
DBSCHEMA.CREDITDECISION,
DBSCHEMA.DIVISION,
DBSCHEMA.CREDITASSET,
DBSCHEMA.QUOTEDASSET,
DBSCHEMA.QUOTE,
DBSCHEMA.EQUIPMENT,
DBSCHEMA.EQUIPMENTTYPE,
DBSCHEMA.ASSETMODEL,
DBSCHEMA.ASSETUNIT,
DBSCHEMA.ASSETMODELTYPE,
DBSCHEMA.MANUFACTURER
WHERE
CREDITAPPLICATION.CREDITAPPSTATUSID = CREDITAPPSTATUS.CREDITAPPSTATUSID
AND CREDITAPPLICATION.DEALERID = DEALER.DEALERID
AND CREDITAPPLICATION.REGIONALOFFICEID = REGIONALOFFICE.REGIONALOFFICEID
AND CREDITAPPLICATION.PARTYID = PARTY.PARTYID (+)
AND PARTY.PARTYNAMEID = PARTYNAME.PARTYNAMEID
AND CREDITAPPLICATION.CREDITAPPLICATIONID = DECISIONREQUEST.CREDITAPPLICATIONID (+)
AND CREDITAPPLICATION.INDUSTRYTYPEID = INDUSTRYTYPE.INDUSTRYTYPEID
AND DECISIONREQUEST.DECISIONREQUESTID = CREDITDECISION.DECISIONREQUESTID (+)
AND DECISIONREQUEST.FINANCETYPEID = FINANCETYPE.FINANCETYPEID
AND CREDITDECISION.DECISIONID = DECISIONSTATUS.DECISIONSTATUSID (+)
AND CREDITDECISION.CREDITDECISIONID = CREDITDECISIONCOMMENT.CREDITDECISIONID (+)
AND CREDITAPPLICATION.DIVISIONID = DIVISION.DIVISIONID
AND DIVISION.DIVISIONID = '1'
AND CREDITAPPLICATION.CREDITAPPLICATIONID = QUOTE.CREDITAPPID (+)
AND CREDITAPPLICATION.CREDITAPPLICATIONID = CREDITASSET.CREDITAPPLICATIONID (+)
AND QUOTE.QUOTEID = QUOTEDASSET.QUOTEID (+)
--AND EQUIPMENT.ASSETID = QUOTEDASSET.ASSETID
AND EQUIPMENT.ASSETID = CREDITASSET.ASSETID
AND EQUIPMENT.TYPEID = EQUIPMENTTYPE.EQUIPMENTTYPEID
AND EQUIPMENT.MODELID = ASSETMODEL.ASSETMODELID
AND EQUIPMENT.EQUIPMENTID = ASSETUNIT.EQUIPMENTID (+)
AND ASSETMODEL.ASSETMODELTYPEID = ASSETMODELTYPE.ASSETMODELTYPEID
AND ASSETMODELTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
 
Sort by date Sort by votes
Maven,

When gathering data that exists in "one table or the other", and you need to work with those data as a single unit, that is usually a clear call for Oracle's UNION operator. The typical format is:
Code: 
SELECT <expression list>
  FROM <Table 1>
 WHERE <condition(s)>
[b]UNION[/b]
SELECT <expression list>
  FROM <Table 2>
 WHERE <condition(s)>;
The only major requirement is that the <expression list> of each SELECT must match in number of expressions and each expression in the first expression list must be compatible with the expression that matches positionally in the second expression list.

The result is as though it comes from a single table. And, luckily for you, the above UNION-Select may function as an in-line VIEW...as a FROM <query>...which you can alias and use as a joinable table.

Let us know if you have follow-up questions and if this helps to resolve your need.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Maven4Champ,
In addition to SantaMufasa's good advice, I would like to offer a bit of my own (unrelated to your outer join). Your code can be made easier to read (and debug) by using aliases and indention.
Code: 
SELECT  DISTINCT
        crdapp.creditapplicationid,
        castat.NAME,
        dealer.dealercode,
        dealer.dealername,
        regoff.NAME,
        division.description,
        division.NAME,
        party.partyid,
        party.ccan,
        partyname.NAME,
        decisionrequest.requestedby,
        decisionrequest.TIMESTAMP,
        decisionrequest.equipmentprice,
        decisionrequest.financedamount,
        decisionrequest.downpayment,
        financetype.NAME,
        industrytype.NAME,
        destat.NAME,
        (CASE WHEN (decisionrequest.auto = 1) 
              THEN 'Y' ELSE '' END) attrib_09,
        decreq.term,
        crddec.decisionby,
        crddec.TIMESTAMP,
        crdecm.commenttext,
        crddec.otheradditionalconditiontype,
        eqptyp.NAME,
        asstmd.NAME,
        asstun.serialnumber,
        eqpmnt.YEAR,
        mnfctr.NAME
FROM    dbschema.creditapplication       crdapp,
        dbschema.creditappstatus         castat,
        dbschema.dealer,
        dbschema.regionaloffice          regoff,
        dbschema.decisionrequest         decreq,
        dbschema.industrytype            indtyp,
        dbschema.financetype             fintyp,
        dbschema.party,
        dbschema.partyname               prtynm,
        dbschema.decisionstatus          destat,
        dbschema.creditdecisioncomment   crdecm,
        dbschema.creditdecision          crddec,
        dbschema.division,
        dbschema.creditasset             crasst,
        dbschema.quotedasset             qtasst,
        dbschema.quote,
        dbschema.equipment               eqpmnt,
        dbschema.equipmenttype           eqptyp,
        dbschema.assetmodel              asstmd,
        dbschema.assetunit               asstun,
        dbschema.assetmodeltype          amdtyp,
        dbschema.manufacturer            mnfctr
WHERE   crdapp.creditappstatusid    = castat.creditappstatusid
AND     crdapp.dealerid             = dealer.dealerid
AND     crdapp.regionalofficeid     = regoff.regionalofficeid
AND     crdapp.partyid              = party.partyid(+)
AND     party.partynameid           = prtynm.partynameid
AND     crdapp.creditapplicationid  = decreq.creditapplicationid(+)
AND     crdapp.industrytypeid       = indtyp.industrytypeid
AND     decreq.decisionrequestid    = crddec.decisionrequestid(+)
AND     decreq.financetypeid        = fintyp.financetypeid
AND     crddec.decisionid           = destat.decisionstatusid(+)
AND     crddec.creditdecisionid     = crdecm.creditdecisionid(+)
AND     crdapp.divisionid           = division.divisionid
AND     division.divisionid         = '1'
AND     crdapp.creditapplicationid  = quote.creditappid(+)
AND     crdapp.creditapplicationid  = crasst.creditapplicationid(+)
AND     quote.quoteid               = qtasst.quoteid(+)
AND     eqpmnt.assetid              = qtasst.assetid
AND     eqpmnt.assetid              = crasst.assetid
AND     eqpmnt.typeid               = eqptyp.equipmenttypeid
AND     eqpmnt.modelid              = asstmd.assetmodelid
AND     eqpmnt.equipmentid          = asstun.equipmentid(+)
AND     asstmd.assetmodeltypeid     = amdtyp.assetmodeltypeid
AND     amdtyp.manufacturerid       = mnfctr.manufacturerid
Is the DISTINCT required? It will have a negative impact on performance.

Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com
 
Upvote 0 Downvote
Thanks guys.

I took out the QUOTEDASSET statement and placed a UNION then copied down the original SQL statement and replaced the CREDITASSET with the QUOTEDASSET statement and all is well.

I tried it both with DISTINCT and without and ran about the same time.

Roughly 80 seconds for 155,000 rows. Would that be considered good performance or are there alternate ways to tweak is so that it runs faster?

Any advice on the performance part would be MUCH appreciated.

Thanks!
 
Upvote 0 Downvote
Maven,

I use DISTINCT only as a last resort to deal with unwanted duplicate rows. UNION automatically does a DISTINCT so its use in your case is absolutely extraneous.

Oracle's UNION, MINUS, and INTERSECT set operators, kilogram for kilogram, do more work, faster, and more efficiently than any other operators in Oracle...extremely tight and efficient code. You should need to worry about using those operators from a performance perspective.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Thanks so much guys for the advice. Here is my new code after removing the SELECT DISTINCT. You can notice my UNION between the two SELECT statements. The query returns 155,000+ rows in about 1 minute (give or take a few).

I will also, upon completion, use aliases for the table names and then refer to them in my statement but as far as writing it, I tend to do things the hard way which is easier on me by referring to the complete table name instead of an alias. Just a preference (maybe a bad one) but a preference nonetheless. Here is my code:

Code: 
SELECT 
DBSCHEMA.CREDITAPPLICATION.CREDITAPPLICATIONID 		   AS "QUOTE_NUM",
DBSCHEMA.CREDITAPPSTATUS.NAME						   AS "APP_STATUS",
DBSCHEMA.DEALER.DEALERCODE 							   AS "LOC",
DBSCHEMA.DEALER.DEALERNAME 							   AS "NAME",
DBSCHEMA.REGIONALOFFICE.NAME 						   AS "REGION",
DBSCHEMA.PARTY.PARTYID 								   AS "PARTY_ID",
DBSCHEMA.PARTY.CCAN 									   AS "CCAN",
DBSCHEMA.PARTYNAME.NAME 								   AS "X_VARCHAR5",
DBSCHEMA.DECISIONREQUEST.DECISIONREQUESTID			   AS "DEC_REQ_ID",
DBSCHEMA.DECISIONREQUEST.REQUESTEDBY 				   AS "REQUESTED_BY",
((TO_DATE('1969-12-31 19:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
+ (DBSCHEMA.DECISIONREQUEST.TIMESTAMP/86400000)) 	   AS "REQUEST_TIMESTAMP",
DBSCHEMA.DECISIONREQUEST.EQUIPMENTPRICE 				   AS "X_NUMBER10",
DBSCHEMA.DECISIONREQUEST.FINANCEDAMOUNT 				   AS "X_NUMBER11",
DBSCHEMA.DECISIONREQUEST.DOWNPAYMENT 				   AS "ATTRIB_22",
DBSCHEMA.FINANCETYPE.NAME 							   AS "X_VARCHAR10",
DBSCHEMA.INDUSTRYTYPE.NAME 							   AS "X_VARCHAR7",
DBSCHEMA.DECISIONSTATUS.NAME 						   AS "X_DECISION",
(CASE
WHEN (DBSCHEMA.DECISIONREQUEST.AUTO = 1)
THEN 'Y'
ELSE ''
END
) ATTRIB_09,
DBSCHEMA.DECISIONREQUEST.TERM 	   	   				   AS "X_NUMBER3",
DBSCHEMA.CREDITDECISION.DECISIONID					   AS "DECISION_ID",
DBSCHEMA.CREDITDECISION.DECISIONBY 					   AS "LOGIN",
((TO_DATE('1969-12-31 19:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
+ (DBSCHEMA.CREDITDECISION.TIMESTAMP/86400000)) 	   AS "ATTRIB_29",
DBSCHEMA.CREDITDECISIONCOMMENT.COMMENTTEXT 			   AS "X_DECISION_REASONS",
DBSCHEMA.CREDITDECISION.OTHERADDITIONALCONDITIONTYPE    AS "X_AGREEMENTS",
DBSCHEMA.EQUIPMENTTYPE.NAME							   AS "ATTRIB_01",
DBSCHEMA.ASSETMODEL.NAME								   AS "ATTRIB_04",
DBSCHEMA.ASSETUNIT.SERIALNUMBER						   AS "ATTRIB_06",
DBSCHEMA.EQUIPMENT.YEAR								   AS "ATTRIB_18",
DBSCHEMA.MANUFACTURER.NAME							   AS "ATTRIB_03"

FROM
DBSCHEMA.CREDITAPPLICATION,
DBSCHEMA.CREDITAPPSTATUS,
DBSCHEMA.DEALER,
DBSCHEMA.REGIONALOFFICE,
DBSCHEMA.DECISIONREQUEST,
DBSCHEMA.INDUSTRYTYPE,
DBSCHEMA.FINANCETYPE,
DBSCHEMA.PARTY,
DBSCHEMA.PARTYNAME,
DBSCHEMA.DECISIONSTATUS,
DBSCHEMA.CREDITDECISIONCOMMENT,
DBSCHEMA.CREDITDECISION,
DBSCHEMA.DIVISION,
DBSCHEMA.CREDITASSET,
DBSCHEMA.QUOTEDASSET,
DBSCHEMA.QUOTE,
DBSCHEMA.EQUIPMENT,
DBSCHEMA.EQUIPMENTTYPE,
DBSCHEMA.ASSETMODEL,
DBSCHEMA.ASSETUNIT,
DBSCHEMA.ASSETMODELTYPE,
DBSCHEMA.MANUFACTURER

WHERE
CREDITAPPLICATION.CREDITAPPSTATUSID = CREDITAPPSTATUS.CREDITAPPSTATUSID 
AND CREDITAPPLICATION.DEALERID = DEALER.DEALERID 
AND CREDITAPPLICATION.REGIONALOFFICEID = REGIONALOFFICE.REGIONALOFFICEID 
AND CREDITAPPLICATION.PARTYID = PARTY.PARTYID (+) 
AND PARTY.PARTYNAMEID = PARTYNAME.PARTYNAMEID 
AND CREDITAPPLICATION.CREDITAPPLICATIONID = DECISIONREQUEST.CREDITAPPLICATIONID (+) 
AND CREDITAPPLICATION.INDUSTRYTYPEID = INDUSTRYTYPE.INDUSTRYTYPEID 
AND DECISIONREQUEST.DECISIONREQUESTID =  CREDITDECISION.DECISIONREQUESTID (+) 
AND DECISIONREQUEST.FINANCETYPEID = FINANCETYPE.FINANCETYPEID 
AND CREDITDECISION.DECISIONID = DECISIONSTATUS.DECISIONSTATUSID (+)
AND CREDITDECISION.CREDITDECISIONID = CREDITDECISIONCOMMENT.CREDITDECISIONID (+) 
AND CREDITAPPLICATION.DIVISIONID = DIVISION.DIVISIONID 
AND DIVISION.DIVISIONID = '1'
AND CREDITAPPLICATION.CREDITAPPLICATIONID = QUOTE.CREDITAPPID (+) 
AND CREDITAPPLICATION.CREDITAPPLICATIONID = CREDITASSET.CREDITAPPLICATIONID (+) 
AND QUOTE.QUOTEID = QUOTEDASSET.QUOTEID (+) 
AND EQUIPMENT.ASSETID = CREDITASSET.ASSETID   
AND EQUIPMENT.TYPEID = EQUIPMENTTYPE.EQUIPMENTTYPEID
AND EQUIPMENT.MODELID = ASSETMODEL.ASSETMODELID
AND EQUIPMENT.EQUIPMENTID = ASSETUNIT.EQUIPMENTID (+)
AND ASSETMODEL.ASSETMODELTYPEID = ASSETMODELTYPE.ASSETMODELTYPEID  
AND ASSETMODELTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
AND DECISIONREQUEST.AUTO = '1'
AND DECISIONSTATUS.DECISIONSTATUSID IN ('1', '2', '4', '7', '')

UNION

SELECT 
DBSCHEMA.CREDITAPPLICATION.CREDITAPPLICATIONID 		   AS "QUOTE_NUM",
DBSCHEMA.CREDITAPPSTATUS.NAME						   AS "APP_STATUS",
DBSCHEMA.DEALER.DEALERCODE 							   AS "LOC",
DBSCHEMA.DEALER.DEALERNAME 							   AS "NAME",
DBSCHEMA.REGIONALOFFICE.NAME 						   AS "REGION",
DBSCHEMA.PARTY.PARTYID 								   AS "PARTY_ID",
DBSCHEMA.PARTY.CCAN 									   AS "CCAN",
DBSCHEMA.PARTYNAME.NAME 								   AS "X_VARCHAR5",
DBSCHEMA.DECISIONREQUEST.DECISIONREQUESTID			   AS "DEC_REQ_ID",
DBSCHEMA.DECISIONREQUEST.REQUESTEDBY 				   AS "REQUESTED_BY",
((TO_DATE('1969-12-31 19:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
+ (DBSCHEMA.DECISIONREQUEST.TIMESTAMP/86400000)) 	   AS "REQUEST_TIMESTAMP",
DBSCHEMA.DECISIONREQUEST.EQUIPMENTPRICE 				   AS "X_NUMBER10",
DBSCHEMA.DECISIONREQUEST.FINANCEDAMOUNT 				   AS "X_NUMBER11",
DBSCHEMA.DECISIONREQUEST.DOWNPAYMENT 				   AS "ATTRIB_22",
DBSCHEMA.FINANCETYPE.NAME 							   AS "X_VARCHAR10",
DBSCHEMA.INDUSTRYTYPE.NAME 							   AS "X_VARCHAR7",
DBSCHEMA.DECISIONSTATUS.NAME 						   AS "X_DECISION",
(CASE
WHEN (DBSCHEMA.DECISIONREQUEST.AUTO = 1)
THEN 'Y'
ELSE ''
END
) ATTRIB_09,
DBSCHEMA.DECISIONREQUEST.TERM 	   	   				   AS "X_NUMBER3",
DBSCHEMA.CREDITDECISION.DECISIONID					   AS "DECISION_ID",
DBSCHEMA.CREDITDECISION.DECISIONBY 					   AS "LOGIN",
((TO_DATE('1969-12-31 19:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
+ (DBSCHEMA.CREDITDECISION.TIMESTAMP/86400000)) 	   AS "ATTRIB_29",
DBSCHEMA.CREDITDECISIONCOMMENT.COMMENTTEXT 			   AS "X_DECISION_REASONS",
DBSCHEMA.CREDITDECISION.OTHERADDITIONALCONDITIONTYPE    AS "X_AGREEMENTS",
DBSCHEMA.EQUIPMENTTYPE.NAME							   AS "ATTRIB_01",
DBSCHEMA.ASSETMODEL.NAME								   AS "ATTRIB_04",
DBSCHEMA.ASSETUNIT.SERIALNUMBER						   AS "ATTRIB_06",
DBSCHEMA.EQUIPMENT.YEAR								   AS "ATTRIB_18",
DBSCHEMA.MANUFACTURER.NAME							   AS "ATTRIB_03"

FROM
DBSCHEMA.CREDITAPPLICATION,
DBSCHEMA.CREDITAPPSTATUS,
DBSCHEMA.DEALER,
DBSCHEMA.REGIONALOFFICE,
DBSCHEMA.DECISIONREQUEST,
DBSCHEMA.INDUSTRYTYPE,
DBSCHEMA.FINANCETYPE,
DBSCHEMA.PARTY,
DBSCHEMA.PARTYNAME,
DBSCHEMA.DECISIONSTATUS,
DBSCHEMA.CREDITDECISIONCOMMENT,
DBSCHEMA.CREDITDECISION,
DBSCHEMA.DIVISION,
DBSCHEMA.CREDITASSET,
DBSCHEMA.QUOTEDASSET,
DBSCHEMA.QUOTE,
DBSCHEMA.EQUIPMENT,
DBSCHEMA.EQUIPMENTTYPE,
DBSCHEMA.ASSETMODEL,
DBSCHEMA.ASSETUNIT,
DBSCHEMA.ASSETMODELTYPE,
DBSCHEMA.MANUFACTURER

WHERE
CREDITAPPLICATION.CREDITAPPSTATUSID = CREDITAPPSTATUS.CREDITAPPSTATUSID 
AND CREDITAPPLICATION.DEALERID = DEALER.DEALERID 
AND CREDITAPPLICATION.REGIONALOFFICEID = REGIONALOFFICE.REGIONALOFFICEID 
AND CREDITAPPLICATION.PARTYID = PARTY.PARTYID (+) 
AND PARTY.PARTYNAMEID = PARTYNAME.PARTYNAMEID 
AND CREDITAPPLICATION.CREDITAPPLICATIONID = DECISIONREQUEST.CREDITAPPLICATIONID (+) 
AND CREDITAPPLICATION.INDUSTRYTYPEID = INDUSTRYTYPE.INDUSTRYTYPEID 
AND DECISIONREQUEST.DECISIONREQUESTID =  CREDITDECISION.DECISIONREQUESTID (+) 
AND DECISIONREQUEST.FINANCETYPEID = FINANCETYPE.FINANCETYPEID 
AND CREDITDECISION.DECISIONID = DECISIONSTATUS.DECISIONSTATUSID (+)
AND CREDITDECISION.CREDITDECISIONID = CREDITDECISIONCOMMENT.CREDITDECISIONID (+) 
AND CREDITAPPLICATION.DIVISIONID = DIVISION.DIVISIONID 
AND DIVISION.DIVISIONID = '1'
AND CREDITAPPLICATION.CREDITAPPLICATIONID = QUOTE.CREDITAPPID (+) 
AND CREDITAPPLICATION.CREDITAPPLICATIONID = CREDITASSET.CREDITAPPLICATIONID (+) 
AND QUOTE.QUOTEID = QUOTEDASSET.QUOTEID (+) 
AND EQUIPMENT.ASSETID = QUOTEDASSET.ASSETID 
AND EQUIPMENT.TYPEID = EQUIPMENTTYPE.EQUIPMENTTYPEID
AND EQUIPMENT.MODELID = ASSETMODEL.ASSETMODELID
AND EQUIPMENT.EQUIPMENTID = ASSETUNIT.EQUIPMENTID (+)
AND ASSETMODEL.ASSETMODELTYPEID = ASSETMODELTYPE.ASSETMODELTYPEID  
AND ASSETMODELTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
AND DECISIONREQUEST.AUTO = '1'
AND DECISIONSTATUS.DECISIONSTATUSID IN ('1', '2', '4', '7', '')
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
Rasheed Shaik
  • Locked
  • Question
\Orant\DBS\FMCUS.MSG not found
Replies
0
Views
960
Rasheed Shaik
Rasheed Shaik
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
640
carp
carp
hipath4k
  • Locked
  • Question
Share link to download Orcale 10 G
Replies
0
Views
168
hipath4k
hipath4k
cabbey77
  • Locked
  • Question
Concat and/or Pivot?
Replies
3
Views
171
cabbey77
cabbey77

Part and Inventory Search

Sponsor

Back
Top