0

I have a table named INVOICE_ET which has column of below definition

INVDATE CHARACTER 10

result of select INVDATE from INVOICE_ET is as below

INVDATE
2021-07-31

In another table INVOICE_DY, I have a column named INV_DAYS, which has below definition

INV_DAYS  DECIMAL 3 

result of select INV_DAYS from INVOICE_DY is as below

INV_DAYS 
88 

Now my question is, I am trying to add INVOICE_DY.INV_DAYS field with INVOICE_ET.INVDATE field, but the char casting is not working, Please suggest how can I add these two columns. I am trying to get something like below

Select a.INVDATE, b.INV_DAYS, (a.INVDATE + b.INV_DAYS) as DUE_DATE 
from INVOICE_ET a 
join INVOICE_DY b 
On a.INVNUM = b.INVNUM //(edited this portion, because join is based on Invoice numbers)
Result should be 
INVDATE       INV_DAYS       DUE_DATE 
2021-07-31    88             2021-10-27 

Any suggestions/ thoughts are welcome.

0

2 Answers 2

1

Big thanks to Akina and ypercubeᵀᴹ for sorting this one out.

I have modified the query in below fashion and it worked.

SELECT INVOICE_ET.INVDATE,
       INVOICE_DY.INV_DAYS,
      (CAST(INVOICE_ET.INVDATE AS date) + INVOICE_DY.INV_DAYS DAYS) AS DUE_DATE 
FROM INVOICE_ET
CROSS JOIN INVOICE_DY;

See this fiddle for more details.

-2
SELECT INVOICE_ET.INVDATE,
       INVOICE_DY.INV_DAYS,
       ADD_DAYS(DATE(INVOICE_ET.INVDATE), INVOICE_DY.INV_DAYS) AS DUE_DATE 
FROM INVOICE_ET
CROSS JOIN INVOICE_DY;

https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=de4bc1d961771ab8826a27eda0855df7

4
  • Hi @Akina , thank you for the query, is ADD_DAYS a SQL function ? In my DB2 output its unable to understand that. See below snapshot of error- SQL State: 42704 Vendor Code: -204 Message: [SQL0204] ADD_DAYS in *LIBL type *N not found. Cause . . . . . : ADD_DAYS in *LIBL type *N was not found.
    – mrsorrted
    Commented Oct 12, 2021 at 11:41
  • @mrsorrted ibm.com/docs/en/db2/11.1?topic=functions-add-days
    – Akina
    Commented Oct 12, 2021 at 11:45
  • 2
    Db2 for IBM i syntax would be DATE(INVOICE_ET.INVDATE) + INVOICE_DY.INV_DAYS DAYS Datetime operands
    – Charles
    Commented Oct 12, 2021 at 13:32
  • Yeah, the problem with DB2 is that there are at least 5 different flavours, all with the same name (DB2) which differ in details and SQL syntax. Commented Oct 13, 2021 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.