Subquery Question

[Guest]

Hello,

I am not very familair with subqueries and thus I am having trobubles. I
have a query that pulls back say 1000 records. Then I have a subquery in the
first query that I am using to determine if the a recorded pulled meets some
criteria for additional processing. The subquery seems to be causing a loop.
Like it is processing each 1000 records of the first query for each record
of the first.

I used a subquery because I could not get a SQL IF statement to run
correctly in Access. I am trying to determine if the records pulled from the
first query is the first record of its type in the database. The query that
I am running through Access is querying a SQL database through an ODBC call.

Below is part of the main query and the entire subquery.

***Main query***
SELECT TOP 1000 Trim(dbo_dealer.ext_name) AS Dealer, Trim(dbo_deals.deal_no)
AS Quantum

***Sub Query ***
(Select top 1 Afn.deal_no
From dbo_deals Afn
where Afn.mature_dt=dbo_deals.cur_mat_dt and Afn.trans_type = "fx" and
Afn.ccy=dbo_deals.ccy and Afn.ccy2=dbo_deals.ccy2 order by Afn.deal_dt,
Afn.deal_no) AS firstNext,

***Additional processing back in the main query after the sub runs***
IIf(firstNext=dbo_deals.deal_no,"first","next") AS firstNextText

 

[Guest]

Hi,

I'm assuming that you are doing this in a function? If so, we'll probably
need to see the whole thing to make sense of it. I will at least.

If nothing else, your first SQL statement isn't going to return much useful
information unless it contains an Order By clause. Otherwise Top 1000 will
just grab the first thousand records. Also as you are trimming deal_no, I'm
assuming that is a text field. As text sorting can be very difficult unless
you can convert it to a number or have a very good way of incrementing the
text.

I consider a subquery as a query inside of another query. It might be that
your problem could be handled that way.

 

[Guest]

Hi Jerry,

Thanks for your response. Below is the query that I am using. I have
posted the entire query. The statement in question is near the from clause.

Thanks for your help.

SELECT Trim(dbo_dealer.ext_name) AS Dealer, Trim(dbo_deals.deal_no) AS
Quantum, dbo_fxdeals.contr_rate AS [Contract Rate], dbo_fxdeals.spot_rate AS
[Spot Rate], dbo_fxdeals.fwdptsccy, dbo_fxdeals.fwdptsamt,
dbo_fxdeals.domccy, dbo_fxdeals.base_type, dbo_fxdeals.deal_rate,
dbo_fxdeals.fwd_rate, dbo_deals.trans_type, dbo_deals.ccy, dbo_deals.ccy2,
dbo_deals.base_ccy, dbo_deals.face_value, dbo_deals.face_valu2,
dbo_deals.deal_dt, dbo_deals.settle_dt, dbo_deals.mature_dt,
dbo_deals.cur_mat_dt, dbo_deals.eff_mat_dt, dbo_deals.act_mat_dt,
dbo_deals.input_dt, dbo_sectype.name AS Instrument, dbo_bustruct.name AS
Entity, Trim(dbo_bustruct.code) AS EntityCode, dbo_cparty.name AS [Counter
Party], dbo_strategy.name AS Strategy,
IIf(dbo_fxdeals.domccy=dbo_deals.ccy2,dbo_deals.ccy2 & " " &
dbo_deals.ccy,dbo_deals.ccy & " " & dbo_deals.ccy2) AS FXPair, (Select top 1
rfsEntityandUser.functionalCurrency From rfsEntityandUser Where
rfsEntityandUser.entityCode = dbo_bustruct.code) AS functionalCurrency,
IIf(FunctionalCurrency=dbo_deals.ccy,dbo_deals.ccy2,dbo_deals.ccy) AS
nonFunctionalCCy,
IIf(dbo_deals.ccy=nonfunctionalCCY,dbo_deals.face_value,-dbo_deals.face_valu2)
AS NonFunctionalNotional, Replace(Trim(UCase(dbo_strategy.name)),"forecasted
","") AS natureOfRiskType,
IIf(dbo_strategy.thekey="4117","decreased","increased") AS
natureOfRiskPhrase, IIf(dbo_strategy.thekey="4117","receipts","payments") AS
PaymentType, rmTextLookup.Memo AS Comments_Strategy,
***Subquery***
(Select top 1 Afn.deal_no From dbo_deals Afn where
Afn.mature_dt=dbo_deals.cur_mat_dt and Afn.trans_type = "fx" and
Afn.ccy=dbo_deals.ccy and Afn.ccy2=dbo_deals.ccy2 order by Afn.deal_dt,
Afn.deal_no) AS firstNext,
***End: Subquery ***
IIf(firstNext=dbo_deals.deal_no,"first","next") AS firstNextText
FROM (dbo_strategy INNER JOIN (dbo_bustruct INNER JOIN (dbo_sectype INNER
JOIN (((dbo_fxdeals INNER JOIN dbo_deals ON
dbo_fxdeals.deal_no=dbo_deals.deal_no) INNER JOIN dbo_dealer ON
dbo_deals.dealer=dbo_dealer.thekey) INNER JOIN dbo_cparty ON
dbo_deals.cparty=dbo_cparty.thekey) ON dbo_sectype.thekey=dbo_deals.sectype)
ON dbo_bustruct.thekey=dbo_deals.entity) ON
dbo_strategy.thekey=dbo_deals.strategy) LEFT JOIN rmTextLookup ON
dbo_strategy.thekey=rmTextLookup.QuantumID
WHERE format(dbo_deals.deal_dt,"yyyy")=[Print documents for which year?] And
dbo_strategy.thekey=[Enter Quantum Strategy Key ID]
ORDER BY dbo_deals.deal_no DESC;

 

[Guest]

Hello all,

I am still having trouble with this. Let me try again.

I have a query that returns ten records. I want to take each individual
record and perform a subquery on it. The reason I am doing this is to
determine the order of the record. Please assume that I cannot determine the
order of the records from the main query. The problem that I am having is
the subquery is running all ten records through the main query for each of
the ten records. Resulting in a result set of 100 records being returned and
not ten. I think I should be using something else than a subquery, but I am
not sure. Is there a way to get the subquery to only return ten records
rather than the entire set for each time through the subquery?


Thanks!
John

Hi Jerry,

Thanks for your response. Below is the query that I am using. I have
posted the entire query. The statement in question is near the from clause.

Thanks for your help.

SELECT Trim(dbo_dealer.ext_name) AS Dealer, Trim(dbo_deals.deal_no) AS
Quantum, dbo_fxdeals.contr_rate AS [Contract Rate], dbo_fxdeals.spot_rate AS
[Spot Rate], dbo_fxdeals.fwdptsccy, dbo_fxdeals.fwdptsamt,
dbo_fxdeals.domccy, dbo_fxdeals.base_type, dbo_fxdeals.deal_rate,
dbo_fxdeals.fwd_rate, dbo_deals.trans_type, dbo_deals.ccy, dbo_deals.ccy2,
dbo_deals.base_ccy, dbo_deals.face_value, dbo_deals.face_valu2,
dbo_deals.deal_dt, dbo_deals.settle_dt, dbo_deals.mature_dt,
dbo_deals.cur_mat_dt, dbo_deals.eff_mat_dt, dbo_deals.act_mat_dt,
dbo_deals.input_dt, dbo_sectype.name AS Instrument, dbo_bustruct.name AS
Entity, Trim(dbo_bustruct.code) AS EntityCode, dbo_cparty.name AS [Counter
Party], dbo_strategy.name AS Strategy,
IIf(dbo_fxdeals.domccy=dbo_deals.ccy2,dbo_deals.ccy2 & " " &
dbo_deals.ccy,dbo_deals.ccy & " " & dbo_deals.ccy2) AS FXPair, (Select top 1
rfsEntityandUser.functionalCurrency From rfsEntityandUser Where
rfsEntityandUser.entityCode = dbo_bustruct.code) AS functionalCurrency,
IIf(FunctionalCurrency=dbo_deals.ccy,dbo_deals.ccy2,dbo_deals.ccy) AS
nonFunctionalCCy,
IIf(dbo_deals.ccy=nonfunctionalCCY,dbo_deals.face_value,-dbo_deals.face_valu2)
AS NonFunctionalNotional, Replace(Trim(UCase(dbo_strategy.name)),"forecasted
","") AS natureOfRiskType,
IIf(dbo_strategy.thekey="4117","decreased","increased") AS
natureOfRiskPhrase, IIf(dbo_strategy.thekey="4117","receipts","payments") AS
PaymentType, rmTextLookup.Memo AS Comments_Strategy,
***Subquery***
(Select top 1 Afn.deal_no From dbo_deals Afn where
Afn.mature_dt=dbo_deals.cur_mat_dt and Afn.trans_type = "fx" and
Afn.ccy=dbo_deals.ccy and Afn.ccy2=dbo_deals.ccy2 order by Afn.deal_dt,
Afn.deal_no) AS firstNext,
***End: Subquery ***
IIf(firstNext=dbo_deals.deal_no,"first","next") AS firstNextText
FROM (dbo_strategy INNER JOIN (dbo_bustruct INNER JOIN (dbo_sectype INNER
JOIN (((dbo_fxdeals INNER JOIN dbo_deals ON
dbo_fxdeals.deal_no=dbo_deals.deal_no) INNER JOIN dbo_dealer ON
dbo_deals.dealer=dbo_dealer.thekey) INNER JOIN dbo_cparty ON
dbo_deals.cparty=dbo_cparty.thekey) ON dbo_sectype.thekey=dbo_deals.sectype)
ON dbo_bustruct.thekey=dbo_deals.entity) ON
dbo_strategy.thekey=dbo_deals.strategy) LEFT JOIN rmTextLookup ON
dbo_strategy.thekey=rmTextLookup.QuantumID
WHERE format(dbo_deals.deal_dt,"yyyy")=[Print documents for which year?] And
dbo_strategy.thekey=[Enter Quantum Strategy Key ID]
ORDER BY dbo_deals.deal_no DESC;

Hi,

I'm assuming that you are doing this in a function? If so, we'll probably
need to see the whole thing to make sense of it. I will at least.

If nothing else, your first SQL statement isn't going to return much useful
information unless it contains an Order By clause. Otherwise Top 1000 will
just grab the first thousand records. Also as you are trimming deal_no, I'm
assuming that is a text field. As text sorting can be very difficult unless
you can convert it to a number or have a very good way of incrementing the
text.

I consider a subquery as a query inside of another query. It might be that
your problem could be handled that way.