Im a bit stuck im trying to append a query in Postgres Sql into a qvd file, when I run the append query it is telling me there is an error in the where not exists statement highlighted red below. can anyone tell me how I am going wrong ?
[ORIGINAL QVD STATEMENT]
Concatenate
select q.* from
(
SELECT
o.orderid,
o.orderid||'-'||oa.orderatomid as primarykey,
o.status,
customer_name(c.custid) as custname,
c.finance_ref,
oa.orderatomid,
oa.state,
a.ldesc,
s.lname as supplier_name,
(
SELECT
CONCAT(oap.value)
FROM
orderatomparams oap
INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)
WHERE
oap.orderatomid = oa.orderatomid
AND ap.sdesc = 'CBUK'
AND oap.value IS NOT NULL
AND oap.value != ''
) as cbuk_nos,
(
SELECT
CONCAT(oap.value)
FROM
orderatomparams oap
INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)
WHERE
oap.orderatomid = oa.orderatomid
AND ap.sdesc = 'BBEU'
AND oap.value IS NOT NULL
AND oap.value != ''
) as bbeu_refs,
(
SELECT
CONCAT(oap.value)
FROM
orderatomparams oap
INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)
WHERE
oap.orderatomid = oa.orderatomid
AND ap.sdesc = 'CIRC-REF'
AND oap.value IS NOT NULL
AND oap.value != ''
) as circrefs
FROM
orders o
INNER JOIN orderatoms oa ON (o.orderid = oa.orderid)
INNER JOIN atoms a ON (a.atomid = oa.atomid)
INNER JOIN customer c ON (COALESCE(o.billcustid, o.custid) = c.custid)
LEFT JOIN supplier s ON (s._id = oa.supplier_id)
WHERE
a.grpid IN ('DSL')
AND o.commissiondate IS NOT NULL
) q
where
(q.circrefs IS NOT NULL AND q.circrefs != '')
OR (q.cbuk_nos IS NOT NULL AND q.cbuk_nos != '')
OR (q.bbeu_refs IS NOT NULL AND q.bbeu_refs != '')
Thanks Sergey, it seems to be not recognising my primary key do you know why it is doing it ? Is strange as I've done an incremental load before without dates and it seemed to work okay using this method
