下面的sql 语句,为什么得不到b.invoice_grace字段的值,我对case when 不太熟悉,请高手们帮忙。谢谢 Select Case When a.CLIENT_CODE In(select b.CLIENT_CODE from TFAT_CLIENT_PROFILE b
where b.DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and b.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR') Then b.invoice_grace(出错的地方)
Else 10
End
From TFAT_INVOICE a 请问我如何才能得到 b.invoice_grace的值呢?,前提是我必须用case when
where b.DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and b.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR') Then b.invoice_grace(出错的地方)
Else 10
End
From TFAT_INVOICE a 请问我如何才能得到 b.invoice_grace的值呢?,前提是我必须用case when
where b.DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and b.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR') Then b.invoice_grace(出错的地方)
Else 10
End
From TFAT_INVOICE a
b是在when里面定义的,不能在外面访问
你的when条件是一个in条件,也就是子查询有可能返回多值,所以即便你指定的b.invoice_grace在访问范围内,也是一个集合,这是不允许的,只能出现单值,否则数据库无法返回结果集了。这跟select * from a where colum =(select colum from b)
这个子查询中不能返回多记录是一样的道理。
select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' “?”这里的约束是: 如果 TFAT_INVOICE 中的 client_code 存在于TFAT_CLIENT_PROFILE 中,就取TFAT_CLIENT_PROFILE的invoice_grace(一个具体的数字),反之取10.
依次我用一个case when 语句来代替 "?"
(Select Case When a.CLIENT_CODE = select b.CLIENT_CODE from TFAT_CLIENT_PROFILE b
where DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR' Then b.invoice_grace
Else 10
End
From TFAT_INVOICE a) 我不知道如何做?请高手们给我写一个语句。(条件就是我上面说的:在TFAT_INVOICE 中查找INVOICE_ID,INVOICE_MATURITYDATE字段,如果TFAT_INVOICE 中的client_code 存在于TFAT_CLIENT_PROFILE 中,就取TFAT_CLIENT_PROFILE的invoice_grace(一个具体的数字),反之取10.
)
select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' “?”这里的约束是: 如果 TFAT_INVOICE 中的 client_code 存在于TFAT_CLIENT_PROFILE 中,就取TFAT_CLIENT_PROFILE的invoice_grace(一个具体的数字),反之取10.
因此我用一个case when 语句来代替 "?"
(Select Case When a.CLIENT_CODE = select b.CLIENT_CODE from TFAT_CLIENT_PROFILE b
where DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR' Then b.invoice_grace
Else 10
End
From TFAT_INVOICE a) 我不知道如何做?请高手们给我写一个语句。(条件就是我上面说的:在TFAT_INVOICE 中查找INVOICE_ID,INVOICE_MATURITYDATE字段,如果TFAT_INVOICE 中的client_code 存在于TFAT_CLIENT_PROFILE 中,就取TFAT_CLIENT_PROFILE的invoice_grace(一个具体的数字),反之取10.
)
TFAT_INVOICE 和 TFAT_CLIENT_PROFILE的关联条件就是上面的 a.site_code = b.site_code And a.client_code=b.client_code
1.取出TFAT_INVOICE表中的3个字段INVOICE_ID,INVOICE_MATURITYDATE
2.如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10
3.TFAT_INVOICE 和TFAT_CLIENT_PROFILE表中的关联字段是:SITE_CODE和CLIENT_CODEselect distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' 关键是"?"处的语句如何写,或许我的上面的sql语句有问题。请高手们帮忙!谢谢
select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE c where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE +
(
select nvl(invoice_grace,10)
from TFAT_INVOICE a, TFAT_CLIENT_PROFILE b
where a.CLIENT_CODE=*b.CLIENT_CODE and a.INVOICE_ID=c.INVOICE_ID
)
<= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D'
以上语句不精确,是Oracle语法,仅供参考思路。
1.我涉及的表就是2个TFAT_INVOICE和TFAT_CLIENT_PROFILE(关联字段是:SITE_CODE和CLIENT_CODE )
2.我想从TFAT_INVOICE表中取出3个字段INVOICE_ID,client_code,INVOICE_MATURITYDATE
3.其中有一个条件限制,就是TFAT_INVOICE表中 INVOICE_MATURITYDATE + "?"<= TO_DATE('2009-05-23','yyyy-mm-dd')
因为TFAT_INVOICE的CLIENT_CODE,有可能再TFAT_CLIENT_PROFILE中(如果存在,就把和CLIENT_CODE对应的invoice_grace取出来 ),也有可能不在TFAT_CLIENT_PROFILE中(如果不存在,就取10)
因此上面得 ”?“一定是一个 SQL帮帮忙 啊 ,不管用什么方法,只要能实现就可以 。
select a.INVOICE_ID,a.client_code,a.INVOICE_MATURITYDATE
from TFAT_INVOICE a left outer join TFAT_CLIENT_PROFILE b on
a.CLIENT_CODE=b.CLIENT_CODE and a.SITE_CODE=b.SITE_CODE
where
INVOICE_MATURITYDATE + nvl(b.INVOICE_GRACE,10)
<= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D'
我自己建了表测了,没问题,楼主不要不知足,自己要动脑子。