subqueries

  • Thread starter Thread starter colmkav
  • Start date Start date
C

colmkav

Hi,

why does the following (1) work but (2) not work? And if possible can
you correct (2) to what it should be?

(1)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
QryBBIDRiskMeasureCreditSEL));

QryBBIDRiskMeasureCreditSEL:
SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA");

QryBitMapRiskMeasuresCreditSEL:
SELECT tblSecurityInfo.BBIdentifier
FROM (tblProduct INNER JOIN tblDomain ON tblProduct.ProductName =
tblDomain.Code) INNER JOIN tblSecurityInfo ON tblDomain.DomainId =
tblSecurityInfo.ProductCodeId
WHERE (tblSecurityInfo.AddDate>[#adddate] AND
tblProduct.VaRSplitCreditSpreadLong<>"NA");

(2)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
(SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") ) ));
 
This is equivalent to #2, try it:

INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") ) );

Its seems weird to me to say:

SELECT BBIdentifier FROM (SELECT BBIdentifier FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )

that's a little redundant.


Did you get an error message or did your query just not do what you thought
it would?
 
This is equivalent to #2, try it:

INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )  );

Its seems weird to me to say:

SELECT BBIdentifier FROM (SELECT BBIdentifier FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )

that's a little redundant.

Did you get an error message or did your query just not do what you thought
it would?



colmkav said:
Hi,
Click to expand...
why does the following (1) work but (2) not work? And if possible can
you correct (2) to what it should be?
Click to expand...
(1)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
QryBBIDRiskMeasureCreditSEL));
Click to expand...
QryBBIDRiskMeasureCreditSEL:
SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA");
Click to expand...
QryBitMapRiskMeasuresCreditSEL:
SELECT tblSecurityInfo.BBIdentifier
FROM (tblProduct INNER JOIN tblDomain ON tblProduct.ProductName =
tblDomain.Code) INNER JOIN tblSecurityInfo ON tblDomain.DomainId =
tblSecurityInfo.ProductCodeId
WHERE (tblSecurityInfo.AddDate>[#adddate] AND
tblProduct.VaRSplitCreditSpreadLong<>"NA");
Click to expand...
(2)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
(SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )  ));- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

thanks yes I had since spotted that myself and it does indeed work.
Still a bit puzzled though as to why what I originally had didnt work
when it works if you do it with subqueries

Anyway thanks.
 
Access is such a fussy little bugger. Drives me nuts sometimes.


This is equivalent to #2, try it:
Click to expand...
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )  );
Click to expand...
Its seems weird to me to say:
Click to expand...
SELECT BBIdentifier FROM (SELECT BBIdentifier FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )
Click to expand...
that's a little redundant.
Click to expand...
Did you get an error message or did your query just not do what you thought
it would?
Click to expand...
colmkav said:
Hi,
why does the following (1) work but (2) not work? And if possible can
you correct (2) to what it should be?
(1)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
QryBBIDRiskMeasureCreditSEL));
QryBBIDRiskMeasureCreditSEL:
SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA");
QryBitMapRiskMeasuresCreditSEL:
SELECT tblSecurityInfo.BBIdentifier
FROM (tblProduct INNER JOIN tblDomain ON tblProduct.ProductName =
tblDomain.Code) INNER JOIN tblSecurityInfo ON tblDomain.DomainId =
tblSecurityInfo.ProductCodeId
WHERE (tblSecurityInfo.AddDate>[#adddate] AND
tblProduct.VaRSplitCreditSpreadLong<>"NA");
(2)
INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE (BBIdentifier Not In (SELECT BBIdentifier FROM
(SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE (BBRiskMeasure="CREDITDELTA") )  ));- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

thanks yes I had since spotted that myself and it does indeed work.
Still a bit puzzled though as to why what I originally had didnt work
when it works if you do it with subqueries

Anyway thanks.- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top