Submitted by: Arteev Alexei (arteev.a)

Duplicates CORE1434

CREATE TABLE PROPERTY_DOC (
ID_PROPERTY UID_STR /* UID_STR = VARCHAR(10) DEFAULT '0' */,
FORTABLE STR_31 COLLATE PXW_CYRL /* STR_31 = VARCHAR(31) DEFAULT '_' */,
ID_KEYTABLE UID /* UID = INTEGER DEFAULT 0 */,
VAL VARCHAR(8000),
READONLY BOOL /* BOOL = SMALLINT DEFAULT 0 */
);
set term ^ ;
CREATE PROCEDURE GET_PROP_ALL(
ATABLE VARCHAR(64),
AID_KEYTABLE INTEGER)
RETURNS (
VAL VARCHAR(8000),
READONLY SMALLINT,
ID_PROPERTY VARCHAR(10),
FORTABLE VARCHAR(64),
ID_KEYTABLE INTEGER)
AS
begin
/*.......................*/
ATABLE = upper(ATABLE );
if (AID_KEYTABLE IS NULL) then AID_KEYTABLE=0;
ID_KEYTABLE = AID_KEYTABLE;
FORTABLE = ATABLE ;
/*.......................*/
FOR
execute statement
'SELECT VAL,READONLY,ID_PROPERTY FROM property_doc WHERE FORTABLE='''||:atable||'''
AND ID_KEYTABLE = '||:AID_KEYTABLE

INTO
:val,
:readonly,
:ID_PROPERTY
DO
suspend;
end
^
set term ; ^

INSERT INTO PROPERTY_DOC (ID_PROPERTY, FORTABLE, ID_KEYTABLE, VAL, READONLY) VALUES ('DOC_SL_ALL', 'DOC', 225, '0.00', 1);
commit;

/*............*/

select VAL,ID_PROPERTY from GET_PROP_ALL('DOC',225);

Gived:
VAL ID_PROPERTY
0.00 DOC_SL_A

Must:
VAL ID_PROPERTY
0 DOC_SL_ALL

Solved:

set term ^ ;
CREATE PROCEDURE GET_PROP_ALL(
ATABLE VARCHAR(64),
AID_KEYTABLE INTEGER)
RETURNS (
VAL VARCHAR(8000),
READONLY SMALLINT,
ID_PROPERTY VARCHAR(10),
FORTABLE VARCHAR(64),
ID_KEYTABLE INTEGER)
AS
begin
/*.......................*/
ATABLE = upper(ATABLE );
if (AID_KEYTABLE IS NULL) then AID_KEYTABLE=0;
ID_KEYTABLE = AID_KEYTABLE;
FORTABLE = ATABLE ;
/*.......................*/
FOR
execute statement
'SELECT VAL,READONLY,cast(ID_PROPERTY as varchar(100)) FROM property_doc WHERE FORTABLE='''||:atable||'''
AND ID_KEYTABLE = '||:AID_KEYTABLE

INTO
:val,
:readonly,
:ID_PROPERTY
DO
suspend;
end
^
set term ; ^