John, 
can you evaluate this please ?
thanks

Hi, thank you for the help!!

I will be more clear and specific...

I am using Netbeans 6.1 and I am developing a Web Application with Visual JSF. This visual JSF works with the woodstock
componentes and it has been working nice so far. I had to make the QUERY above and CONCATENATE fields like here

MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS
CONTACT_NAME,


but somehow, when I execute the query on the DataSet, it says there is a Lexical problem with the "||" and it will not
allow me to display the fields and data from on the visual table. 
Even SUBQUERIES don't work because of the "(".... like here 

SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME ||
' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, 

(SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS
LDSPROVIDED, 

(SELECT COUNT(LDS2.ID) FROM LEADS LDS2 WHERE LDS2.AGENCY_ID = AGENCY.ID AND
LDS2.CUSTOMER = 1) AS LDSCONVERTED,

(SELECT COUNT(AG.ID) FROM AGENCY AG) AS TOTALAGENCY

I have no idea how to solve this.

Thank you very much for the help,

Vitor

Which database and driver versions are you using?


To make sure the SQL is correct, you can test the query from the Services window by selecting a connection, right-click
then choose Execute Query.
In the SQL Editor, paste the SQL then execute.

The sql is working perfectly fine. I only get the problem when I go to the visual query on Netbeans. I am using Oracle
XE and the driver version is the latest one.

The Visual Query Editor only interprets a subset of SQL.  It's parser is very limited, unfortunately.
We hope to improve this in a future release.

A better editor to use to verify your SQL is the NetBeans SQL Editor as explained in my previous comment.
Even  though the Visual Query Editor fails to execute the query, the application should still work.

Here's how you can make sure your application works:

1) Test your query using the SQL Editor.  If you have a parameterized query, though, you'll need to insert some test
data for the parameter

2) In your Visual Web project, open the Design view of your page, then use the Navigator window.
Expand the SessionBean node where your rowset is created (by default) then select the Rowset.
View the Properties window and for the Statement property, you can edit the SQL.

3) Also, in the rowset Properties window, enable the printStatements property. 
By enabling this property, the SQL statement executed by the application at runtime will be printed in the Application
Server log file.  That way you can be sure the SQL you expect is executed

Hi Jbaker,

Everything you said works perfectly, but the problem is... I can not bind this rowSet on the table.. it does not allow
me to do it. It is all because this Lexical problem. is there anything that I can for this binding? I can send print
screens in jpg format so you can see. But the problem is, I can not bind the rowSet on my table. I always click with the
right button on the table, then table layout.... and there I choose the dataSource.. but it does not allow me to do it.
And it only happend now. All my pages I have done this way, they work fine. the problem is only with this kind of select
that uses concatenation and sub queries.

thank  you very much for your attention,

Vitor

Check the SQL written to SessionBean1.java 's  _init() method.

You may need to escape each character of [ || ' ' || ]  in the SQL.

you mean doing like this..........


"SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME [|| \' \' ||] CONTACT_PERSON.L_NAME)..." ?

I think like this:

SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME \|\|
\'\ ' \|\| CONTACT_PERSON.L_NAME)   ...

If this doesn't work then I'll try to create a similar query.
Any chance you can send me the SQL to create the tables ?

yeah! it doesn't allow me to do what you said.. so here you have the sql and the tables, JUST RUN THE SCRIPT

SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS
CONTACT_NAME, AGENCY.EMAIL, 
(SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS LDSPROVIDED, 
(SELECT COUNT(LDS2.ID) FROM LEADS LDS2 WHERE LDS2.AGENCY_ID = AGENCY.ID AND LDS2.CUSTOMER = 1) AS LDSCONVERTED,
(SELECT COUNT(AG.ID) FROM AGENCY AG) AS TOTALAGENCY,
'2' AS SELECTEDAGENCY
FROM AGENCY, CONTACT_PERSON
WHERE AGENCY.ID = CONTACT_PERSON.AGENCY_ID 
      AND CONTACT_PERSON.ID = (SELECT MIN(CP2.ID) FROM CONTACT_PERSON CP2 WHERE CP2.AGENCY_ID = AGENCY.ID)
GROUP BY AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID
ORDER BY AGENCY.CREATED_DATE


COPY/PASTE EVERYTHING AND RUN


CREATE TABLE AGENCY
(
ID NUMBER NOT NULL,
AGENCY VARCHAR2(40),
C_NAME VARCHAR2(40),
EMAIL VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(30),
COUNTRY VARCHAR2(20),
POSTAL_CODE VARCHAR2(12),
PHONE VARCHAR2(12),
FAX VARCHAR2(12),
COMMENTS VARCHAR2(500),
ACTIVE NUMBER,
COMISSION_AGREED NUMBER,
BANK VARCHAR2(40),
BANK_ACCOUNT NUMBER,
BANK_CODE NUMBER,
IBAN VARCHAR2(30),
BIC VARCHAR2(30),
WEBSITE VARCHAR2(30),
CREATED_DATE DATE
)
;

CREATE TABLE AGENCYCONTACTLOG
(
ID NUMBER NOT NULL,
LOG_DATE DATE,
CONTACTED VARCHAR2(15),
DETAILS VARCHAR2(500),
AGENCY_ID NUMBER NOT NULL
)
;

CREATE TABLE APARTMENT
(
ID NUMBER NOT NULL,
NAME VARCHAR2(20),
HTYPE VARCHAR2(20),
ZONE NUMBER,
FLOOR VARCHAR2(20),
SOLD NUMBER,
APTPRICE_ID NUMBER,
SEAVIEW VARCHAR2(20),
COMMENTS VARCHAR2(400)
)
;

CREATE TABLE APPSETTINGS
(
ID NUMBER NOT NULL,
PRICE_AP_NFA NUMBER,
PRICE_AP_TER NUMBER,
PRICE_VI_NFA NUMBER,
PRICE_VI_LAND NUMBER,
PRICE_VI_TER NUMBER,
PRICE_VI_POOL NUMBER,
COMMISSION NUMBER,
DISC_OSV NUMBER,
DISC_LSV NUMBER,
DISC_NRG NUMBER,
TAX_LAND NUMBER,
TAX_PROP NUMBER,
DISC_NOU NUMBER,
PAY_RES NUMBER,
PAY_PURCHAG_PERC NUMBER,
PAY_PRELPURCHAG_PERC NUMBER,
PAY_RESAG_PERC NUMBER
)
;

CREATE TABLE APTPRICE
(
ID NUMBER NOT NULL,
ATYPE VARCHAR2(20),
SQM_NFA NUMBER(5, 2),
SQM_TERRANCE NUMBER(5, 2)
)
;

CREATE TABLE CONTACT_PERSON
(
ID NUMBER NOT NULL,
F_NAME VARCHAR2(40),
L_NAME VARCHAR2(40),
TEL VARCHAR2(20),
FAX VARCHAR2(20),
EMAIL VARCHAR2(40),
AGENCY_ID NUMBER
)
;

CREATE TABLE LEADS
(
ID NUMBER NOT NULL,
FIRST_NAME VARCHAR2(40),
LAST_NAME VARCHAR2(40),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(30),
POSTAL_CODE VARCHAR2(10),
EMAIL VARCHAR2(40),
PHONE VARCHAR2(15),
FAX VARCHAR2(15),
COUNTRY VARCHAR2(15),
QUALITY VARCHAR2(10),
STATUS VARCHAR2(15),
DATE_CREATED DATE,
LEADSOURCE VARCHAR2(20),
ACTIVE NUMBER,
AGENCY_ID NUMBER,
CUSTOMER NUMBER,
LONG_STOP_DATE DATE
)
;

CREATE TABLE LEADSCONTACTLOG
(
ID NUMBER NOT NULL,
LOG_DATE DATE,
DETAILS VARCHAR2(500),
CONTACTED VARCHAR2(15),
LEADS_ID NUMBER
)
;

CREATE TABLE PAYMENT
(
UNITSOLD_ID NUMBER NOT NULL,
RESERVATION NUMBER,
RESERV_DATE DATE,
RESERV_AGREED NUMBER,
RESERV_AGREED_DATE DATE,
PRELIM_PURCHASE NUMBER,
PRELIM_PURCHASE_DATE DATE,
PURCHASE_AGREED NUMBER,
PURCHASE_AGREED_DATE DATE
)
;

CREATE TABLE ROLE
(
ID NUMBER(*, 0) NOT NULL,
TYPE VARCHAR2(20),
DESCRIPTION VARCHAR2(300) NOT NULL
)
;

CREATE TABLE UNITSOLD
(
ID NUMBER NOT NULL,
PAY_PURCHAG NUMBER,
PAY_PRELPURCHAG NUMBER,
PAY_RESAG NUMBER,
PAY_RES NUMBER,
COMMISSION NUMBER,
TAX_PROP NUMBER,
TAX_LAND NUMBER,
PRICE_GROSS NUMBER,
PRICE_NET NUMBER,
AGENCY_ID NUMBER,
UNITY_TYPE VARCHAR2(2),
VILLA_ID NUMBER,
APARTMENT_ID NUMBER,
LEADS_ID NUMBER,
PAY_PURCHAG_PERC NUMBER,
PAY_PRELPURCHAG_PERC NUMBER,
PAY_RESAG_PERC NUMBER,
CONTRACT VARCHAR2(20),
PURCHASE_DATE DATE,
DISCOUNT NUMBER
)
;

CREATE TABLE USERPROJECT
(
ID NUMBER(*, 0) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
PASSWORD VARCHAR2(20),
EMAIL VARCHAR2(30) NOT NULL,
ROLE_ID NUMBER(*, 0)
)
;

CREATE TABLE VILLA
(
ID NUMBER NOT NULL,
NAME VARCHAR2(10),
ZONE NUMBER(*, 0),
SQM_LAND NUMBER(8, 2),
SOLD NUMBER(*, 0),
VTPRICE_ID NUMBER,
SEAVIEW VARCHAR2(10),
COMMENTS VARCHAR2(400)
)
;

CREATE TABLE VTPRICE
(
ID NUMBER NOT NULL,
TYPE VARCHAR2(20),
SQM_ANGF NUMBER(6, 2),
SQM_VTER NUMBER(6, 2),
SQM_POOL NUMBER(5, 2)
)
;

ALTER TABLE AGENCY
ADD CONSTRAINT AGENCY_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE AGENCYCONTACTLOG
ADD CONSTRAINT AGENCYCONTACTLOG_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE APARTMENT
ADD CONSTRAINT APARTMENT_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE APPSETTINGS
ADD CONSTRAINT APPSETTINGS_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE APTPRICE
ADD CONSTRAINT APTPRICE_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE CONTACT_PERSON
ADD CONSTRAINT TABLE1_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE LEADS
ADD CONSTRAINT LEADS_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE LEADSCONTACTLOG
ADD CONSTRAINT LEADSCONTACTLOG_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE PAYMENT
ADD CONSTRAINT PAYMENT_PK PRIMARY KEY
(
UNITSOLD_ID
)
 ENABLE
;

ALTER TABLE ROLE
ADD CONSTRAINT ROLE_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE UNITSOLD
ADD CONSTRAINT UNITSOLD_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE USERPROJECT
ADD CONSTRAINT USERPROJECT_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE VILLA
ADD CONSTRAINT VILLA_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE VTPRICE
ADD CONSTRAINT VTPRICE_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE AGENCYCONTACTLOG
ADD CONSTRAINT AGENCYCONTACTLOG_AGENCY_FK FOREIGN KEY
(
AGENCY_ID
)
REFERENCES AGENCY
(
ID
) ENABLE
;

ALTER TABLE APARTMENT
ADD CONSTRAINT APARTMENT_APTPRICE_FK FOREIGN KEY
(
APTPRICE_ID
)
REFERENCES APTPRICE
(
ID
) ENABLE
;

ALTER TABLE CONTACT_PERSON
ADD CONSTRAINT CONTACT_PERSON_AGENCY_FK FOREIGN KEY
(
AGENCY_ID
)
REFERENCES AGENCY
(
ID
) ENABLE
;

ALTER TABLE LEADS
ADD CONSTRAINT LEADS_AGENCY_FK FOREIGN KEY
(
AGENCY_ID
)
REFERENCES AGENCY
(
ID
) ENABLE
;

ALTER TABLE LEADSCONTACTLOG
ADD CONSTRAINT LEADSCONTACTLOG_LEADS_FK FOREIGN KEY
(
LEADS_ID
)
REFERENCES LEADS
(
ID
) ENABLE
;

ALTER TABLE PAYMENT
ADD CONSTRAINT PAYMENT_UNITSOLD_FK FOREIGN KEY
(
UNITSOLD_ID
)
REFERENCES UNITSOLD
(
ID
) ENABLE
;

ALTER TABLE UNITSOLD
ADD CONSTRAINT UNITSOLD_APARTMENT_FK FOREIGN KEY
(
APARTMENT_ID
)
REFERENCES APARTMENT
(
ID
) ENABLE
;

ALTER TABLE UNITSOLD
ADD CONSTRAINT UNITSOLD_LEADS_FK FOREIGN KEY
(
LEADS_ID
)
REFERENCES LEADS
(
ID
) ENABLE
;

ALTER TABLE UNITSOLD
ADD CONSTRAINT UNITSOLD_VILLA_FK FOREIGN KEY
(
VILLA_ID
)
REFERENCES VILLA
(
ID
) ENABLE
;

ALTER TABLE UNITSOLD
ADD CONSTRAINT UNITSOLD_AGENCY_FK FOREIGN KEY
(
AGENCY_ID
)
REFERENCES AGENCY
(
ID
) ENABLE
;

ALTER TABLE USERPROJECT
ADD CONSTRAINT USERPROJECT_ROLE_FK FOREIGN KEY
(
ROLE_ID
)
REFERENCES ROLE
(
ID
) ENABLE
;

ALTER TABLE VILLA
ADD CONSTRAINT VILLA_VTPRICE_FK FOREIGN KEY
(
VTPRICE_ID
)
REFERENCES VTPRICE
(
ID
) ENABLE
;

CREATE INDEX AGENCY_INDEX_AG ON AGENCY (AGENCY ASC);

CREATE INDEX AGENCY_INDEX_CFN ON AGENCY (C_NAME ASC);

CREATE INDEX LEADS_INDEX_FNAME ON LEADS (FIRST_NAME ASC);

CREATE INDEX LEADS_INDEX_LNAME ON LEADS (LAST_NAME ASC);

Looks like a bug in CachedRowSetXImpl5 oracleFixup :

oracleFixup modifies the query so metadata can be retrieved.

Error is ORA-00921: unexpected end of SQL command

The statement with the problem gets truncated to 

 SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME ||  ' ' || CONTACT_PERSON.L_NAME) AS
CONTACT_NAME, AGENCY.EMAIL, (SELECT COUNT(LDS.ID) FROM LEADS LDS  WHERE 1=0 GROUP BY AGENCY.AGENCY, AGENCY.COUNTRY,
AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID ORDER BY AGENCY.CREATED_DATE

which is an incomplete SQL statement.

oracleFixup is a hack to get applications that use the Oracle 9 driver to work.

However, there is no check for the Oracle version, only if the database is Oracle

The underlying problem is with the Oracle driver.

Oracle cannot retrieve the metadata for this SQL
java.lang.RuntimeException: java.sql.SQLException: statement handle not executed: getMetaData
Oracle error code is 17144

The standard SQL Editor seems to be able to obtain the metadata, so I'll need to find out how it retrieves it

I see that DBMetaDataFactory in db.dataview has some workarounds for Oracle.
This looks like a risky fix.

I think the best bet is for the user to simplify the query.

Could you use views to simplify this ?

Hi John,

I will make the view, but if I change the database to MySQl, will it work fine? the same sql?

thank you,

Vitor

you mean that Oracle modifies this query that I made

SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME ||
' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, 
(SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS
LDSPROVIDED


to this.....

/**The statement with the problem gets truncated to **/

 SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME ||  ' ' || CONTACT_PERSON.L_NAME) AS
CONTACT_NAME, AGENCY.EMAIL,/** (   SELECT COUNT(LDS.ID  ) **/FROM LEADS LDS  WHERE 1=0 GROUP BY AGENCY.AGENCY,
AGENCY.COUNTRY,
AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID ORDER BY AGENCY.CREATED_DATE

which is an incomplete SQL statement.

or you created this sql?

MySQL should work - SQL may be slightly different though.


As a result of the Oracle JDBC limitation, NetBeans - Visual Web modifies the query.

It's too risky to fix Visual Web to compensate for the Oracle bug.   Any change could affect other users' applications
that use Oracle.

Reporter can use MySQL as a workaround.

Well! thank you very much for your attention John! you were quite helpful!!! I made a view to compensate this lack. do
you know if there is any plans to change the visual faces-config.xml to make the arrows free to be moved?? 

have a nice day,

Vitor

your welcome.

No, I don't think arrows can be moved or changed and I don't know of any plans to enhance the faces-config editor

This is really an Oracle bug that is worked-around by Visual Web.  The workaround just doesn't work for very complicated
queries that involve  subqueries with group-by

I see. But, if I try to use MySQl, will it work with concatenation, sub queries and so on?

Maybe these will help:

http://www.mysqltutorial.org/mysql-join-subqueries.aspx
http://www.dovka.org/blog/category/mysql
http://help.godaddy.com/article/2384
http://placementpapers.net/helpingroot/Paper/MySQL-Query-Interview-Question-Part-7

Thank you for the links, but I meant if the queries will work with Netbeans, visual JSF and MySQL, like I am doing with
Oracle. But it is okay. I will find out later. 

thank you very much for you help John,


Vitor