SQL (Join) from Oracle dont run in tOracleInput

[wmacke]

Hi i have a prob with the following SQL:

" SELECT D.cid, D.DLayAusgabe, D.SeitNr, dlayressort,  dlayresslang,farbplan, D.Durchlaufseite, R.destid, to_char(D.cgd,'dd.mm.yyyy hh24:mi:ss') cgd, To_Char(D.ced,'dd.mm.yyyy:mi:ss') ced
FROM refs R, Dlayout D
WHERE
(
 R.srcid(+) = D.cid
 and
 R.srcattr(+) = 'LayoutMaster'
)
AND
D.cid IN
(
SELECT destid FROM refs WHERE srcid in
(
SELECT
  cid
FROM
  ddruckprodukt
WHERE
cid IN
  (SELECT destid FROM refs WHERE srcid IN
    (SELECT cid FROM dspiegel WHERE pperscham <= To_Date ('01.04.2010', 'DD.MM.YYYY') and pperscham >= To_Date ('01.01.2009', 'DD.MM.YYYY')))
AND
  PPAusgabe LIKE '1%'
AND
  PPAusgabe NOT IN ('112','122','123','125','126','142')
AND
   PPProduktTyp = 'hp'
)
)
ORDER BY DLayAusgabe "

 

At the normal console the Script will run with out Prob's. And in the JasperETL it will give me the following result:

" [statistics] connecting to socket on port 4320
[statistics] connected
Exception in component tOracleInput_1
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
    at java.sql.Timestamp.valueOf(Unknown Source)
    at oracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:486)
    at oracle.jdbc.driver.T4CVarcharAccessor.getTimestamp(T4CVarcharAccessor.java:897)
    at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1060)
    at blattplannung.ngtst_dbextraktion_blattplanung_inhalt_0_1.NGTST_DBExtraktion_Blattplanung_Inhalt.tOracleInput_1Process(NGTST_DBExtraktion_Blattplanung_Inhalt.java:1084)
    at blattplannung.ngtst_dbextraktion_blattplanung_inhalt_0_1.NGTST_DBExtraktion_Blattplanung_Inhalt.runJobInTOS(NGTST_DBExtraktion_Blattplanung_Inhalt.java:1894)
    at blattplannung.ngtst_dbextraktion_blattplanung_inhalt_0_1.NGTST_DBExtraktion_Blattplanung_Inhalt.main(NGTST_DBExtraktion_Blattplanung_Inhalt.java:1787)
[statistics] disconnected "

What is the Problem? The Join or the ...... ????

Many thanks for any idea's

regars Wolfgang

[trobert]

Hello!

This is only an idea:

JasperETL is a transformation tool, You can make "joins" in a graphical way, not in sql.

You need to add 2 datasources (tOracleInput).

In the first, you can select one of the tables (select * from refs), and in the other you can select the other table (select * from Dlayout).

After this, you can make a "joined" table with tMap component (tMap's inputs are the two tables above). You can select the required fields from the tables, and you can make a new output table.

 

Robert

[jakec]

The documentation for t<DB flavor>Input state that the schema of those components must exactly match the schema of the table they represent, so the most you can do in the SQL is filter out items.

 

If you want to do a join, then as Robert said, use two tOracleInput components, and either a tJoin (which does not seem to generate valid Java unless you include all fields of both tables in the resulting output) or a tMap to merge them together.

 

There is also a tELT<DB flavor>Map, which apparently generates SQL and uses the DB to do all the work, instead of generating Java. However, I have not used it yet, so your mileage may vary.