postgres concat two field

CASE WHEN contact_list.group_role = '1' THEN 'Main Contact' WHEN contact_list.group_role = 'developer' THEN 'Developer Contact' ELSE '' END AS contact_group,
contact_list.group_desc,

I have contact list select statement that I want to join contact_group with contact_list.group_desc after the case statement. Just wondering is this possible? I have tried the following but it doesn't seem to work.

contact_group || ' ' || group.desc AS complate_group_title

What's the exact error you're getting? I usually don't have problems with string concatenations, although I haven't had to use them after a case.

Can you print out the complete query and the error?

-b

You can't use an alias other than in order by.

You could write is a derived table



Btw, it's not a case statement, it's a case expression.

Hi Thank for the example.

This is my complete sql

SELECT project.project_name,
contact_group ||' ' || g.group_desc AS complate_group_title from
(Select CASE WHEN contact_list.group_role = '1' THEN 'Main Contact' WHEN contact_list.group_role = 'developer' THEN 'Developer Contact' ELSE '' END AS contact_group, contact_list.group_desc
from contact_list) as g,
CASE WHEN (contact_list.priority = 1) THEN 'Primary Contact' WHEN (contact_list.priority = 2) THEN 'Secondary Contact' ELSE 'Management Contact' END AS contact_priority,
contact_person.lastname || ', ' || contact_person.firstname AS name,
contact_person.mobileph,
contact_person.workph,
contact_person.workext,
contact_person.fax,
contact_person.pager,
contact_person.otherph,
contact_person.email,
contact_person.comments,
contact_person.title,
contact_person.ic,
contact_person.cpid,
contact_person.modifydate,
contact_list.pid FROM project,
contact_list,
contact_person WHERE ((project.pid = contact_list.pid) AND (contact_person.cpid = contact_list.cpid)) ORDER BY project.project_name,
contact_list.group_role,
contact_list.group_desc,
contact_list.priority;

The error message I got is parse error near CASE

I forgot to post my table as well.

CREATE TABLE "contact_person" (
"cpid" int4 DEFAULT nextval('public.contact_person_cpid_seq') NOT NULL ,
"modifydate" timestamp without time zone,
"firstname" varchar(50),
"lastname" varchar(50),
"pager" varchar(15),
"workph" varchar(15),
"mobileph" varchar(15),
"otherph" varchar(15),
"email" varchar(50),
"comments" text,
"fax" varchar(15),
"title" varchar(50),
"ic" varchar(15),
"workext" varchar(5),
CONSTRAINT "contact_person_pkey" PRIMARY KEY ("cpid")
);

CREATE TABLE "contact_list" (
"cl_id" int4 DEFAULT nextval('public.contact_list_cl_id_seq') NOT NULL ,
"pid" int4 NOT NULL ,
"cpid" int4 NOT NULL ,
"priority" int2,
"group_desc" varchar(30),
"group_role" varchar(15),
CONSTRAINT "cpid_delete" FOREIGN KEY ("cpid") REFERENCES contact_person(cpid) MATCH FULL ,
CONSTRAINT "pid_delete" FOREIGN KEY ("pid") REFERENCES project(pid) MATCH FULL
);

CREATE TABLE "project" (
"pid" int4 DEFAULT nextval('public.project_pid_seq') NOT NULL ,
"project_name" varchar(50),
CONSTRAINT "project_pkey" PRIMARY KEY ("pid")
);


I want to join contact_list.group_role with contact_list.group_desc but group_role I need to replace the following value

case 1
Main Contact
Case 2
Developer Contact
Case 3
Null

Thx for posting your full query and table setup. Try this query to see if it's what you want:

SELECT project.project_name,
g.contact_group ||' ' || g.group_desc AS complate_group_title,
contact_person.lastname || ', ' || contact_person.firstname AS name,
CASE WHEN (contact_list.priority = 1) THEN 'Primary Contact' WHEN (contact_list.priority = 2) THEN 'Secondary Contact' ELSE 'Management Contact' END AS contact_priority,
contact_person.mobileph,
contact_person.workph,
contact_person.workext,
contact_person.fax,
contact_person.pager,
contact_person.otherph,
contact_person.email,
contact_person.comments,
contact_person.title,
contact_person.ic,
contact_person.cpid,
contact_person.modifydate,
contact_list.pid
FROM project,
(SELECT (CASE WHEN contact_list.group_role = '1' THEN 'Main Contact' WHEN contact_list.group_role = 'developer' THEN 'Developer Contact' ELSE '' END) AS contact_group, contact_list.group_desc FROM contact_list) as g,
contact_list,
contact_person WHERE project.pid = contact_list.pid AND contact_person.cpid = contact_list.cpid
ORDER BY project.project_name,
contact_list.group_role,
contact_list.group_desc,
contact_list.priority

The query runs in a db I created from your table setup, but since I don't have any data I'm not exactly sure if it's what you want. If it's not exactly what you were looking for, hopefully it'll get you moving in the right direction, your original query had 2 FROM clauses.

HTH
-b

Error: ERROR: current transaction is aborted, queries ignored until end of transaction block

This is very interesting error. I tried to figure out