We have the due date and payment date in a query builder for some invoices. Is there a way to add a calculated field to show the number of days difference between the due date and payment date so we can present it on a graph in a lobby?
Try this select * from ifsinfo.icfr_pro_116b X join IFSAPP.purchase_order Y on X.order_no = Y.order_no where x.project_id = '20012' But i do belive you need to go to the old standard for Join tables then you need to do like this select * from ifsinfo.icfr_pro_116b X, IFSAPP.purchase_order Y where X.order_no = Y.order_no and x.project_id = '20012' Both should give the same result First proposed code worked like a charm. Many thanks for you rhelp
Hi @Housseiny, You can try several options. First Order by using the ‘Net Amount in USD’ value. Then try as below. ex: # Add below to the beginning of the select statement.SELECT TOP (20) PERCENT Or # Add below where condition to the SQL Query WHERE ROWNUM <= 20 Or #Add below condition to end of the Select Statement FETCH FIRST 20 ROWS ONLY. Best Regards -Kelum Many thanks Kelum for your prompt feedback
Like this, had to remove &AO when I tested but I guess you get the idea: SELECT i.identity "Supplier ID", s.vendor_name "Supplier Name", to_char(sum(i.actual_net_dom_amount), '$999,999,999') as "Net Amount in USD" FROM invoice i INNER JOIN supplier s ON i.identity = s.vendor_no WHERE i.objstate <> 'Cancelled' AND i.party_type_db = 'SUPPLIER' GROUP BY i.identity,s.vendor_name ORDER BY sum(i.actual_net_dom_amount) DESC FETCH FIRST 20 ROWS ONLY Many thanks Tomas, worked like a charm
Hi Replace &AO. with IFSAPP and use parameter from PROJECT_FORECAST_ITEM_COST view. See a simple example below SELECT x.*, IFSAPP.SUB_PROJECT_API.Get_Description(x.PROJECT_ID, x.SUB_PROJECT_ID) DESCRIPTION FROM PROJECT_FORECAST_ITEM_COST x Hope this helps Dear Gumabs, This worked like a charm. Really appreciate your prompt feedback and help. RegardsHousseiny
Hi Gumabs, Would it be possible to give a couple of examples for other functions from other APIs you are referring to please? I am not familiar with APIs or how it works :-) RegardsHousseiny
Hi @Housseiny For example you can add project customer and name by adding 2 more functions. If you need any information from any window then you can add it by a function into your report. As long as parameters are entered correctly. SELECT x.*, IFSAPP.SUB_PROJECT_API.Get_Description(x.PROJECT_ID, x.SUB_PROJECT_ID) DESCRIPTION, IFSAPP.PROJECT_API.Get_Customer_Id(x.PROJECT_ID) CUSTOMER_ID, IFSAPP.CUSTOMER_INFO_API.Get_Name(IFSAPP.PROJECT_API.Get_Customer_Id(x.PROJECT_ID)NAMEFROM PROJECT_FORECAST_ITEM_COST x You must try different reports from different views so you will practice and understand the basics. Hope this helps Hi Gumabs, This is exactly what i needed. Thanks a million!!
Hi gumabs, Thanks again for your help. Much appreciated!!
Many thanks ChristianJ
Thanks MMcK, actually i am looking for the below result where i will have all data from table X and all Data from table Y as per below. I also expect the below blanks as null. your help is much appreciated regardsHousseiny
Many thanks MMcK, it works as expected. Much appreciated!!! RegardsHousseiny
Already have an account? Login
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.