Re: Issue with subquery

This looks like Oracle dialect. Try to get rid of (+).

Fred

You can rewrite this as SELECT

On Fri, May 24, 2013, at 13:38, risaldeep wrote:
> 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
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
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