下面的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
比如表A,有个字段NUM里面有0,-1,
select
(case a.NUM when '0' then '零' when '-1' then '负一' else '其他' end)from A
建议LZ用自定义函数来实现你的需求
条件为FALSE 返回 值(2)
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语句有问题。请高手们帮忙!谢谢
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(条件是:如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10 ),
不知道这个SQL如何写,或者不用我的方法也可以,只要能实现我的要求就可以
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 c.invoice_grace
Else 10
End
From TFAT_INVOICE a,TFAT_CLIENT_PROFILE c
where c.DATA_ACTIVE='Y' And a.site_code = c.site_code And a.client_code=c.client_code and c.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR';
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' 改成这样
select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE a left join TFAT_CLIENT_PROFILE b on 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'
where ADVANCE_ID Is Not Null
And INVOICE_MATURITYDATE + nvl2(b.client_code,b.invoice_grace,10) <= TO_DATE('2009-05-23','yyyy-mm-dd')
and INVOICE_STATUS = 'D' 如果用你的语句就要写子查询,复杂点.
A.INVOICE_MATURITYDATE INVOICE_MATURITYDATE,
(CASE
WHEN A.CLIENT_CODE IN (SELECT CLIENT_CODE FROM TFAT_CLIENT_PROFILE) THEN B.INVOICE_GRACE
ELSE 10
END) CLIENT_CODE
FROM TFAT_INVOICE A, TFAT_CLIENT_PROFILE B
WHERE A.SITE_CODE = B.SITE_CODE
AND A.CLIENT_CODE = B.CLIENT_CODE
大意如上
另,楼上,关连字段怎么变得这么丰富了?