Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Is the syntax correct for this code rewrite for a LEFT JOIN...:

    SELECT a. AdjustableInd
    , a. AccrualBasis
    , a. FirstPaymentdate
    , a. FloaterType
    , a. FloaterRecordInd
    , a. CurrentDeterminationDate
    , a. CurrentResetEffectiveDate
    , a. RateResetFrequency
    , a. NextResetEffectiveDate
    , b. ResetDayOfMonth
    , b. ResetDayOfWeek
    , c. FormulaText
    , c. FormulaConditionHandlingCode
    FROM RecordB a
    LEFT JOIN refAdjustableRateIndicator d
    on d.Code = a.AdjustableInd
    LEFT JOIN refAccrualBasis e
    on e.Code = a.AccrualBasis
    LEFT JOIN refFloaterType f
    on f.Code = a.FloaterType
    LEFT JOIN refFloaterRecordIndicator g
    on g.Code = a.FloaterRecordInd
    LEFT JOIN refRateResetFrequency h
    on h.Code = a.RateResetFrequency,
    RecordFB b, RecordF_FRNFormula c
    WHERE a.SecurityID =02666QL27
    and a.SecurityID = b.SecurityID
    and a.SecurityID = c.SecurityID;
  2. #17
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    OK I was able to run it and here it is....


    SELECT a. AdjustableInd
    , a. AccrualBasis
    , a. FirstPaymentdate
    , a. FloaterType
    , a. FloaterRecordInd
    , a. CurrentDeterminationDate
    , a. CurrentResetEffectiveDate
    , a. RateResetFrequency
    , a. NextResetEffectiveDate
    , b. ResetDayOfMonth
    , b. ResetDayOfWeek
    , c. FormulaText
    , c. FormulaConditionHandlingCode
    FROM RecordB a
    LEFT JOIN refAdjustableRateIndicator d
    on d.Code = a.AdjustableInd
    LEFT JOIN refAccrualBasis e
    on e.Code = a.AccrualBasis
    LEFT JOIN refFloaterType f
    on f.Code = a.FloaterType
    LEFT JOIN refFloaterRecordIndicator g
    on g.Code = a.FloaterRecordInd
    LEFT JOIN refRateResetFrequency h
    on h.Code = a.RateResetFrequency,
    RecordFB b, RecordF_FRNFormula c
    WHERE a.SecurityID = '02666QL27'
    and a.SecurityID = b.SecurityID
    and a.SecurityID = c.SecurityID;

    Now when I run it now it runs super fast but it returns

    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | AdjustableInd | AccrualBasis | FirstPaymentdate | FloaterType | FloaterRecordInd | CurrentDeterminationDate | CurrentResetEffectiveDate | RateResetFrequency | NextResetEffectiveDate | ResetDayOfMonth | ResetDayOfWeek | FormulaText | FormulaConditionHandlingCode |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    8 rows in set (0.00 sec)


    Why does it do that, return 8 results all the same thing?
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    Originally Posted by ByGoneYrs
    ... it has been sudjested I do a Left Join instead of a Inner join?
    suggested by whom?

    the purpose of an inner join is to return matching rows only

    the purpose of a left outer join is to return all rows from the left table, with or without matching rows from the right

    so you would use a left outer join whenever the table relationships warrant this -- it's got nothing to do with performance

    Comments on this post

    • ByGoneYrs agrees : Thank you for all your help, I did learn here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #19
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    ok I tried a Inner Join using the following Syntax:

    SELECT a. AdjustableInd
    , a. AccrualBasis
    , a. FirstPaymentdate
    , a. FloaterType
    , a. FloaterRecordInd
    , a. CurrentDeterminationDate
    , a. CurrentResetEffectiveDate
    , a. RateResetFrequency
    , a. NextResetEffectiveDate
    , b. ResetDayOfMonth
    , b. ResetDayOfWeek
    , c. FormulaText
    , c. FormulaConditionHandlingCode
    FROM RecordB a
    INNER JOIN refAdjustableRateIndicator d
    on d.Code = a.AdjustableInd
    INNER JOIN refAccrualBasis e
    on e.Code = a.AccrualBasis
    INNER JOIN refFloaterType f
    on f.Code = a.FloaterType
    INNER JOIN refFloaterRecordIndicator g
    on g.Code = a.FloaterRecordInd
    INNER JOIN refRateResetFrequency h
    on h.Code = a.RateResetFrequency,
    RecordFB b, RecordF_FRNFormula c
    WHERE a.SecurityID = '02666QL27'
    and a.SecurityID = b.SecurityID
    and a.SecurityID = c.SecurityID;

    Got the following 8 rows again....

    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | AdjustableInd | AccrualBasis | FirstPaymentdate | FloaterType | FloaterRecordInd | CurrentDeterminationDate | CurrentResetEffectiveDate | RateResetFrequency | NextResetEffectiveDate | ResetDayOfMonth | ResetDayOfWeek | FormulaText | FormulaConditionHandlingCode |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    8 rows in set (0.00 sec)


    So then I guess I need to figure out how to do a Left Outer join then next....
  8. #20
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    I even looked in these three tables and ran the following selects vs each table....


    mysql> select * from RecordB where SecurityID = '02666QL27';
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    | SecurityID | OrigPaymentRate | CalledAnnounceInd | AdjustableInd | OrigAccrualBasis | TerminationInd | MaturityDate | AccrualBasis | AccrualBeginDate | FirstPaymentDate | LastPaymentDate | IssueFeaturesCode | OrigIssueYield | OddLastCouponFlag | OrigIssueDiscountInd | DualPaymentCurrencyInd | DenominationScale | Denomination | CapitalCurrency | PaymentCurrency | PrimaryCurrency | SecondCurrency | FxRate | CallMakeWholeFlag | CouponType | DividendType | MaturityType | MaturityPriceType | FloaterType | CurrentCouponInd | FloaterRecordInd | FloaterInverseRateInd | ConversionScheduleInd | PaymentDisburseType | CreditSensitivityInd | FixedAdjEndingDate | InterestAccrualDate | CurrentCoupontType | DeferredConversionDate | FirstVariableResetDate | ResetFrequency | TraceFlag | TraceEventEffectiveDate | TraceEventFlag | GovernmentAgency | MinPurchasePrimary | MinPurchasePrimaryScale | MinPurchaseSecondary | MinPurchaseSecondaryScale | MinDenomIncremental | MinDenomIncrementalScale | TailDenomination | TailDenominationScale | MaturityExtensionDate1 | MaturityExtensionDate2 | MaturityExtensionDate3 | MaturityExtensionDate4 | MaturityExtensionDate5 | MaturityExtensionDate6 | CurrentCouponRate | CurrentResetEffectiveDate | CurrentDeterminationDate | CurrentCouponSource | NextResetEffectiveDate | NextDeterminationDate | RateResetFrequency | RateResetFrequencyPeriod | LastModifiedTime |
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    | 02666QL27 | 0.00000 | NULL | F | 1 | NULL | 2014-06-18 | 3 | 2012-06-26 | 2012-09-18 | 2014-03-18 | NULL | 0.0000 | A | N | NULL | NULL | 1000 | USD | USD | USD | | 0.000000 | 0 | E | NULL | A | F | D | Y | I | F | NULL | C | N | NULL | NULL | NULL | NULL | NULL | NULL | N | 2012-06-19 | 0000-00-00 | Z | 1000 | 0 | 1000 | 0 | 1000 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | 0.68010 | 2013-03-18 | 2013-03-14 | C | 2013-06-18 | 2013-06-14 | Q | 0 | 2013-04-30 00:00:00 |
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    1 row in set (0.00 sec)

    mysql> select * from RecordFB where SecurityID = '02666QL27';
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    | SecurityID | RecordSequence | ResetSequence | ResetMethodEffectiveDate | ResetFrequency | ResetFrequencyPeiod | ResetDayOfWeek | ResetDayOfMonth | ResetWeekOfMonth | MultipleFormulaHandlingCode | DetermineDaysPrior2ResetDate | DetermineDaysPrior2InterestDate | LastModifiedTime |
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    | 02666QL27 | 1 | 12 | 2012-06-26 | Q | 0 | NULL | C | NULL | NULL | 2 | 0 | 2013-04-30 00:00:00 |
    | 02666QL27 | 2 | 22 | 2012-09-18 | Q | 0 | NULL | C | NULL | NULL | 2 | 0 | 2013-04-30 00:00:00 |
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    2 rows in set (0.00 sec)

    mysql> select * from RecordF_FRNFormula where SecurityID = '02666QL27';
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    | SecurityID | ResetSequence | FormulaSequence | FormulaEffectiveDate | FormulaConditionHandlingCode | FixedPriceForCouponSetting | FormulaText | LastModifiedTime |
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    | 02666QL27 | 1 | 1 | 2012-06-26 | NULL | 0.00000 | RATE = 3M LIBOR + 40BP | 2013-04-30 00:00:00 |
    | 02666QL27 | 2 | 1 | 2012-09-18 | NULL | 0.00000 | RATE = 3M LIBOR + 40BP | 2013-04-30 00:00:00 |
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    2 rows in set (0.01 sec)


    What am I missing here why I am getting 8 rows returning?
  10. #21
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    OK I thought I would try using a different Security ID of '3696264P'

    first I did the three selects vs the three tables and then I ran the view ...

    mysql> select * from RecordB where SecurityID like '36962G4P%';
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    | SecurityID | OrigPaymentRate | CalledAnnounceInd | AdjustableInd | OrigAccrualBasis | TerminationInd | MaturityDate | AccrualBasis | AccrualBeginDate | FirstPaymentDate | LastPaymentDate | IssueFeaturesCode | OrigIssueYield | OddLastCouponFlag | OrigIssueDiscountInd | DualPaymentCurrencyInd | DenominationScale | Denomination | CapitalCurrency | PaymentCurrency | PrimaryCurrency | SecondCurrency | FxRate | CallMakeWholeFlag | CouponType | DividendType | MaturityType | MaturityPriceType | FloaterType | CurrentCouponInd | FloaterRecordInd | FloaterInverseRateInd | ConversionScheduleInd | PaymentDisburseType | CreditSensitivityInd | FixedAdjEndingDate | InterestAccrualDate | CurrentCoupontType | DeferredConversionDate | FirstVariableResetDate | ResetFrequency | TraceFlag | TraceEventEffectiveDate | TraceEventFlag | GovernmentAgency | MinPurchasePrimary | MinPurchasePrimaryScale | MinPurchaseSecondary | MinPurchaseSecondaryScale | MinDenomIncremental | MinDenomIncrementalScale | TailDenomination | TailDenominationScale | MaturityExtensionDate1 | MaturityExtensionDate2 | MaturityExtensionDate3 | MaturityExtensionDate4 | MaturityExtensionDate5 | MaturityExtensionDate6 | CurrentCouponRate | CurrentResetEffectiveDate | CurrentDeterminationDate | CurrentCouponSource | NextResetEffectiveDate | NextDeterminationDate | RateResetFrequency | RateResetFrequencyPeriod | LastModifiedTime |
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    | 36962G4P6 | 2.50000 | NULL | F | 1 | NULL | 2015-09-23 | 3 | 2010-09-23 | 2011-12-23 | 2015-06-23 | NULL | 0.0000 | A | N | NULL | NULL | 1000 | USD | USD | USD | | 0.000000 | 0 | E | NULL | A | F | D | Y | I | F | NULL | C | N | NULL | NULL | NULL | NULL | NULL | NULL | D | 2010-09-07 | 0000-00-00 | Z | 1000 | 0 | 1000 | 0 | 1000 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | 1.00000 | 2013-03-25 | 2013-03-21 | C | 2013-06-24 | 2013-06-20 | Q | 0 | 2013-04-30 00:00:00 |
    +------------+-----------------+-------------------+---------------+------------------+----------------+--------------+--------------+------------------+------------------+-----------------+-------------------+----------------+-------------------+----------------------+------------------------+-------------------+--------------+-----------------+-----------------+-----------------+----------------+----------+-------------------+------------+--------------+--------------+-------------------+-------------+------------------+------------------+-----------------------+-----------------------+---------------------+----------------------+--------------------+---------------------+--------------------+------------------------+------------------------+----------------+-----------+-------------------------+----------------+------------------+--------------------+-------------------------+----------------------+---------------------------+---------------------+--------------------------+------------------+-----------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+-------------------+---------------------------+--------------------------+---------------------+------------------------+-----------------------+--------------------+--------------------------+---------------------+
    1 row in set (0.00 sec)

    mysql> select * from RecordFB where SecurityID like '36962G4P%';
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    | SecurityID | RecordSequence | ResetSequence | ResetMethodEffectiveDate | ResetFrequency | ResetFrequencyPeiod | ResetDayOfWeek | ResetDayOfMonth | ResetWeekOfMonth | MultipleFormulaHandlingCode | DetermineDaysPrior2ResetDate | DetermineDaysPrior2InterestDate | LastModifiedTime |
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    | 36962G4P6 | 1 | 12 | 2010-09-23 | Q | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 2013-04-30 00:00:00 |
    | 36962G4P6 | 2 | 22 | 2011-12-23 | Q | 0 | NULL | C | NULL | NULL | 2 | 0 | 2013-04-30 00:00:00 |
    +------------+----------------+---------------+--------------------------+----------------+---------------------+----------------+-----------------+------------------+-----------------------------+------------------------------+---------------------------------+---------------------+
    2 rows in set (0.00 sec)

    mysql> select * from RecordF_FRNFormula where SecurityID like '36962G4P%';
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    | SecurityID | ResetSequence | FormulaSequence | FormulaEffectiveDate | FormulaConditionHandlingCode | FixedPriceForCouponSetting | FormulaText | LastModifiedTime |
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    | 36962G4P6 | 1 | 1 | 2010-09-23 | NULL | 0.00000 | RATE = 2.5% | 2013-04-30 00:00:00 |
    | 36962G4P6 | 2 | 1 | 2011-12-23 | NULL | 0.00000 | RATE = 3M LIBOR + 70BP | 2013-04-30 00:00:00 |
    +------------+---------------+-----------------+----------------------+------------------------------+----------------------------+------------------------+---------------------+
    2 rows in set (0.00 sec)

    mysql>


    mysql> SELECT a. AdjustableInd
    -> , a. AccrualBasis
    , a. FloaterType
    , a. FloaterRecordInd
    -> , a. FirstPaymentdate
    -> , a. FloaterType
    , c. FormulaText
    , c. FormulaConditionHandlingCode
    -> , a. FloaterRecordInd
    on d.Code = a.AdjustableInd
    -> , a. CurrentDeterminationDate
    on g.Code = a.FloaterRecordInd
    INNER JOIN refRateResetFrequency h
    -> , a. CurrentResetEffectiveDate
    -> , a. RateResetFrequency
    -> , a. NextResetEffectiveDate
    -> , b. ResetDayOfMonth
    -> , b. ResetDayOfWeek
    -> , c. FormulaText
    -> , c. FormulaConditionHandlingCode
    -> FROM RecordB a
    -> INNER JOIN refAdjustableRateIndicator d
    -> on d.Code = a.AdjustableInd
    -> INNER JOIN refAccrualBasis e
    -> on e.Code = a.AccrualBasis
    -> INNER JOIN refFloaterType f
    -> on f.Code = a.FloaterType
    -> INNER JOIN refFloaterRecordIndicator g
    -> on g.Code = a.FloaterRecordInd
    -> INNER JOIN refRateResetFrequency h
    -> on h.Code = a.RateResetFrequency,
    -> RecordFB b, RecordF_FRNFormula c
    -> WHERE a.SecurityID like '36962G4P%'
    -> and a.SecurityID = b.SecurityID
    -> and a.SecurityID = c.SecurityID;
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | AdjustableInd | AccrualBasis | FirstPaymentdate | FloaterType | FloaterRecordInd | CurrentDeterminationDate | CurrentResetEffectiveDate | RateResetFrequency | NextResetEffectiveDate | ResetDayOfMonth | ResetDayOfWeek | FormulaText | FormulaConditionHandlingCode |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | NULL | NULL | RATE = 2.5% | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | C | NULL | RATE = 2.5% | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | NULL | NULL | RATE = 3M LIBOR + 70BP | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | C | NULL | RATE = 3M LIBOR + 70BP | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | NULL | NULL | RATE = 2.5% | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | C | NULL | RATE = 2.5% | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | NULL | NULL | RATE = 3M LIBOR + 70BP | NULL |
    | F | 3 | 2011-12-23 | D | I | 2013-03-21 | 2013-03-25 | Q | 2013-06-24 | C | NULL | RATE = 3M LIBOR + 70BP | NULL |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    8 rows in set (0.00 sec)

    mysql>


    So I am still getting 8 rows back, I must not have this view select correct yet!
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    Originally Posted by ByGoneYrs
    mysql> SELECT a. AdjustableInd
    -> , a. AccrualBasis
    , a. FloaterType
    , a. FloaterRecordInd
    -> , a. FirstPaymentdate
    -> , a. FloaterType
    , c. FormulaText
    , c. FormulaConditionHandlingCode
    -> , a. FloaterRecordInd
    on d.Code = a.AdjustableInd
    -> , a. CurrentDeterminationDate
    on g.Code = a.FloaterRecordInd
    INNER JOIN refRateResetFrequency h
    -> , a. CurrentResetEffectiveDate
    -> , a. RateResetFrequency
    -> , a. NextResetEffectiveDate
    -> , b. ResetDayOfMonth
    -> , b. ResetDayOfWeek
    -> , c. FormulaText
    -> , c. FormulaConditionHandlingCode
    -> FROM RecordB a
    this is completely invalid

    how are you copying/pasting this code? because you're messing it up, and what you posted here ~clearly~ won't run, because ON and INNER JOIN are not allowed to come ahead of FROM

    my advice is to remove all the joins whose purpose is only to translate a code into a description

    debug the query with just the main tables involved (as i suggested back in post #7)

    then add the code translation joins one at a time
    Last edited by r937; May 29th, 2013 at 01:53 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #23
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    OK I figured it out...added DISTINCT at the beginning of the Select....

    Thank you for your help, it was a good learning experience.

    Here is the select and the results:

    mysql> SELECT DISTINCT a. AdjustableInd
    -> , a. AccrualBasis
    , a. CurrentDeterminationDate
    , a. CurrentResetEffectiveDate
    -> , a. FirstPaymentdate
    , b. ResetDayOfWeek
    -> , a. FloaterType
    LEFT JOIN refAdjustableRateIndicator d
    on d.Code = a.AdjustableInd
    -> , a. FloaterRecordInd
    -> , a. CurrentDeterminationDate
    WHERE a.SecurityID = '02666QL27'
    -> , a. CurrentResetEffectiveDate
    -> , a. RateResetFrequency
    -> , a. NextResetEffectiveDate
    -> , b. ResetDayOfMonth
    -> , b. ResetDayOfWeek
    -> , c. FormulaText
    -> , c. FormulaConditionHandlingCode
    -> FROM RecordB a
    -> LEFT JOIN refAdjustableRateIndicator d
    -> on d.Code = a.AdjustableInd
    -> LEFT JOIN refAccrualBasis e
    -> on e.Code = a.AccrualBasis
    -> LEFT JOIN refFloaterType f
    -> on f.Code = a.FloaterType
    -> LEFT JOIN refFloaterRecordIndicator g
    -> on g.Code = a.FloaterRecordInd
    -> LEFT JOIN refRateResetFrequency h
    -> on h.Code = a.RateResetFrequency,
    -> RecordFB b, RecordF_FRNFormula c
    -> WHERE a.SecurityID = '02666QL27'
    -> and a.SecurityID = b.SecurityID
    -> and a.SecurityID = c.SecurityID;
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | AdjustableInd | AccrualBasis | FirstPaymentdate | FloaterType | FloaterRecordInd | CurrentDeterminationDate | CurrentResetEffectiveDate | RateResetFrequency | NextResetEffectiveDate | ResetDayOfMonth | ResetDayOfWeek | FormulaText | FormulaConditionHandlingCode |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    | F | 3 | 2012-09-18 | D | I | 2013-03-14 | 2013-03-18 | Q | 2013-06-18 | C | NULL | RATE = 3M LIBOR + 40BP | NULL |
    +---------------+--------------+------------------+-------------+------------------+--------------------------+---------------------------+--------------------+------------------------+-----------------+----------------+------------------------+------------------------------+
    1 row in set (0.00 sec)
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    Originally Posted by ByGoneYrs
    OK I figured it out...added DISTINCT at the beginning of the Select....
    there's a good chance that this is simply masking a significant underlying problem

    also, you appear to be ignoring my repeated attempts to get you to post an actual working query rather than these disastrous invalid messed up FROM clauses

    nor did you attempt to debug the problem using just the base tables as i recommended

    i'm afraid i will no longer be able to offer you any support in future
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #25
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Hi there, I did look into each table, I did selects * vs each of these three tables and looked at the differences and discovered that the fields that were different were discription text where someone had enterred duplicate entries for discriptors for the same rows, thus creating bogus rows.

    Now I further did a bunch of querrys offline here to see what was what. I even went to both our senior SQL developer and VP of Developement and show them my code and what I found and what I was reasoning out what and why. Further more I asked them if the duplicate rows in question were needed or valid, they both said no. Next I asked them if my syntax was valid and they ran it and checked it and said yes. As I said I am a DBA and not a Developer and they both are.

    Now they say it is ok, BUT if you feel there is more to work on here please explain and I will dig further. I know there are many different ways to write or do anything, and some are better than others...so if I am wrong and need to dig more then please advise and I will.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo