topic Re: Not sure what I am doing wrong. in QlikView

Not sure what I am doing wrong.

Tue, 10 Feb 2015 18:18:19 GMT

SQL##f - SqlState: S1000, ErrorCode: 920, ErrorMsg: [Oracle][ODBC][Ora]ORA-00920: invalid relational operator

SQL SELECT
     corekey AS "corekey",
     coreid AS "coreid",
     coreconst AS "coreconst",
     corebrthyr AS "corebrthyr",
corebrthmn AS "corebrthmn",
corebrthdy AS "corebrthdy",
coreprefyr AS "coreprefyr",
coreprfsch AS "coreprfsch"
FROM corebio_full

WHERE not exists (select attrid,attrtype from attribute_full where attrid = coreid and attrtype in ('dnm','dnc','BOT','FBoD'))
AND not exists (select deathid from death_full where deathid = coreid)
AND not exists (select phnid,phnnumber from phone_full where phnid = coreid and isnull(phnnumber))

I am trying to get rid of any records that do not have a phone number

Re: Not sure what I am doing wrong.

maxgro — Tue, 10 Feb 2015 18:39:32 GMT

From a syntax point of view I think isnull doesn't exist in Oracle

Try with NVL

Re: Not sure what I am doing wrong.

prieper — Tue, 10 Feb 2015 20:32:03 GMT

EXISTS is neither a valid syntax in SQL

Script may work with preceding load (where then the QV-commands are available):

LOAD

     corekey AS "corekey",
     coreid AS "coreid",
     coreconst AS "coreconst",
     corebrthyr AS "corebrthyr",
corebrthmn AS "corebrthmn",
corebrthdy AS "corebrthdy",
coreprefyr AS "coreprefyr",
coreprfsch AS "coreprfsch"

WHERE not exists (select attrid,attrtype from attribute_full where attrid = coreid and attrtype in ('dnm','dnc','BOT','FBoD'))

AND not exists (select deathid from death_full where deathid = coreid)
AND not exists (select phnid,phnnumber from phone_full where phnid = coreid and isnull(phnnumber));

SQL SELECT * FROM ....

HTH Peter

Re: Not sure what I am doing wrong.

manojkvrajan — Tue, 10 Feb 2015 20:33:33 GMT

Tammy - I am with Massimo. Please try NVL.

Re: Not sure what I am doing wrong.

maxgro — Tue, 10 Feb 2015 21:16:43 GMT

exists (<subquery>)

and

not exists (<subquery>)

is a valid Oracle (and sql) syntax

Re: Not sure what I am doing wrong.

prieper — Wed, 11 Feb 2015 18:03:31 GMT

@ Massimo: you are correct, did not read the syntax carefully enough

Peter