How to count rows from two columns?

Question

I try to create the function login that takes customer number(pnr) and password from same table. Its fine to create function but test crashes with following eror: ORA-00904: "P_PASSWD": invalid identifier

create or replace function logga_in(
    p_pnr bankkund.pnr%type,
    p_passwd bankkund.passwd%type
    )
    return number
as
    v_resultat number(1);
begin
    select count(pnr) into v_resultat
    from   bankkund
    where  p_pnr = pnr
    and    p_passwd = passwd;

    return 1;
exception
    when no_data_found then
        return 0;
end;

. . That is your problem. The columns do not exist in dual. — Gordon Linoff
– Gordon Linoff, Commented Apr 14, 2019 at 12:56
How you execute this select? If you use it in some plsql code then you can use local variable with values. — Mladen S
– Mladen S, Commented Apr 14, 2019 at 13:41

Kaushik Nayak · Accepted Answer · 2019-04-14 15:21:25Z

There is one other problem with your code not suggested in the comments, A count function from a select into will not raise a NO_DATA_FOUND exception. You may use an IF condition on count or do something like this, which is preferable

CREATE OR REPLACE FUNCTION logga_in (
    p_pnr      bankkund.pnr%TYPE,
    p_passwd   bankkund.passwd%TYPE
) RETURN NUMBER AS
    v_resultat NUMBER(1);
BEGIN
SELECT 1       --do not use count if you wish to handle no_data_found
     INTO v_resultat FROM
        bankkund WHERE  pnr = p_pnr AND
        passwd = p_passwd
AND ROWNUM = 1; --Add this 
  RETURN 1;
EXCEPTION
    WHEN no_data_found THEN
        RETURN 0;
END;

Now, as far as calling the procedure is concerned, there are various options available including using bind variable

VARIABLE  p_pnr    number        --use the datatype of bankkund.pnr%TYPE
VARIABLE p_passwd VARCHAR2(10)  --use the datatype of bankkund.passwd
SELECT logga_in(:p_pnr,:p_passwd) FROM dual;

Or substitution variable

SELECT logga_in('&p_pnr','&p_passwd') FROM dual;

Give inputs when prompted.

Or use PL/SQL block

DECLARE
v_res INT;
v_pnr    bankkund.pnr%type    := 12892;  --or appropriate value
p_passwd bankkund.passwd%type := some_passwd';
BEGIN
   v_res := logga_in();
 If v_res = 1 THEN
  do_something_u_want; --call or execute appropriate action.
 END IF;
END;
/

Collectives™ on Stack Overflow

How to count rows from two columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related