现有两个表:
header table :
segment type error_code
1 I YES
2 M YES_M(注:type的值只有两种:I/M )line table :
segment qyt price error_msg line_num
1 10 12 YES 1
1 11 13 YES_S 2
1 130 16 YES 3
2 56 1000 NO 1这两个表通过segment关连,一对多!一个头表可以有多个行!当header.type='I'时
只要求显示单独一行信息,信息内容包含:header.*,line.qyt,line.price,
当header.type='M'时
显示所有行,header.*,line.*最好用视图!不考虑用procedure,function 现实!
谢谢!
谢谢!
header table :
segment type error_code
1 I YES
2 M YES_M(注:type的值只有两种:I/M )line table :
segment qyt price error_msg line_num
1 10 12 YES 1
1 11 13 YES_S 2
1 130 16 YES 3
2 56 1000 NO 1这两个表通过segment关连,一对多!一个头表可以有多个行!当header.type='I'时
只要求显示单独一行信息,信息内容包含:header.*,line.qyt,line.price,
当header.type='M'时
显示所有行,header.*,line.*最好用视图!不考虑用procedure,function 现实!
谢谢!
谢谢!
如果不用楼上的办法。那就还有一个
select header.*,decode(header.type='I',line.qyt||line.price,line.qyt||line.price==) from header,line where line.segment=header.segment
select header.*, 'DUMMY' as segment, line.qyt as qyt,
line.price as price, 'DUMMY' as error_msg,
'DUMMY' as line_num
from header, line
where header.segment = line.segment
and header.type = 'I'
union
select header.*, to_char(line.segment) as segment, line.qyt as qyt,
line.price as price, line.error_msg as error_msg,
to_char(line.line_num) as line_num
from header, line
where header.segment = line.segment
and header.type = 'M'结果中显示为DUMMY的就是你不想显示的字段喽
CREATE OR REPLACE VIEW v_header_line
AS
SELECT h.SEGMENT, h.TYPE, h.ERROR_CODE, l.qyt, l.price, l.error_msg,
l.line_num
FROM header h, line l
WHERE l.SEGMENT = h.SEGMENT AND h.TYPE = 'I' AND ROWNUM = 1
UNION ALL
SELECT h.SEGMENT, h.TYPE, h.ERROR_CODE, l.qyt, l.price, l.error_msg,
l.line_num
FROM header h, line l
WHERE l.SEGMENT = h.SEGMENT AND h.TYPE = 'Y'
CREATE OR REPLACE VIEW v_header_line
AS
SELECT h.SEGMENT, h.TYPE, h.ERROR_CODE, l.qyt, l.price, null error_msg,
null line_num
FROM header h, line l
WHERE l.SEGMENT = h.SEGMENT AND h.TYPE = 'I' AND ROWNUM = 1
UNION ALL
SELECT h.SEGMENT, h.TYPE, h.ERROR_CODE, l.qyt, l.price, l.error_msg,
l.line_num
FROM header h, line l
WHERE l.SEGMENT = h.SEGMENT AND h.TYPE = 'Y'