一个DB2 SQL语句报错,求高人解答,本人新手.解决思路

一个DB2 SQL语句报错,求高人解答,本人新手.

SQL code

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F100, BF7, BF8, BF33 FROM ( SELECT ROW_NUMBER() OVER() AS RNUM FROM ( SELECT A.F1 AS F1, A.F2 AS F2, A.F3 AS F3, A.F4 AS F4, A.F5 AS F5, A.F6 AS F6, A.F7 AS F7, A.F8 AS F8, A.F9 AS F9, A.F10 AS F10, A.F11 AS F11, A.F12 AS F12, A.F13 AS F13, A.F14 AS F14, A.F15 AS F15, A.F16 AS F16, A.F17 AS F17, A.F18 AS F18, A.F19 AS F19, A.F20 AS F20, A.F21 AS F21, A.F100 AS F100, B.F7 AS BF7, B.F8 AS BF8, B.F33 AS BF33 FROM MBN001 AS A LEFT JOIN USKREC01 AS B ON B.F3 = A.F3 Where 1 = 1 AND F9 LIKE 'PA0101%'  ORDER BY F2,F1) ) AS AA WHERE RNUM>=0 AND RNUM<=60

只有2个表的做链接分页查询,
数据报错:
sqlcode:-0204
sqlstate:42704

两个表的ddl如下:

SQL code

CREATE TABLE OMF03.MBN001(   F1                             VARCHAR2(10) NOT NULL,    F2                             VARCHAR2(8)  NOT NULL,    F3                             VARCHAR2(6),    F4                             VARCHAR2(3),    F5                             VARCHAR2(5),    F6                             VARCHAR2(2),    F7                             NUMBER,    F8                             VARCHAR2(2),    F9                             VARCHAR2(8),    F10                            VARCHAR2(13),    F11                            VARCHAR2(10),    F12                            VARCHAR2(10),    F13                            VARCHAR2(8),    F14                            VARCHAR2(8),    F15                            VARCHAR2(2),    F16                            VARCHAR2(3),    F17                            VARCHAR2(5),    F18                            VARCHAR2(2),    F19                            VARCHAR2(2),    F20                            VARCHAR2(8),    F21                            VARCHAR2(6),    F100                           VARCHAR2(20) NOT NULL);

SQL code

CREATE TABLE OMF03.USKREC01(   F1                             VARCHAR2(2),    F2                             VARCHAR2(2),    F3                             VARCHAR2(6),    F4                             VARCHAR2(4),    F5                             VARCHAR2(6),    F6                             VARCHAR2(35),    F7                             VARCHAR2(3),    F8                             VARCHAR2(5),    F9                             VARCHAR2(1),    F10                            VARCHAR2(14),    F11                            VARCHAR2(3),    F12                            VARCHAR2(35),    F13                            VARCHAR2(35),    F14                            VARCHAR2(5),    F15                            VARCHAR2(3),    F16                            VARCHAR2(2),    F17                            VARCHAR2(2),    F18                            VARCHAR2(2),    F19                            VARCHAR2(2),    F20                            VARCHAR2(2),    F21                            NUMBER,    F22                            VARCHAR2(1),    F23                            VARCHAR2(1),    F24                            VARCHAR2(1),    F25                            VARCHAR2(5),    F26                            VARCHAR2(1),    F27                            VARCHAR2(1),    F28                            VARCHAR2(3),    F29                            VARCHAR2(2),    F30                            VARCHAR2(1),    F31                            VARCHAR2(7),    F32                            VARCHAR2(6),    F33                            VARCHAR2(60),    F34                            VARCHAR2(1),    F35                            VARCHAR2(1),    F36                            VARCHAR2(60),    F37                            VARCHAR2(2),    F38                            VARCHAR2(7),    F39                            VARCHAR2(1),    F40                            VARCHAR2(7),    F41                            VARCHAR2(5),    F42                            VARCHAR2(1),    F43                            VARCHAR2(1),    F44                            NUMBER,    F45                            NUMBER,    F46                            VARCHAR2(8),    F47                            VARCHAR2(12),    F48                            VARCHAR2(2),    F49                            VARCHAR2(15),    F50                            VARCHAR2(14),    F51                            VARCHAR2(1),    F52                            VARCHAR2(1),    F53                            VARCHAR2(12)); 
 

是我SQL错误,还是数据库错误,谢谢高人了哦~~

[解决办法]
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F100, BF7, BF8, BF33 FROM 
( SELECT ROW_NUMBER() OVER() AS RNUM FROM 
( SELECT A.F1 AS F1, A.F2 AS F2, A.F3 AS F3, A.F4 AS F4, A.F5 AS F5, A.F6 AS F6, A.F7 AS F7, A.F8 AS F8, A.F9 AS F9, A.F10 AS F10, A.F11 AS F11, A.F12 AS F12, A.F13 AS F13, A.F14 AS F14, A.F15 AS F15, A.F16 AS F16, A.F17 AS F17, A.F18 AS F18, A.F19 AS F19, A.F20 AS F20, A.F21 AS F21, A.F100 AS F100, B.F7 AS BF7, B.F8 AS BF8, B.F33 AS BF33 
FROM MBN001 AS A 
LEFT JOIN USKREC01 AS B ON B.F3 = A.F3 
Where 1 = 1 AND F9 LIKE 'PA0101%'
RDER BY F2,F1) as B
) AS AA WHERE RNUM>=0 AND RNUM<=60
这样试试
[解决办法]

SQL code

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F100, BF7, BF8, BF33 FROM (  -- VV acmain 下面添加上你的这些列！    SELECT ROW_NUMBER() OVER() AS RNUM ,F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F100, BF7, BF8, BF33    FROM (         SELECT A.F1 AS F1, A.F2 AS F2, A.F3 AS F3, A.F4 AS F4, A.F5 AS F5, A.F6 AS F6, A.F7 AS F7, A.F8 AS F8, A.F9 AS F9,             A.F10 AS F10, A.F11 AS F11, A.F12 AS F12, A.F13 AS F13, A.F14 AS F14, A.F15 AS F15, A.F16 AS F16, A.F17 AS F17,             A.F18 AS F18, A.F19 AS F19, A.F20 AS F20, A.F21 AS F21, A.F100 AS F100, B.F7 AS BF7, B.F8 AS BF8, B.F33 AS BF33         FROM MBN001 AS A LEFT JOIN USKREC01 AS B ON B.F3 = A.F3         Where 1 = 1         AND A.F9 LIKE 'PA0101%'  -- acmain , F9 在两表中均有        ORDER BY F2,F1        )  as KK                -- acmain , 加上别名    ) AS AA WHERE RNUM>=0 AND RNUM<=60
[解决办法]
什么错误信息？
加上别名试试
AND a.F9 LIKE 'PA0101%' 
RDER BY F2,F1) as B 

 

查看更多 下一篇