#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    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
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    You can't use an alias other than in order by.

    You could write is a derived table

    Code:
    select contact_group || ' ' || g.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
    Btw, it's not a case statement, it's a case expression.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12
    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
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12
    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
  10. #6
  11. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12
    Error: ERROR: current transaction is aborted, queries ignored until end of transaction block

    This is very interesting error. I tried to figure out

IMN logo majestic logo threadwatch logo seochat tools logo