ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML149  

-- SQL Test Suite, V6.0, Interactive SQL, dml149.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O   ROLLBACK WORK;

-- date_time print

-- TEST:0561 Double SET TRANSACTION!

   ROLLBACK WORK;
ij> -- PASS:0561 If successful completion?

--O   SET TRANSACTION READ ONLY;
-- PASS:0561 If successful completion?

--O   SET TRANSACTION READ WRITE;
-- PASS:0561 If successful completion?

--O   INSERT INTO USIG VALUES (10, 20);
-- PASS:0561 If 1 row is inserted?

--O   ROLLBACK WORK;
-- PASS:0561 If successful completion?

--O   SET TRANSACTION READ WRITE;
-- PASS:0561 If successful completion?

--O   SET TRANSACTION READ ONLY;
-- PASS:0561 If successful completion?

--O   INSERT INTO USIG VALUES (10, 20);
-- PASS:0561 If ERROR, invalid transaction state, 0 rows inserted?

--O   ROLLBACK WORK;
-- PASS:0561 If successful completion?

-- END TEST >>> 0561 <<< END TEST

-- *********************************************

-- TEST:0846 Feature 20, CAST functions (static) nits!

   CREATE TABLE NO_DUCK (
  GOOSE       NUMERIC (4, 2),
  ALBATROSS   FLOAT,
  SEAGULL     INT,
  OSPREY      CHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0846 If table is created?

   COMMIT WORK;
ij> -- NOTE:0846 CAST (100 AS NUMERIC (2)) loses the leading significant digit
   SELECT CAST (100 AS NUMERIC (2))
  FROM HU.ECCO;
1  
---
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(2,0).
ij> -- PASS:0846 If ERROR, numeric value out of range, 0 rows selected?

   SELECT CAST (100.5 AS DECIMAL (3))
  FROM HU.ECCO;
1   
----
100 
ij> -- PASS:0846 If 1 row selected and value is 100 or 101?

   INSERT INTO NO_DUCK VALUES (
  CAST ('  23.23 ' AS NUMERIC (4, 2)), 1.57E-1, -9, 'QUACK');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?

   SELECT COUNT(*)
  FROM NO_DUCK WHERE GOOSE = 23.23;
1          
-----------
1          
ij> -- PASS:0846 If count = 1?

-- No cast from CHAR to FLOAT allowed
--   DELETE FROM NO_DUCK
--  WHERE ALBATROSS - CAST ('   15.5E0    ' AS FLOAT) < 3E-1;
-- PASS:0846 If 1 row is deleted?
-- Instead to keep the rest of the test in sync we will:
DELETE FROM NO_DUCK
WHERE ALBATROSS - CAST (15.5E0  AS FLOAT) < 3E-1;
1 row inserted/updated/deleted
ij> SELECT COUNT(*) FROM NO_DUCK;
1          
-----------
0          
ij> -- PASS:0846 If count = 0?

   INSERT INTO NO_DUCK
--O  SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM HU.ECCO;
  SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM ECCO;
ERROR 42X05: Table/View 'ECCO' does not exist.
ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted?

   INSERT INTO NO_DUCK
  SELECT 22.22, 2.222E1, CAST (C1 AS INT), 'QUACK!' FROM HU.ECCO;
ERROR 22018: Invalid character string format for type INTEGER.
ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted?

   SELECT CAST (CAST (3 AS DEC (5, 3)) AS CHAR (5))
  FROM HU.ECCO;
1    
-----
3.000
ij> -- PASS:0846 If 1 row selected and value is '3.000'?

   INSERT INTO NO_DUCK VALUES (
  12.00, -10.5E0, 12, 'QUACK!');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?

   UPDATE NO_DUCK
  SET OSPREY = CAST (GOOSE AS CHAR (10))
  WHERE SEAGULL = CAST (GOOSE AS DEC);
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?

   SELECT OSPREY
  FROM NO_DUCK;
OSPREY    
----------
12.00     
ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00     '?

   SELECT OSPREY
  FROM NO_DUCK
  WHERE OSPREY < CAST (SEAGULL + 1 AS CHAR (10))
  AND OSPREY = CAST (GOOSE * 1 AS CHAR (10));
OSPREY    
----------
12.00     
ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00     '?

   UPDATE NO_DUCK
  SET OSPREY = CAST (-SEAGULL AS CHAR (10));
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?

   SELECT OSPREY
  FROM NO_DUCK;
OSPREY    
----------
-12       
ij> -- PASS:0846 If 1 row selected and OSPREY = '-12       '?

-- NOTE:0846 Expected value -12.00 is too long for CHAR (5) cast
   SELECT CAST (-GOOSE AS CHAR (5))
  FROM NO_DUCK;
1    
-----
-12.0
WARNING 01004: Data truncation
ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected?

   UPDATE NO_DUCK
  SET ALBATROSS = 0.0;
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?

   SELECT CAST (-ALBATROSS AS CHAR (5))
  FROM NO_DUCK;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '0E0  '?

   SELECT CAST (0230E-1 AS CHAR (10))
  FROM HU.ECCO;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '2.3E1     '?

   SELECT CAST (0230E+1 AS CHAR (10))
  FROM HU.ECCO;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '2.3E3     '?

   DELETE FROM NO_DUCK;
1 row inserted/updated/deleted
ij> INSERT INTO NO_DUCK VALUES (
  0.00, -10.5E0, -0, 'QUACK!');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?

   UPDATE NO_DUCK
  SET OSPREY = CAST (ALBATROSS AS CHAR (10))
  WHERE GOOSE = CAST (SEAGULL AS NUMERIC (2));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?

   SELECT OSPREY
  FROM NO_DUCK;
OSPREY    
----------
QUACK!    
ij> -- PASS:0846 If 1 row selected and OSPREY = '-1.05E1   '?

   UPDATE NO_DUCK SET ALBATROSS = -0.5;
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?

   UPDATE NO_DUCK
  SET OSPREY = CAST (ALBATROSS AS CHAR (10));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?

   SELECT OSPREY
  FROM NO_DUCK;
OSPREY    
----------
QUACK!    
ij> -- PASS:0846 If 1 row selected and OSPREY = '-5E-1     '?

   UPDATE NO_DUCK
  SET OSPREY = CAST (-ALBATROSS AS CHAR (10));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?

   SELECT OSPREY
  FROM NO_DUCK;
OSPREY    
----------
QUACK!    
ij> -- PASS:0846 If 1 row selected and OSPREY = '5E-1      '?

-- NOTE:0846 Expected value -5E-1 is too long for CHAR (4) cast
   SELECT CAST (ALBATROSS AS CHAR (4))
  FROM NO_DUCK;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected?

  SELECT CAST (NULL AS CHAR (10)), GOOSE FROM NO_DUCK
  WHERE SEAGULL = 0
  UNION
  SELECT OSPREY, CAST (SEAGULL AS NUMERIC (4, 2)) FROM NO_DUCK
  WHERE GOOSE > 10000;
1         |2      
------------------
NULL      |0.00   
ij> -- PASS:0846 If 1 row selected and first value is NULL?

   UPDATE NO_DUCK SET GOOSE =
  CAST (NULL AS NUMERIC (2, 2));
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?

   SELECT COUNT(*)
  FROM NO_DUCK WHERE GOOSE IS NULL;
1          
-----------
1          
ij> -- PASS:0846 If count = 1?

   SELECT CAST (GOOSE AS INT)
  FROM NO_DUCK;
1          
-----------
NULL       
ij> -- PASS:0846 If 1 row selected and value is NULL?

   ROLLBACK WORK;
ij> --O   DROP TABLE NO_DUCK CASCADE;
   DROP TABLE NO_DUCK ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0846 <<< END TEST

-- *************************************************////END-OF-MODULE
;
ij>