this sql won't run

[Former Member]

Why is this validating?

SELECT SBOOK.MANDT, SBOOK.AGENCYNUM,

SUM (SBOOK.FORCURAM) AS FORCURAM,

FORCURKEY, NAME, COUNTRY, CURRENCY

FROM SYSTEM.SBOOK, SYSTEM.STRAVELAG

WHERE SBOOK.AGENCYNUM = STRAVELAG.AGENCYNUM

AND SBOOK.MANDT = STRAVELAG.MANDT

GROUP BY SBOOK.MANDT, SBOOK.AGECYNUM,

FORCURKEY, NAME, COUNTRY, CURRENCY;

but not activating?

[2011-11-04 13:51:13 679]Create Table Type DDL: create type "_SYS_BIC"."xxxx/SC_AGENCY_REV/proc/tabletype/VAR_OUT" as table ("MANDT" NVARCHAR(3), "AGENCYNUM" NVARCHAR(8), "FORCURAM" FLOAT(0), "FORCURKEY" NVARCHAR(5), "NAME" NVARCHAR(25), "COUNTRY" NVARCHAR(3), "CURRENCY" NVARCHAR(5))

[2011-11-04 13:51:13 679]Create Table Type failed: SQL: sql syntax error: out of range: the number must be a value between 1 and 53 2011-11-04,10:51:09.543

[tomas-krojzl · Accepted solution]

Hello,

looking at the error "out of range" and the generated statement above - I would guess that "FLOAT(0)" is the problem..

It looks that you are creating table type as part of creation of procedure SC_AGENCY_REV. It looks that definition of VAR_OUT output parameter is wrong - please check definition of type of field FORCURAM.

If this will not help - then:

Can you please paste whole content of procedure definition?

Can you please also check definition of field FORCURAM of table SBOOK in schema SYSTEM?

Can you please describe how you defined output parameter VAR_OUT?

Tomas

[Former Member · Accepted solution]

Tomas,

that was it, FLOAT had length of '0'. time to update page 48 with a new screenshot.

thx, greg

[former_member182779]

Greg:

It's working just fine for me...I just changed SYSTEM by SFLIGHT and fetch 40 rows...

SELECT SBOOK.MANDT, SBOOK.AGENCYNUM,
 SUM (SBOOK.FORCURAM) AS FORCURAM,
 FORCURKEY, NAME, COUNTRY, CURRENCY
 FROM SFLIGHT.SBOOK, SFLIGHT.STRAVELAG
 WHERE SBOOK.AGENCYNUM = STRAVELAG.AGENCYNUM
 AND SBOOK.MANDT = STRAVELAG.MANDT
 GROUP BY SBOOK.MANDT, SBOOK.AGENCYNUM,
 FORCURKEY, NAME, COUNTRY, CURRENCY;

Are you creating a procedure or it's just a normal SQL query?

Greetings,

Blag.