Issue with subquery

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue with subquery

Buwan
We have a function wrf_getInheritedModels which returns a table. The following
syntax works fine for us and gives us the result we expected.

select  mdl.*  from table (wrf_getInheritedModels('Transmission')) A , mdl  
where A.id = mdl.id

However if use it in the form of subquery, it does not give any error but at the
same returns an empty resultset.

The subquery we used is
 select  mdl.*  from  mdl where id in ( Select id from table
(wrf_getInheritedModels('Transmission')) A )

We expected both to give same result.

We would like to how we can use the functions returning a table in subqueries,
as we are making an application which uses subqueries work with HSQLDB.

Regards
Buwan


------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Issue with subquery

Fred Toussi-2
Thanks,

It looks like there a bug here. Will fix for the next jar snapshot which
will be available early next week from:

http://hsqldb.org/support/

Fred

On Fri, 17 Sep 2010 07:53 +0000, "Buwan" <[hidden email]> wrote:

> We have a function wrf_getInheritedModels which returns a table. The
> following
> syntax works fine for us and gives us the result we expected.
>
> select  mdl.*  from table (wrf_getInheritedModels('Transmission')) A ,
> mdl  
> where A.id = mdl.id
>
> However if use it in the form of subquery, it does not give any error but
> at the
> same returns an empty resultset.
>
> The subquery we used is
>  select  mdl.*  from  mdl where id in ( Select id from table
> (wrf_getInheritedModels('Transmission')) A )
>
> We expected both to give same result.
>
> We would like to how we can use the functions returning a table in
> subqueries,
> as we are making an application which uses subqueries work with HSQLDB.
>
> Regards
> Buwan
>
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Issue with subquery

Fred Toussi-2
In reply to this post by Buwan
There was a bug. Thanks for reporting.
Issue has been fixed and committed to SVN /base/trunk/
You can checkout from SVN and compile the jar.
The next snapshot jar from http://hsqldb.org/support/ page will include
the fix.

Fred

On Fri, 17 Sep 2010 07:53 +0000, "Buwan" <[hidden email]> wrote:

> We have a function wrf_getInheritedModels which returns a table. The
> following
> syntax works fine for us and gives us the result we expected.
>
> select  mdl.*  from table (wrf_getInheritedModels('Transmission')) A ,
> mdl  
> where A.id = mdl.id
>
> However if use it in the form of subquery, it does not give any error but
> at the
> same returns an empty resultset.
>
> The subquery we used is
>  select  mdl.*  from  mdl where id in ( Select id from table
> (wrf_getInheritedModels('Transmission')) A )
>
> We expected both to give same result.
>
> We would like to how we can use the functions returning a table in
> subqueries,
> as we are making an application which uses subqueries work with HSQLDB.
>
> Regards
> Buwan
>
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Issue with subquery

keithaniz
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
Reply | Threaded
Open this post in threaded view
|

Re: Issue with subquery

Fred Toussi-2
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
> [hidden email]
> 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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Issue with subquery

keithaniz
MANY MANY THANKS FRED..