Many thanks MMcK, it works as expected. Much appreciated!!! RegardsHousseiny
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 ChristianJ
Hi gumabs, Thanks again for your help. Much appreciated!!
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, 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 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
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 @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
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
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?
Already have an account? Login
No account yet? Create an account
Enter your username or 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.
