Re: AW: AW: [dbforms] Re: [Jdbforms-developers] Query: joining 3 tables

[Chow Kwan Yu]

Hi,
It works in sqlplus.  I copied the select statement (from the stack 
trace) and run in sqlplus as is...
Any idea?
Thx
--bee

Henner Kollmann wrote:

Seems to be an Oracle bug. 
Could you please test the statement with one of the oracle tools
directly? 
Are you sure the Oracle supports the join syntax? 



 

-----Urspr�ngliche Nachricht-----
Von: Chow Kwan Yu [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 8. Oktober 2003 15:47
An: Henner Kollmann
Betreff: Re: AW: [dbforms] Re: [Jdbforms-developers] Query: 
joining 3 tables

Oracle 9i.

Henner Kollmann wrote:

   

Which database do you use?





     

-----Urspr�ngliche Nachricht-----
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im 
Auftrag von Chow Kwan Yu
Gesendet: Mittwoch, 8. Oktober 2003 10:40
An: Shawn
Cc: dbf interest
Betreff: Re: [dbforms] Re: [Jdbforms-developers] Query: 
joining 3 tables

Hi,
I've tried to use query for joining 3 tables (customer, orders and
service;  based on the testsuites example).  However it fails 
in runtime. Using similiar way I were successful to make 
joining of 2 tables works. Any idea. THX!! --bee

~~~ in dbforms-config.xml:
<query name="qit" from="(customer join orders on
customer.id=orders.customer_id) join service on  
orders.service_id=service.id">
      <field name="customer.id" fieldType="int" size="11"/>
      <field name="customer.firstname" fieldType="char" 
       

size="50"/>
   

      <field name="orders.order_id" fieldType="int" size="11" 
isKey="true" autoInc="true" sortable="true"/>
      <field name="service.id" fieldType="int" size="11" 
sortable="true"/>
      <field name="service.name" fieldType="char" size="30" 
sortable="true"/>
      <field name="service.description" fieldType="char" 
size="255"/> </query> ~~~ in jsp: <db:dbform 
autoUpdate="false" followUp="/customer3.jsp" maxRows="5" 
tableName="qit">

~~~but I've got the following error in runtime:
java.sql.SQLException: Unexpected token: CUSTOMER in statement [SET
MAXROWS 5;SE
LECT customer.id, customer.firstname, orders.order_id, service.id, 
service.name,
service.description  FROM (customer join orders on 
customer.id=orders.customer_
id) join service on  orders.service_id=service.id ORDER BY 
orders.order_id]

Shawn wrote:

  

       

On Wed, 08 Oct 2003 14:11:20 +0800, Chow Kwan Yu
    

         

<[EMAIL PROTECTED]>
  

       

wrote:

    

         

Hi,

Let me try query and tells the result later.

BTW, can whereClause be used together with searches (1.1.3) (not 
using attribute but by HTML inputs)?  I've tried the
      

           

following thing
  

       

but seems clicking on "Applica!" gives no effect on the
      

           

SQL prepared
  

       

statement generated by dbforms:
<db:dbform autoUpdate="false" followUp="/customer2.jsp" 
           

maxRows="5" 
   

tableName="cos" tableList="customer,orders,service"
whereClause="WHERE customer.id=orders.customer_id AND
orders.service_id=service.id">
<INPUT type="text" name="<%= 
searchFieldNames_cos.get("orders.order_id") %>">
<input type="button" value="Applica!" 
onClick="javascript:document.dbform.submit()">
      

           

-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf 
_______________________________________________
DbForms Mailing List

  

       

http://www.wap-force.net/dbforms



     

   

 





-------------------------------------------------------
This SF.net email is sponsored by: SF.net Giveback Program.
SourceForge.net hosts over 70,000 Open Source Projects.
See the people who have HELPED US provide better services:
Click here: http://sourceforge.net/supporters.php
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms