CODEWITHSUNDEEP
sqloracle-databasejoin
marcin2x4
marcin2x4

Reputation: 1429

Sub-select as a column with join (attaching extra attribute to the statement)

I have two sqls that I want to combine into one (join both to get additional attribute column):

1.Master data: this part shows companys' detail info.

SELECT 
  dim.firm_sys_no "firm_sys_no",
  firm_global_id "Firm Global ID"


FROM
  FIRM_DETAILS_DIM LEFT OUTER JOIN ACTIVITIES_FACT ON
  ( FIRM_DETAILS_DIM.FIRM_SYS_NO=AACTIVITIES_FACT.FIRM_SYS_NO  )

  LEFT OUTER JOIN REPORTING_SALES_ORG_DIM ON
  (REPORTING_SALES_ORG_DIM.ORG_SYS_NO=ACTIVITIES_FACT.SALES_ORG_SYS_NO  )

  INNER JOIN 
  (select * from REPORTING_SALES_ORG_DIM where EFFECTIVE_END_DATE is null) SALES_ORG_LATEST ON
  (REPORTING_SALES_ORG_DIM.SALES_OFFICE_ID=SALES_ORG_LATEST.SALES_OFFICE_ID )


WHERE
(ACTIVITIES_FACT.EFFECTIVE_END_DATE is null )
  AND  ( FIRM_DETAILS_DIM.FIRM_GLOBAL_ID  <>  '-1')
  AND  ( SALES_ORG_LATEST.GLOBAL_REGION_NAME  IS NOT Null )
  AND  ( SALES_ORG_LATEST.global_region_name IS NOT NULL )

GROUP BY 
  firm_details_dim.firm_sys_no,
  firm_details_dim.firm_global_id
ORDER BY 
  firm_details_dim.firm_global_id,
  firm_details_dim.firm_sys_no

2.Lookup/sub-select: returns info on transaction count per company global id.

SELECT 
  firm_details_dim.firm_global_id,
  (CASE
    WHEN round(months_between(current_date, SLS_MONTHLY_TRANS_F.transaction_date),0)>4
    THEN 0 
    ELSE 1 
  END) "Trs"

FROM SLS_MONTHLY_TRANS_F
  INNER JOIN firm_details_dim ON
  ( firm_details_dim.firm_sys_no=SLS_MONTHLY_TRANS_F.firm_sys_no )

WHERE SLS_MONTHLY_TRANS_F.transaction_amount_usd<>0

GROUP BY
  firm_details_dim.firm_global_id,
  (CASE
    WHEN round(months_between(current_date, SLS_MONTHLY_TRANS_F.transaction_date),0)>4
    THEN 0 
    ELSE 1 
  END)

SQL I need to get: combine both above queries so that master sql will have additional column from the second one (Trs4m). What I'm aiming at is to see which company from master had transaction count 0 or 1.

Upvotes: 0

Views: 36

Answers (2)

marcin2x4
marcin2x4

Reputation: 1429

Ok, I moved the sub query to the from clause part, joined with other tables and brought one of two columns to the select part. That why I didn't have to limit the sub query to one attribute while it was in select.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142788

Hm, obvious option is to (outer?) JOIN additional tables (from the second query) to the first query in order to produce the result.

If, for some reason, that can't be done, you could create a function (based on the second query) and call it from the first one, passing required parameters. That would probably be (much?) slower than the first option (hint: SQL - PL/SQL context switching).

Upvotes: 1

Related Questions