Oracle queries the current asset status, and another database, (query the first in duplicate data), subquery as a field query

Source: Internet
Author: User

Background: Oracle queries the current asset status, including asset information (table 1), Asset maintenance status (table 2), Asset retirement status (table 3)

As follows:

Asset Information:

Asset Repair:

Asset retirement:

After the asset application is finished, you can apply for maintenance and scrap of the current asset, with two processes in service, one for maintenance and then maintenance. The corresponding need to start the corresponding process to apply for repair or scrap

The process table is as follows:

Query all current assets and corresponding asset status (repair status, scrap status): SQL statements are as follows:


Select t.asset_id as AssetID,
T.asset_name as Assetname,
T.asset_sort as Assetsort,
T.asset_model as Assetmodel,
T.birth_date as BirthDate,
T.international_number as Internationalnumber,
T.manufacturer as Manufacturer,
T.purchase_date as Purchasedate,
T.net_salvage as Netsalvage,
T.expected_year as Expectedyear,
T.net_value as Netvalue,
T.original_value as OriginalValue,
T.depreciation_method as Depreciationmethod,
T.service_condition as Servicecondition,
T.storeman as Storeman,
T.remark as REMARK,
T.add_type as AddType,
T.processinstanceid as Processinstanceid,
T.custody_dept as Custodydept,
T.used_year as Usedyear,
T.id as ID,
M.status as Clearstatus,
--Nn.status as Maintainstatus,
(Select A.status
From (select a.asset_id, B.status
From Asset_maintain A
Join Workflow.workflow_execution b
On b.processinstanceid = A.processinstanceid
ORDER BY b.create_time Desc) A
where a.asset_id = t.asset_id
and rownum = 1) as Maintainstatus
From Asset_info t
Left JOIN (SELECT *
From Asset_clear C
Join Workflow.workflow_execution N
On c.processinstanceid = N.processinstanceid) m
On t.asset_id = m.asset_id

Here's the point:

Comprehension: Subqueries can be used as business fields, and can be associated with the table of the current main query, taking out the data they want

Oracle queries the current asset status, and another database, (query the first in duplicate data), subquery as a field query

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.