SELECT DISTINCT mq.cusip,
                'TICKERSECTYPE'    AS uniqueid5typecode,
                be.ticker          AS uniqueid2,
                be.ticker + '-' + CASE be.security_typ
                                    WHEN 'ADR' THEN 'AD'
                                    WHEN 'Closed-End Fund' THEN 'M1'
                                    WHEN 'Common Stock' THEN 'CS'
                                    WHEN 'ETF' THEN 'EF'
                                    WHEN 'GDR' THEN 'GD'
                                    WHEN 'Ltd Part' THEN 'LP'
                                    WHEN 'NY Reg Shrs' THEN 'NR'
                                    WHEN 'Preferred' THEN 'PS'
                                    WHEN 'REIT' THEN 'RI'
                                    WHEN 'Royalty Trst' THEN 'RO'
                                    WHEN 'Tracking Stk' THEN 'TS'
                                    WHEN 'Unit' THEN 'UT'
                                  END AS uniqueid5,
                be.ticker + '-' + CASE be.security_typ
                                    WHEN 'ADR' THEN 'AD'
                                    WHEN 'Closed-End Fund' THEN 'M1'
                                    WHEN 'Common Stock' THEN 'CS'
                                    WHEN 'ETF' THEN 'EF'
                                    WHEN 'GDR' THEN 'GD'
                                    WHEN 'Ltd Part' THEN 'LP'
                                    WHEN 'NY Reg Shrs' THEN 'NR'
                                    WHEN 'Preferred' THEN 'PS'
                                    WHEN 'REIT' THEN 'RI'
                                    WHEN 'Royalty Trst' THEN 'RO'
                                    WHEN 'Tracking Stk' THEN 'TS'
                                    WHEN 'Unit' THEN 'UT'
                                  END AS assetcode,
                be.name            AS shortname,
                be.long_comp_name  AS longname,
                be.security_typ    AS [description],
                '0301'             AS assettypecode,
                'ISSUERCODE'       AS issueruniqueidtypecode,
                be.id_bb_company   AS issueruniqueid,
                CASE be.eqy_prim_exch
                  WHEN 'Chicago' THEN 'Chic'
                  WHEN 'NASDAQ GM' THEN 'NASD'
                  WHEN 'NASDAQ CM' THEN 'NASD'
                  WHEN 'New York' THEN 'NYSE'
                  WHEN 'NASDAQ GS' THEN 'NASD'
                  WHEN 'NYSE Arca' THEN 'NYSE'
                  WHEN 'American' THEN 'AMEX'
                  ELSE be.eqy_prim_exch
                END AS primaryexchangecode,
                be.voting_rights   AS votespershare,
                bp.px_ask          AS askprice,
                bp.px_bid          AS bidprice,
                bp.px_last         AS price,
                bp.last_update     AS pricedatetime,
                be.when_issued     AS iswhenissue,
                be.crncy           AS pricecurrency,
                be.eqy_sh_out_real AS quantityoutstanding,
                CASE be.dvd_freq
                  WHEN 'Quarter' THEN 'Q'
                  WHEN 'Annual' THEN 'A'
                  WHEN 'Semi-Anl' THEN 'S'
                  ELSE NULL
                END AS dividendfrequency,
                be.dvd_pay_dt      AS deliverydate,
                CASE be.industry_sector
                  WHEN 'Asset Backed Securities' THEN 'ASSETBS'
                  WHEN 'Basic Materials' THEN 'BASIC'
                  WHEN 'Communications' THEN 'COMM'
                  WHEN 'Consumer Cyclical' THEN 'CONCYCL'
                  WHEN 'Consumer Non-cyclical' THEN 'CONNCYCL'
                  WHEN 'Diversified' THEN 'DIVERSE'
                  WHEN 'Energy' THEN 'ENERGY'
                  WHEN 'Financial' THEN 'FINANCE'
                  WHEN 'Funds' THEN 'XFUNDS'
                  WHEN 'Government' THEN 'GOVNT'
                  WHEN 'Mortgage Securities' THEN 'MORTSEC'
                  WHEN 'Industrial' THEN 'INDUSTRY'
                  WHEN 'Technology' THEN 'TECH'
                  WHEN 'Utilities' THEN 'UTILITY'
                  ELSE 'ZNONCLAS'
                END AS extinternationalsector
                
FROM   metaeqcusip_view mq
       LEFT JOIN (SELECT cusip,
                         ticker,
                         security_typ,
                         [name],
                         voting_rights,
                         crncy,
                         eqy_sh_out_real,
                         id_bb_company,
                         eqy_prim_exch,
                         long_comp_name,
                         when_issued,
                         dvd_freq,
                         dvd_pay_dt,
                         industry_sector
                  FROM   dbo.mibloombergequitydata) be
         ON mq.cusip = be.cusip
       LEFT JOIN (SELECT cusip,
                         px_last,
                         px_bid,
                         px_ask,
                         last_update
                  FROM   dbo.mibloombergequitypricingdata) bp
         ON mq.cusip = bp.cusip
       LEFT JOIN dbo.marketqa_equitygicsdata mg
         ON mq.cusip = mg.cusip
WHERE  mq.cusip IS NOT NULL
       AND be.cusip IS NOT NULL
       AND bp.cusip IS NOT NULL
       AND mg.cusip IS NOT NULL
           
UNION

SELECT DISTINCT si.cusip,
                'TICKERSECTYPE'    AS uniqueid5typecode,
                be.ticker          AS uniqueid2,
                be.ticker + '-' + CASE be.security_typ
                                    WHEN 'ADR' THEN 'AD'
                                    WHEN 'Closed-End Fund' THEN 'M1'
                                    WHEN 'Common Stock' THEN 'CS'
                                    WHEN 'ETF' THEN 'EF'
                                    WHEN 'GDR' THEN 'GD'
                                    WHEN 'Ltd Part' THEN 'LP'
                                    WHEN 'NY Reg Shrs' THEN 'NR'
                                    WHEN 'Preferred' THEN 'PS'
                                    WHEN 'REIT' THEN 'RI'
                                    WHEN 'Royalty Trst' THEN 'RO'
                                    WHEN 'Tracking Stk' THEN 'TS'
                                    WHEN 'Unit' THEN 'UT'
                                  END AS uniqueid5,
                be.ticker + '-' + CASE be.security_typ
                                    WHEN 'ADR' THEN 'AD'
                                    WHEN 'Closed-End Fund' THEN 'M1'
                                    WHEN 'Common Stock' THEN 'CS'
                                    WHEN 'ETF' THEN 'EF'
                                    WHEN 'GDR' THEN 'GD'
                                    WHEN 'Ltd Part' THEN 'LP'
                                    WHEN 'NY Reg Shrs' THEN 'NR'
                                    WHEN 'Preferred' THEN 'PS'
                                    WHEN 'REIT' THEN 'RI'
                                    WHEN 'Royalty Trst' THEN 'RO'
                                    WHEN 'Tracking Stk' THEN 'TS'
                                    WHEN 'Unit' THEN 'UT'
                                  END AS assetcode,
                be.name            AS shortname,
                be.long_comp_name  AS longname,
                be.security_typ    AS [description],
                '0301'             AS assettypecode,
                'ISSUERCODE'       AS issueruniqueidtypecode,
                be.id_bb_company   AS issueruniqueid,
                CASE be.eqy_prim_exch
                  WHEN 'Chicago' THEN 'Chic'
                  WHEN 'NASDAQ GM' THEN 'NASD'
                  WHEN 'NASDAQ CM' THEN 'NASD'
                  WHEN 'New York' THEN 'NYSE'
                  WHEN 'NASDAQ GS' THEN 'NASD'
                  WHEN 'NYSE Arca' THEN 'NYSE'
                  WHEN 'American' THEN 'AMEX'
                  ELSE be.eqy_prim_exch
                END AS primaryexchangecode,
                be.voting_rights   AS votespershare,
                bp.px_ask          AS askprice,
                bp.px_bid          AS bidprice,
                bp.px_last         AS price,
                bp.last_update     AS pricedatetime,
                be.when_issued     AS iswhenissue,
                be.crncy           AS pricecurrency,
                be.eqy_sh_out_real AS quantityoutstanding,
                CASE be.dvd_freq
                  WHEN 'Quarter' THEN 'Q'
                  WHEN 'Annual' THEN 'A'
                  WHEN 'Semi-Anl' THEN 'S'
                  ELSE NULL
                END AS dividendfrequency,
                be.dvd_pay_dt      AS deliverydate,
                CASE be.industry_sector
                  WHEN 'Asset Backed Securities' THEN 'ASSETBS'
                  WHEN 'Basic Materials' THEN 'BASIC'
                  WHEN 'Communications' THEN 'COMM'
                  WHEN 'Consumer Cyclical' THEN 'CONCYCL'
                  WHEN 'Consumer Non-cyclical' THEN 'CONNCYCL'
                  WHEN 'Diversified' THEN 'DIVERSE'
                  WHEN 'Energy' THEN 'ENERGY'
                  WHEN 'Financial' THEN 'FINANCE'
                  WHEN 'Funds' THEN 'XFUNDS'
                  WHEN 'Government' THEN 'GOVNT'
                  WHEN 'Mortgage Securities' THEN 'MORTSEC'
                  WHEN 'Industrial' THEN 'INDUSTRY'
                  WHEN 'Technology' THEN 'TECH'
                  WHEN 'Utilities' THEN 'UTILITY'
                  ELSE 'ZNONCLAS'
                END AS extinternationalsector
FROM   (SELECT cusip,
               ticker,
               security_typ,
               [name],
               voting_rights,
               crncy,
               eqy_sh_out_real,
               id_bb_company,
               eqy_prim_exch,
               long_comp_name,
               when_issued,
               dvd_freq,
               dvd_pay_dt,
               industry_sector
        FROM   dbo.mibloombergequitydata
        WHERE  ticker IN (SELECT ticker
                          FROM   metavantetickersnotinbloomberg)) be
       LEFT JOIN (SELECT cusip,
                         px_last,
                         px_bid,
                         px_ask,
                         last_update
                  FROM   dbo.mibloombergequitypricingdata) bp
         ON be.cusip = bp.cusip
       LEFT JOIN dbo.marketqa_equitygicsdata mg
         ON be.ticker = mg.ticker
       LEFT JOIN dbo.securityinfobank98 si
         ON be.ticker = si.ticker
WHERE  be.cusip IS NOT NULL
       AND bp.cusip IS NOT NULL
       AND mg.cusip IS NOT NULL
UNION

SELECT ms.cusip,
       'TICKERSECTYPE'              AS uniqueid5typecode,
       ms.ticker                    AS uniqueid2,
       ms.ticker + '-' + ms.sectype AS uniqueid5,
       ms.ticker + '-' + ms.sectype AS assetcode,
       ms.name1                     AS shortname,
       ms.name1                     AS longname,
       CASE
         WHEN ms.name1 LIKE 'CALL%'
               OR ms.name1 LIKE 'PUT%' THEN 'Options'
         ELSE NULL
       END AS [description],
       '0301'                       AS assettypecode,
       'ISSUERCODE'                 AS issueruniqueidtypecode,
       NULL                         AS issueruniqueid,
       NULL                         AS primaryexchangecode,
       NULL                         AS votespershare,
       ms.market_price              AS price,
       'USD'                        AS pricecurrency,
       NULL                         AS quantityoutstanding,
       NULL                         AS askprice,
       NULL                         AS bidprice,
       NULL                         AS pricedatetime,
       NULL                         AS iswhenissue,
       NULL                         AS deliverydate,
       NULL                         AS dividendfrequency,
       NULL                         AS extinternationalsector
FROM   metaeqcusip_view mv
       LEFT JOIN securityinfobank98 ms
         ON mv.cusip = ms.cusip
       LEFT JOIN (SELECT be.ticker
                  FROM   (SELECT cusip,
                                 ticker
                          FROM   dbo.mibloombergequitydata
                          WHERE  ticker IN (SELECT ticker
                                            FROM   metavantetickersnotinbloomberg)) be
                         LEFT JOIN (SELECT cusip,
                                           ticker
                                    FROM   dbo.mibloombergequitypricingdata) bp
                           ON be.cusip = bp.cusip
                  WHERE  be.cusip IS NOT NULL
                         AND bp.cusip IS NOT NULL) badtickers
         ON ms.ticker = badtickers.ticker
       LEFT JOIN dbo.mibloombergequitydata mibe
         ON mv.cusip = mibe.cusip
WHERE  mibe.cusip IS NULL
       AND badtickers.ticker IS NULL
UNION
SELECT mi.ticker,
       'TICKERSECTYPE'         AS uniqueid5typecode,
       mi.ticker               AS uniqueid2,
       mi.ticker + '-' + 'INX' AS uniqueid5,
       mi.ticker + '-' + 'INX' AS assetcode,
       mi.name                 AS shortname,
       mi.name                 AS longname,
       mi.name                 AS [description],
       '0802'                  AS assettypecode,
       'ISSUERCODE'            AS issueruniqueidtypecode,
       NULL                    AS issueruniqueid,
       NULL                    AS primaryexchangecode,
       NULL                    AS votespershare,
       mi.price                AS price,
       'USD'                   AS pricecurrency,
       NULL                    AS quantityoutstanding,
       NULL                    AS askprice,
       NULL                    AS bidprice,
       NULL                    AS pricedatetime,
       NULL                    AS iswhenissue,
       NULL                    AS deliverydate,
       NULL                    AS dividendfrequency,
       NULL                    AS extinternationalsector
       
FROM   dbo.marketqa_indexinfo mi

quote:

---

Originally posted by duhaas

I found the column "'USD' AS pricecurrency" but am unsure why it thinks it should be money? Its USD in the first two queries

---