WITH SHOHINENTRIES AS (  
        SELECT  /*+ FIRST_ROWS(100) */
                M_TG_RENSHOHIN.NBSHOUHINCD  
                , M_TG_RENSHOHIN.SHOHINMEI_NY AS SHOHINMEI  
                , ROW_NUMBER ( ) OVER (  
                ORDER BY  
                        M_TG_RENSHOHIN.SHOHINMEI ) RN  
                , M_TG_RENSHOHIN.KANSU  
                , M_TG_RENSHOHIN.SHOYOJIKAN  
                , M_TG_RENSHOHIN.KIBAN  
                , M_TG_RENSHOHIN.RENKISEITOSIKB  
                , M_TG_RENSHOHIN.TITLECD  
                , M_TG_RENSHOHIN.TOUROKUKB  
                , M_TG_RENSHOHIN.SYOKAINYUKABI AS RENTALDATE  
                , M_TG_RENSHOHIN.RENTALKAISIBI  
                , DECODE ( M_TG_RENSHOHIN.ITEMCD , '0020' , 'VHS' , '0021' , 'DVD' ) ITEM  
        FROM  
                M_TG_RENSHOHIN  
        WHERE  
                EXISTS (  
                SELECT  
                        M_SHOHINARTIST.ARTISTMEI  
                FROM  
                        M_SHOHINARTIST  
                WHERE  
                        M_SHOHINARTIST.NBSHOUHINCD=M_TG_RENSHOHIN.NBSHOUHINCD )  
                AND M_TG_RENSHOHIN.SHOHINMEI_NY  LIKE A%' )  )  

SELECT  
        SHOHINENTRIES.*  
        , ROWTOCOL ( 'select ARTISTMEI from M_SHOHINARTIST where NBSHOUHINCD = ''' || SHOHINENTRIES.NBSHOUHINCD || ''' '  
                , ',' ) AS ARTISTMEI  
        , CASE WHEN SHOHINENTRIES.RENTALKAISIBI = '1' THEN '1' WHEN SHOHINENTRIES.RENTALKAISIBI != '1' THEN GETSTOCKSTATUS ( 'select ZAIKOJOTAI from R_RENTAL where TITLECD='  
                , SHOHINENTRIES.TITLECD ) END AS STOCK  
        , (  
        SELECT  
                M_MISEGENRE.TANAGENREMEI  
        FROM  
                M_MISEGENRE  
                , (  
                SELECT  
                        M_MISEGENRE_SHOHIN.TANAGENRECD  
                        , M_MISEGENRE_SHOHIN.SHOHINCD  
                FROM  
                        M_MISEGENRE_SHOHIN  
                        , M_TG_RENSHOHIN  
                WHERE  
                        M_MISEGENRE_SHOHIN.SHOHINCD=M_TG_RENSHOHIN.TITLECD ) M_MISE_SH  
        WHERE  
                M_MISEGENRE.TANAGENRECD=M_MISE_SH.TANAGENRECD  
                AND M_MISE_SH.SHOHINCD=SHOHINENTRIES.TITLECD ) AS TANA  
FROM  
        SHOHINENTRIES  
WHERE  
        RN  
        BETWEEN 1  
        AND 10