SQL 쿼리 (값이 null이면 1 반환)

SQL 쿼리 (값이 null이면 1 반환)

---

시스템에 설정된 환율 값을 반환하는 쿼리가 있습니다. 모든 주문에 환율 (currate.currentrate)이있는 것은 아니므로 null 값을 반환합니다.

null 대신 1을 반환하도록 할 수 있습니까?

if 문과 같은 것은 다음과 같습니다.

 if isnull(currate.currentrate) then 1 else currate.currentrate 

다음은 내 쿼리입니다. 모든 도움에 감사드립니다!

 SELECT     orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  currrate.currentrate
 FROM         orderhed LEFT OUTER JOIN
                  currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate

---

CASE성명서 를 사용할 수 있습니다 .

SELECT 
    CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...

---

다음을 사용할 수 있습니다 COALESCE.

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    coalesce(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

또는 IsNull():

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    IsNull(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

다음은 COALESCE과 사이를 결정하는 데 도움이되는 문서입니다 IsNull.

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

---

SELECT 
    ISNULL(currate.currentrate, 1)  
FROM ...

이기는 답변보다 덜 장황하며 동일한 작업을 수행합니다.

https://msdn.microsoft.com/en-us/library/ms184325.aspx

---

SELECT orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  

case(currrate.currentrate) when null then 1 else currrate.currentrate end

FROM orderhed LEFT OUTER JOIN currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate  

---

a) 값이 null 일 때 0을 원하는 경우

SELECT isnull(PartNum,0) AS PartNumber, PartID
FROM Part

b) 값이 null 일 때 0을 원하고 그렇지 않으면 1을 원할 경우

SELECT 
  (CASE
    WHEN PartNum IS NULL THEN 0
    ELSE 1
  END) AS PartNumber,
  PartID
FROM Part

---

try like below...

CASE 
WHEN currate.currentrate is null THEN 1
ELSE currate.currentrate
END as currentrate

참고URL : https://stackoverflow.com/questions/14962970/sql-query-if-value-is-null-then-return-1