topic Oracle subquery within QV script in QlikView

Oracle subquery within QV script

Wed, 08 Dec 2010 15:50:23 GMT

I have a huge table that I am trying to pre-condense into QV. Normally I would do the heavy lifting in QV, but I run out of memory. Now I have a Oracle (11g) query that works fine. When I copy & paste into QV the query fails. Error QL error: ORA-00907: missing right parenthesis...

When run in Oracle this query gives me exactly what I need, what did I miss bringing it into QV? I know once in QV I can bring in about 50 million rows with no problem. The original db has 500 million rows no way is it going to all fit into QV.

Here's the Oracle query...

Select
i.cpid,
i.model,
i.install_method,
i.os,
i.Code,
sub_query.DT
From R_externalim.logging i

inner join
(Select
l.Pin,
l.code,
Min(l.Download_time) as DT
from r_externalim.Logging l
where l.download_time >= add_months(SysDate, -3)
group by l.code, l.pin ) sub_query
on sub_query.pin = i.pin
and sub_query.code = i.code
and sub_query.DT = i.download_time
;

Thanks,

Bruce

Oracle subquery within QV script

Wed, 08 Dec 2010 16:00:56 GMT

Hello Bruce,

from my former oracle days I remember that a missing right parenthesis could have many reasons. I assume that you are able to cp/paste correctly so my first guess is that the oracle driver (odbc or oledb) isn't able to deal correctly with your statement. What if you create a view on the oracle site? This would have an addional effect of reusing it if necessary.

HtH,

Roland

Oracle subquery within QV script

Wed, 08 Dec 2010 16:52:09 GMT

Roland

That sounds like a good idea, I will ask my db admin if I can add a view onto the db. That way the Oracle driver won't have to deal with extra brackets of a sub-query. I was trying to do directly in QV but I would have 45 million rows /month to deal with.

Thanks

Oracle subquery within QV script

Wed, 08 Dec 2010 23:00:23 GMT

Personally I would write the code something like this:


Select
 i.cpid,
 i.model,
 i.install_method,
 i.os,
 i.Code,
 sub_query.DT
From R_externalim.logging i
,(Select
l.Pin,
l.code,
Min(l.Download_time) as DT
from r_externalim.Logging l
where l.download_time >= add_months(SysDate, -3)
group by l.code, l.pin ) sub_query
WHERE
 sub_query.pin = i.pin
and sub_query.code = i.code
and sub_query.DT = i.download_time
;

Oracle subquery within QV script

Thu, 09 Dec 2010 01:40:04 GMT

It turns out that I had an Oracle comment in the script (not shown here). Between QV & Oracle it could not figure out what -- Meant. When I remove the line I was able to run the query from QV into Oracle.

Cisco & Roland thanks for the help. I used both suggestions as well as got rid of the comments.