Search Postgresql Archives

When is Like different to =

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



All,

I've a query:

SELECT
    c.code, c.pc_no, jl.event_no, jl.order_number, jl.location, s.status, 
cs.commercial_status
FROM
    ((codes as c JOIN job_list as jl ON c.id = jl.code_id) JOIN status as s 
ON jl.event_no = s.event_no)
        JOIN commercial_status AS cs on jl.event_no = cs.event_no
WHERE
    (status = 'Job Allocated') AND (code_id = 39);

Where codes and job_list are tables and status is a view:

CREATE VIEW status AS
 SELECT job_list.event_no, status(job_list.event_no) AS status
   FROM job_list
   JOIN user_codes ON job_list.code_id = user_codes.code_id
  WHERE user_codes.user_name::name = "current_user"();

CREATE FUNCTION status(int4)
  RETURNS text AS
$BODY$SELECT
 CASE
            WHEN job_cancelled IS NOT NULL THEN 'Cancelled'::text
            WHEN job_sheet_received IS NOT NULL THEN 'Job Sheet 
Received'::text
            WHEN works_complete IS NOT NULL THEN 'Works Complete'::text
            WHEN awaiting_action_id IS NOT NULL THEN 'Awaiting Action'::text
            WHEN attend_date IS NOT NULL THEN 'Job Attended'::text
            WHEN issued_to IS NOT NULL THEN 'Job Allocated'::text
            ELSE 'Not Allocated'::text
 END
FROM
 job_list
WHERE
 event_no = $1$BODY$
  LANGUAGE 'sql' STABLE;

The above query should return one row from my current database but does not. 
If I change the where clause from (status = 'Job Allocated') AND (code_id = 
39)
to
(status LIKE 'Job Allocated') AND (code_id = 39)
it does return the row.

What am I missing?

Regards,

Ben 



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux