When is Like different to =

From: "Ben Trewern" <ben(dot)trewern(at)_nospam_mowlem(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: When is Like different to =
Date: 2005-07-22 16:25:28
Message-ID: dbr6fj$10p3$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Solange 2005-07-22 16:50:51 PostgreSQL + windows xp + ipv6
Previous Message Roman Neuhauser 2005-07-22 15:57:35 Re: Wishlist?