Completed

Oracle Loop a query with a variable ID which is contained in a different a table (iterative)

I have two queries one which selects the IDs:

SELECT CLIENT_REFERENCE FROM [login to view URL]

The problem that i have is I need the query below run for each ID. I have put the text "THIS NEEDS TO BE REPLACED WITH THE ID" where the ID (CLIENT_REFERENCE) needs to be placed.

The I cannot use an IN statement because I also need the CLIENT_REFERENCE to appear for each record retrieved from he query.

The end result should be what is in the fields in the select statement as well as the client_reference/ID which is queried.

I am using dbeaver to run the statements against the oracle DB.

The query - I have put the text "THIS NEEDS TO BE REPLACED WITH THE ID" where the ID (CLIENT_REFERENCE) needs to be placed.

SELECT /*+ RULE */

[login to view URL],

sum(((amount)*-1)/nvl(s.minor_per_unit,1)) bal,

e.security_id,

s.local_code,

s.curr_sym,

[login to view URL],

nvl(s.minor_per_unit,1) mpu,

nvl(s.decimal_places,0) dp,

nvl(s.pricing_units,1) pricing_units,

s.exchange_rate,

s.val_security,

SUM(CASE WHEN S.VAL_SECURITY NOT IN ('GBP') AND E.SECURITY_ID NOT IN ('GBP') Then ((((amount)*-1)/nvl(s.minor_per_unit,1))*s.exchange_rate)*(SELECT EXCHANGE_RATE FROM [login to view URL] WHERE SECURITY_ID=S.VAL_SECURITY) WHEN S.VAL_SECURITY IN ('GBP') AND E.SECURITY_ID NOT IN ('GBP') Then ((((amount)*-1)/nvl(s.minor_per_unit,1))*s.exchange_rate) Else ((amount)*-1)/nvl(s.minor_per_unit,1) End )GBP_VALUE,

(CASE WHEN e.security_id IN ('GB00BCFKG114','GB00BJ591Z61','GB00BLRZNY93','GB00BQ70PN21','GB00BYZZYP05','GB00BD5NDM75','GB00BD5NDF09','GB00BYQLMM09','GB00BDRKWQ32','GB00BD3H4G23','GB00BYQJ1839','GB00BFX17144','GB00BV9G0J47','GB00BYQJ1946','GB00BD5M5K62','GB00BGPP6817','XS0159013068') THEN 'BONDS' ELSE s.class_narrative END) AS ASSETCLASS

FROM trans t, security s, account a, entry e

WHERE a.account_id in

(select account_id from account

where account_id in

(select account_id from account

connect by prior account_id = parent

start with account_id in

(select account_id from account x

where type = 'INVESTOR'

and holder ="THIS NEEDS TO BE REPLACED WITH THE ID"

and (

('PHY_CUS') in

(select account_id from account

connect by prior parent = account_id

start with account_id = x.account_id)

OR

('CRE_AV') in

(select account_id from account

connect by prior parent = account_id

start with account_id = x.account_id)

or ('CRE_INV') in

(select account_id from account

connect by prior parent = account_id

start with account_id = x.account_id)

or ('CDP') in

(select account_id from account

connect by prior parent = account_id

start with account_id = x.account_id)

)

)))

and e.account_id = ''||a.account_id

and ( ''||[login to view URL] in ( 'CRE_DEL','CRE_RES','CRE_STW','CRE_OAT','CRE_MTM',

'CRE_USE','INVESTOR','CRE_ESCROW' )

or ( ''||[login to view URL] = 'CRE_CLA' and nvl(s.class_narrative,'SHARES') != 'CURRENCY' ) )

and ( ''||[login to view URL] != 'CRE_DEL'

or ( ''||[login to view URL] = 'CRE_DEL'

and exists(

select 1 from entry ee, account aa

where aa.account_id = ee.account_id

and [login to view URL] = [login to view URL]

and [login to view URL] = [login to view URL]

and ''||ee.security_id != e.security_id ) ) )

and s.security_id = ''||e.security_id

and t.trans_id = ''||e.trans_id

and nvl( e.val_date, decode('STAT',

'STAT',t.statement_date, 'PHYS',t.physical_date-1 ) )

<= '05-AUG-19'

group by

[login to view URL], e.security_id,

s.local_code,

s.curr_sym,

[login to view URL],

nvl(s.minor_per_unit,1),

nvl(s.decimal_places,0),

nvl(s.pricing_units,1),

s.exchange_rate,

s.val_security,

S.class_narrative

having sum(amount) !=0;

Thank you for reading, any help would be greatly appreciated.

I will only pay after testing.

Skills: Oracle, SQL, Oracle Database

See more: add fields different table time, sql query find child parent items table, oracle 10g query bible, oracle sql query buyer, variable number column database table, mysql scripts loop query, oracle sql query date weeks, store procedure data different table sql, different table format, php query search drop box output table, mysql loop query results, oracle sql query full name last name name, day week oracle sql query, oracle project query, oracle age query, loop query oracle sql developer, oracle loop query, for loop in oracle sql query

About the Employer:
( 19 reviews ) essex, United Kingdom

Project ID: #21370997

Awarded to:

truongnguyen86

Hello there, i'm expert on writing complex queries in Oracle. To solve your problem just need to put the id into the group by statement to return for each ID, and you can filter the ID by the IN statement. If it's not More

$30 USD in 1 day
(170 Reviews)
6.8

3 freelancers are bidding on average $22 for this job

banani1989

Dear project manager I have gone through the requirement and I have modified the query. We can chat and I can deliver the code. So that you can test it. Regards Banani

$15 USD in 1 day
(1 Review)
0.4
Srinfoteam

Thanks for spending your valuable time for reading my proposal It may be something different if you feel something cool. Thanks TGG:) 100% in budget and on time Quality pass tested work delivered Hear More and conc More

$20 USD in 7 days
(0 Reviews)
0.0