Hi,
I have a table
CREATE TABLE "HR"."Participant_Names"
(
"PIP" int4 NOT NULL,
"LastNAME" varchar(32) NOT NULL,
"FirstName" varchar(20) NOT NULL,
"NameUsed" varchar(20),
CONSTRAINT "pkey_PIP" PRIMARY KEY ("PIP"),
)
WITHOUT OIDS;
and a view
CREATE OR REPLACE VIEW "HR"."tvw_EmployeeNames_ActiveAndInactive" AS
SELECT "Participant_Names"."PIP" AS "employee_ID",
CASE
WHEN "Participant_Names"."NameUsed" IS NULL THEN
("Participant_Names"."LastNAME" || ', ') || "Participant_Names"."FirstName"
ELSE ("Participant_Names"."LastNAME" || ', ') ||
"Participant_Names"."NameUsed"
END AS "employeeName"
FROM "HR"."Participant_Names"
ORDER BY
CASE
WHEN "Participant_Names"."NameUsed" IS NULL THEN
("Participant_Names"."LastNAME" || ', ') || "Participant_Names"."FirstName"
ELSE ("Participant_Names"."LastNAME" || ', '::text) ||
"Participant_Names"."NameUsed"
END;
which when viewed in pgAdmin seems to be automatically cast of text
CREATE OR REPLACE VIEW "HR"."tvw_EmployeeNames_ActiveAndInactive" AS
SELECT "Participant_Names"."PIP" AS "employee_ID",
CASE
WHEN "Participant_Names"."NameUsed" IS NULL THEN
("Participant_Names"."LastNAME"::text || ', '::text) ||
"Participant_Names"."FirstName"::text
ELSE ("Participant_Names"."LastNAME"::text || ', '::text) ||
"Participant_Names"."NameUsed"::text
END AS "employeeName"
FROM "HR"."Participant_Names"
ORDER BY
CASE
WHEN "Participant_Names"."NameUsed" IS NULL THEN
("Participant_Names"."LastNAME"::text || ', '::text) ||
"Participant_Names"."FirstName"::text
ELSE ("Participant_Names"."LastNAME"::text || ', '::text) ||
"Participant_Names"."NameUsed"::text
END;
We are using Access as a front-end and when I link to the view I get
Field Name Data Type
employee_ID Number
employeeName Memo
I need employeeName to be a text field in Access. I have tried casting
the fields in the view as varchar, but it seem to default back to ::text.
Any help would be greatly appreciated.
Thanks,
Karen
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match