This worked perfectly! Thanks!!!SELECT to_char(SYSDATE, 'MM/DD')
thx! I’ll try that...
thx, I’ll try those. To clarify, these two dates, 3/9 and 3/30, are sorting as 3/30, 3/9 instead of 3/9, 3/30 because it’s treating these as alphanumeric field type instead of Date.Here’s the DataSource that shows it incorrectly sorted:
thx!
great, thx!
ok, thx for the info- good to know!
thx.fyi, we added the ADDRESS_ID column and it ran successfully w/o errors, and said # rows modified, but when we checked the CustomerInfo screen they did not update.
thx- that looks very polished!
Thx! That worked!Any reason why the time didn’t get calculated/populated for Calc_Wanted_Dated?
That worked! Thx very much!
fyi, I was able to design a solution.I created an IAL hard-coding the values I wanted, and then used the above solution to plug it into my Lobby. Thx for the effort on this one!select 'Division A' as DIVISIONfrom DUALUNION ALLselect 'Division B' as DIVISIONfrom DUALUNION ALLselect 'Division C' as DIVISIONfrom DUAL
The base query will run quicker if you make LEVEL_1_FORECAST the first table in your query and then join your way down the rest of the tables because your WHERE clause is referencing this table. I know that oracle can be smart enough to optimise queries but better to give it a fighting change Apply the same approach to the sub queries i.e. Filter the data from the primary table first if possible then join other tables to it. So in your sub queries SELECT blah from INVOSTAT INNER JOIN CUSTOMER_INFO where <INVOSTAT field filters> INNER JOINS vs LEFT JOINS - INNER is ‘better’ if you know the records will always link and may be slightly faster depending on the structure of your query. Re-writing the query as suggested above took care of the performance issue, along with Durette’s reply:and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')Thx, everyone for your input! Many good ideas!
Thx, Durette; that fills out my understanding.
Great solution!Is it possible to build the Selections from a hard-coded list instead of an IFS table?For instance we have a lobby with the customer District_Code as a selection. We’ve set up customers with groups of DistrictCodes for a company division, i.e., District Codes of 651, 652, 653, 654, 655, and 656 all belong to Division A. So the users want the selection to be:Division > Division A Division B Division C …...which in turn filter by their list of corresponding District Codes.
If I use the _TAB table name in my SQL that it finds it but now it doesn’t have sufficient priviledges:
Tried to grant access to the INDEX:
When I try to query the new indexes I do see them:
When I try to query off of that new index I get this error:I also tried the prefix ifsinfo..
If you’re going to extract the YEAR and MONTH from a field that’s of DATE type, there’s no need to wrap the DATE field in TO_DATE first. If you need BOTH the year and month, you might be better off truncating to the month to include both the year and month in the clause at the same time. and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM') After you change the query like that, try these function-based indexes (in a nonproduction environment first): CREATE INDEX c_cust_ord_inv_stat_invdate_ix ON cust_ord_invo_stat_tab (TRUNC(invoice_date, 'MM')) TABLESPACE ifsapp_index;CREATE INDEX c_level_1_fcast_ms_date_ix ON level_1_forecast_tab (TRUNC(ms_date, 'MM')) TABLESPACE ifsapp_index; I created the indexes; now will try a query using them...
If you’re going to extract the YEAR and MONTH from a field that’s of DATE type, there’s no need to wrap the DATE field in TO_DATE first. If you need BOTH the year and month, you might be better off truncating to the month to include both the year and month in the clause at the same time. and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM') After you change the query like that, try these function-based indexes (in a nonproduction environment first): CREATE INDEX c_cust_ord_inv_stat_invdate_ix ON cust_ord_invo_stat_tab (TRUNC(invoice_date, 'MM')) TABLESPACE ifsapp_index;CREATE INDEX c_level_1_fcast_ms_date_ix ON level_1_forecast_tab (TRUNC(ms_date, 'MM')) TABLESPACE ifsapp_index; Hmm, I must be missing something; I have two questions on this:1.) How does TRUNC(date,’MM’) include both year and month? Wouldn’t it just return ‘01’, ‘02’, … ‘11’, ‘12’? Why wouldn’t it be TRUNC(date,’YYYYMM’)?2.) I put TRUNC(Date,’MM’) in a SQL and it returned the entire date, including day and
ok, thx, I’ll try that.
Thx. I removed the to_date().We don’t have PL/SQL access to do CREATE INDEX statements. We do have Oracle SQL Developer.
I always recommend putting parentheses around clauses when you mix AND and OR, but that piece of this query is right. OR has a higher precedence than AND. If you ever forget, you can ask the database by running an experiment. SELECT * FROM (SELECT 'A' AS col1, 'Z' AS col2 FROM DUAL UNION ALLSELECT 'A' AS col1, 'Y' AS col2 FROM DUAL UNION ALLSELECT 'B' AS col1, 'X' AS col2 FROM DUAL UNION ALLSELECT 'B' AS col1, 'W' AS col2 FROM DUAL)WHERE col1 = 'A' AND col2 = 'Z' OR col1 != 'A';/*Returns:A ZB XB W*/ good idea- thx!
fyi, I tried replacing the two NVL() statements with an ILA, but still get the performance issue:IAL:selectcol.contract as Contract,col.part_no as Part_No,col.customer_no as Customer_No,to_char(col.invoice_date,'yyyymm') as Year_Month,ROUND( SUM( NULLIF(invoiced_qty * sale_unit_price,0) ), 2) AS Order_Total,ROUND( SUM( NULLIF(invoiced_qty ,0)), 2) AS Order_Qtyfrom rega1app.cust_ord_invo_stat colleft join rega1app.customer_info_cfv cic on cic.customer_id = col.customer_nowhere invoice_date >= to_date('1/1/2018', 'MM/DD/YYYY') and invoiced_qty > 0 and invoiced_qty IS NOT NULLgroup by Contract, Part_No, Customer_No, to_char(col.invoice_date,'yyyymm')order by 1, 2, 3, 4, 5
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.
