ERROR: there is no parameter $1 when creating view

Posted by idlemoments on Stack Overflow See other posts from Stack Overflow or by idlemoments
Published on 2010-04-07T14:31:08Z Indexed on 2010/04/07 14:33 UTC
Read the original article Hit count: 117

Filed under:
|

When we try to create a view within a funcion we get ERROR: there is no parameter $1. This is the sample code.

Begin

CREATE VIEW artikelnr AS
SELECT datum, 'uitgifte' as "type", CASE WHEN 'test'='test' THEN 0 END as "aantal ontvangen", aantal as "aantal uitgegeven"
FROM uitgifteregel 
JOIN artikel ON artikel.artikelnr = new.artikelnr
JOIN uitgifte ON uitgifte.uitgiftenr = uitgifteregel.uitgiftenr

UNION
SELECT datum, 'ontvangst' as "type", aantal as "aantal ontvangen" , CASE WHEN 'test'='test' THEN 0 END as "aantal uitgegeven"
FROM ontvangstregel 
JOIN artikel ON artikel.artikelnr = new.artikelnr
JOIN ontvangst ON ontvangst.ontvangstnr = ontvangstregel.ontvangstnr;
Return new; 
end;

When we replace new.artikelnr on line 7 with value 1 it works like it should, but the function needs to work with different artikelnr's.

example line 7: JOIN artikel ON artikel.artikelnr = new.artikelnr

Please point us in the right direction.

© Stack Overflow or respective owner

Related posts about sql

Related posts about views