Re: Issue with subquery

Hello Fred,
i just created view in hsqldb but it is little bit complex,
CREATE VIEW RTN_TEST.HISTORY_XX(ORDINAL,TT_STOP,.......,
            ,TT_START
            ,UNIT_ALLOCATION_MAP
            ,MPD_RANKS)as select A.ORDINAL
            ,PA.TT_STOP,........
            ,PA.TT_START
            ,PA.UNIT_ALLOCATION_MAP
            ,PA.MPD_RANKS
    FROM   RTN_TEST.TX_SEQUENCE SQ,
           RTN_TEST.TX_110_RESOLVED RS
           LEFT JOIN RTN_TEST.HISTORY_XX ON historical_claim_num =
rs.claim_number,
           RTN_TEST.TX_110_PROCESSED PS,
           RTN_TEST.TX_110_PROCESSED_AUDIT PA,
(select historical_line_num, historical_claim_num
               FROM   RTN_TEST.CUSTOM_TX_STATUS a,
                      nexxus.standardized_code sc1,
                      nexxus.standardized_code_set scs1,
                      nexxus.standardized_code sc2,
                      nexxus.standardized_code_set scs2
              WHERE       sc1.standardized_code_set_ref = scs1.ID
                      AND sc2.standardized_code_set_ref = scs2.ID
                      AND a.TX_TYPE_REF = sc1.id
                      AND a.EXCLUSION_SCENARIO_REF = sc2.id
                      AND scs1.NAME = 'TRANSACTION_TYPE'
                      AND scs2.NAME IN
                               ('EXCLUSION_SCENARIO',
'DT_EXCLUSION_SCENARIO')
                      AND sc2.code IN
                               ('0', '1', '2',
                                '3', '4', '5',
                                '7', '8', '9',
                                '10', '11', '12',
                                '13', '14', '15')
                      AND sc1.code = '2'
                      AND a.historical_claim_num IS NOT NULL) as hs		   
WHERE       RS.ID = PS.TX_110_RESOLVED_REF
            AND PS.TX_110_RESOLVED_REF = PA.TX_110_RESOLVED_REF
            AND SQ.RAW_REF = RS.TX_110_RAW_REF
            AND SQ.TX_TYPE = 2
            AND PS.PROC_CD_ST = 0
            AND PS.PROC_CD_REF > 0
            AND SQ.STATUS_IND = 2                      -- 2 means
IN_SEQUENCE 
			AND HS.historical_claim_num(+) = rs.claim_number            
            AND HS.historical_line_num(+) = rs.line_number
            AND HS.historical_claim_num IS NULL           
            AND NOT EXISTS
                  (SELECT   1
                     FROM   RTN_TEST.CUSTOM_TX_STATUS a,
                            nexxus.standardized_code sc1,
                            nexxus.standardized_code_set scs1,
                            nexxus.standardized_code sc2,
                            nexxus.standardized_code_set scs2
                    WHERE       a.activity_raw_ref = rs.tx_110_raw_ref
                            AND sc1.standardized_code_set_ref = scs1.ID
                            AND sc2.standardized_code_set_ref = scs2.ID
                            AND scs1.NAME = 'TRANSACTION_TYPE'
                            AND (SCS2.NAME = 'EXCLUSION_SCENARIO'
                                 AND SC2.CODE IN ('0', '1')
                                 OR (SCS2.NAME = 'DT_EXCLUSION_SCENARIO'
                                     AND SC2.CODE IN
                                              ('0', '1', '2',
                                               '3', '4', '5',
                                               '7', '8', '9',
                                               '10', '11', '12',
                                               '13', '14', '15')))
                            AND sc1.code = '2'
                            AND sc1.ID = a.tx_type_ref
                            AND sc2.ID = a.exclusion_scenario_ref)


 i've used subquery  alias as HS,but error is HS is treating as schema and
it is 
populating error as undefined schema HS.

Any suggestion..plzzzz
thank in advanced



--
View this message in context: http://hsqldb.10974.n7.nabble.com/Issue-with-subquery-tp1254p3894.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service 
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_may