-- View: "v_item_import_export"

DROP VIEW v_item_import_export;

CREATE VIEW v_item_import_export AS
SELECT DISTINCT ON (b.itemcd)
    ''                AS shorikbn,
    b.itemcd          AS itemcd,
    b.itemnm          AS itemnm,
    b.itemnmkana      AS itemnmkana,
    b.makercd         AS makercd,
    m.makernm         AS makernm,
    b.labelcd         AS labelcd,
    l.labelnm         AS labelnm,
    b.seriescd        AS seriescd,
    ''                AS seriesnm,
    b.itemdivcd       AS itemdivcd,
    n1.name           AS itemdivnm,
    b.examgroupcd     AS examgroupcd,
    n2.name           AS examgroupnm,
    b.examno          AS examno,
    b.iteminfo        AS iteminfo,
    b.eirinrating     AS eirinrating,
    n3.name           AS eirinratingnm,
    b.subtitle        AS subtitle,
    w.rankingrate     AS rankingrate,
    w.comment         AS comment,
    s.mediacd         AS mediacd,
    n4.name           AS mediacdnm,
    s.makeritemcd     AS makeritemcd,
    s.jancd           AS jancd,
    s.isbncd          AS isbncd,
    s.salestartdate   AS salestartdate,
    s.orderclosedate  AS orderclosedate,
    s.saleformcd      AS saleformcd,
    n5.name           AS saleformnm,
    s.rectime         AS rectime,
    s.makerprice      AS makerprice,
    e.sellingprice    AS sellingprice,
    g.genrecd         AS genrecd1,
    g.genrenm         AS genrenm1,
    ''                AS genrecd2,
    ''                AS genrenm2,
    ''                AS genrecd3,
    ''                AS genrenm3,
    ''                AS genrecd4,
    ''                AS genrenm4,
    ''                AS genrecd5,
    ''                AS genrenm5,
    c.castcd          AS castcd1,
    c.castnm          AS castnm1,
    ''                AS castcd2,
    ''                AS castnm2,
    ''                AS castcd3,
    ''                AS castnm3,
    ''                AS castcd4,
    ''                AS castnm4,
    ''                AS castcd5,
    ''                AS castnm5,
    ''                AS imagel,
    ''                AS imagem,
    ''                AS images
 FROM t_item_basic_attr b
      INNER JOIN t_item_struct s
          INNER JOIN t_ex_stock_detail e USING (itemcd, structcd)
           USING (itemcd)
      INNER JOIN t_item_web_attr w   USING (itemcd)
      INNER JOIN t_maker m           USING (makercd)
      INNER JOIN t_label l           USING (labelcd)
      INNER JOIN t_name n1           ON (n1.namecd = b.itemdivcd AND n1.namediv = 1)
      INNER JOIN t_name n2           ON (n2.namecd = b.examgroupcd AND n2.namediv = 2)
      INNER JOIN t_name n3           ON (n3.namecd = b.eirinrating AND n3.namediv = 3)
      INNER JOIN t_name n4           ON (n4.namecd = s.mediacd AND n4.namediv = 4)
      INNER JOIN t_name n5           ON (n5.namecd = s.saleformcd AND n5.namediv = 5)
      INNER JOIN t_genre_item gi
          INNER JOIN t_genre g        USING (genrecd)
           USING (itemcd)
      INNER JOIN t_cast_item ci
          INNER JOIN t_cast c         USING (castcd)
           USING (itemcd)
ORDER BY itemcd DESC
LIMIT 3
;
