pears.CollectionsGetRecord

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."CollectionsGetRecord"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pCollectionID" CHAR(20) ) 
RESULT( "CollectionID" CHAR(20),"Parent" CHAR(20),"Name" CHAR(60),"StaffID" CHAR(20),"IDType" CHAR(2),"ID" CHAR(20),
  "CollectionTypeID" CHAR(20),"WhenCreated" TIMESTAMP,"WhoCreated" CHAR(20),"WhenArchived" TIMESTAMP,"WhoArchived" CHAR(20),
  "DateFrom" DATE,"DateTo" DATE,"Notes" long VARCHAR,"OtherNotes" long VARCHAR,"SortOrder" INTEGER,"IsTemplate" SMALLINT,
  "ItemTypeDescription" CHAR(20),"ItemDescription" CHAR(250),"ParentType" CHAR(1),"ParentDescription" CHAR(250),"CollectionTagLocation" CHAR(3) ) 
BEGIN
  SELECT "CollectionID","Parent","collection"."Name","staffid","IDType","ID","collection"."CollectionTypeID","WhenCreated",
    "WhoCreated","WhenArchived","WhoArchived","DateFrom","DateTo","Notes","OtherNotes","collection"."sortorder","IsTemplate",
    "CollectionGetItemTypeName"("idtype") AS "ItemTypeDescription",
    "CollectionItemDescription"("collectionid","idtype","id") AS "ItemDescription",
    (SELECT "IDType" FROM "collection" AS "cc" WHERE "cc"."collectionid" = "collection"."parent") AS "ParentType",
    "CollectionItemDescription"("collectionid","Parenttype","Parent") AS "ParentDescription",
    "string"('J',"collectiontype"."QuestionLink") AS "CollectionTagLocation"
    FROM "pears"."Collection" KEY JOIN "collectiontype" WHERE "collectionID" = "pCollectionID" ORDER BY "collection"."sortorder" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CollectionsGetRecord" IS 
{CREATE PROCEDURE CollectionsGetRecord 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN pCollectionID CHAR(20))
 RESULT( CollectionID CHAR(20),Parent CHAR(20),Name CHAR(60),StaffID CHAR(20),IDType CHAR(2),ID CHAR(20),
CollectionTypeID CHAR(20),WhenCreated TIMESTAMP,WhoCreated CHAR(20),WhenArchived TIMESTAMP,WhoArchived CHAR(20),
DateFrom DATE,DateTo DATE,Notes long VARCHAR,OtherNotes long VARCHAR,SortOrder  INTEGER,IsTemplate SMALLINT,
ItemTypeDescription CHAR(20),ItemDescription CHAR(250),  ParentType CHAR(1), ParentDescription CHAR(250), CollectionTagLocation CHAR(3))
BEGIN
 SELECT  CollectionID,Parent,collection.Name,staffid,IDType,ID,collection.CollectionTypeID,WhenCreated,
  WhoCreated,WhenArchived,WhoArchived,DateFrom,DateTo,Notes, OtherNotes,collection.sortorder,IsTemplate,
  CollectionGetItemTypeName (idtype)   AS ItemTypeDescription ,
  CollectionItemDescription (collectionid, idtype, id) AS ItemDescription,
  (SELECT IDType FROM collection cc WHERE cc.collectionid = collection.parent) AS ParentType,
  CollectionItemDescription (collectionid, Parenttype, Parent)   AS ParentDescription,
  string('J',collectiontype.QuestionLink) AS CollectionTagLocation
  FROM "pears".Collection KEY JOIN collectiontype WHERE collectionID = pCollectionID ORDER BY collection.sortorder
END
}