Skip to main content
Solved

Understanding SQL Date Formatting for QuickReports, IALs, and SQL Query Tool

  • August 13, 2020
  • 2 replies
  • 1068 views

Forum|alt.badge.img+10
  • Sidekick (Customer)
  • 119 replies

We’re getting run-time errors due to date logic/formatting/selections. Is there any standardized set of date formatting rules?

I have numerous Oracle SQL websites detailing date functions, but we run into issues every now and then, with each case having slightly different requirements/logic.

Scenarios:

1.) SQL in an IAL runs fine in the quick report using it, but fails when run in the SQL Query Tool.

2.) A quick report runs fine (and also runs fine with RMB Output>Save As...) but if you take Export to Excel it fails.

For instance, our latest situation is this error when running an IAL’s logic from the SQL Query Tool that doesn’t occur when running a quick report over the same IAL:

 

IAL logic:

1select
2soi.sub_program_id as PGM,
3cois.district_code as Class,
4omi.commission_receiver as Receiver,
5max(cin2.name) as Receiver_Name,
6max(cia.name) as Receiver_Company,
7omi.customer_id as Dealer,
8max(cin.name) as Dealer_Name,
9max(cois.country_code) || ' - ' || max(cia.state) as CN_ST,
10
11sum(CASE WHEN trunc(cois.invoice_date) between to_date( '#START_OF_LAST_MONTH#', 'YYYY-MM-DD-HH24:MI:SS' ) and to_date( '#END_OF_LAST_MONTH#', 'YYYY-MM-DD-HH24:MI:SS' )
12then cois.gross_amount else 0 end) as Month_Sales,
13
14sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYYMM')) = to_char( ADD_MONTHS( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), -12), 'YYYYMM' )
15then cois.gross_amount else 0 end) as Prior_Year_Month_Sales,
16
17sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYY')) = to_char( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), 'YYYY' )
18then cois.gross_amount else 0 end) as Current_YTD_Sales,
19
20sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYY')) = to_char( ADD_MONTHS( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), -12), 'YYYY' )
21then cois.gross_amount else 0 end) as Prior_YTD_Sales,
22
23min(cicm.value) as Phone_Number
24
25from REGA1APP.c_override_master_line omi
26
27left join rega1app.C_SUPPLIER_OVERRIDE_INFO soi on omi.supplier_id = soi.supplier_id and omi.sub_program_id = soi.sub_program_id
28
29left join rega1app.CUST_ORD_INVO_STAT cois on omi.customer_id = cois.customer_no
30
31left join rega1app.Sales_Part sp on cois.catalog_no = sp.catalog_no and cois.contract = sp.contract
32
33left join rega1app.customer_info_address cia on soi.supplier_id = cia.customer_id and omi.supplier_id = cia.address_id
34
35left join rega1app.customer_info cin2 on omi.supplier_id = cin2.customer_id
36
37left join rega1app.customer_info cin on omi.customer_id = cin.customer_id
38
39left join rega1app.customer_info_comm_method cicm on omi.customer_id = cicm.customer_id and cicm.method_id_db = 'PHONE' and cicm.method_default = 'TRUE'
40
41where cois.gross_amount <> 0
42and soi.sub_program_id in ('AVP', 'AVP2', 'AM', 'DIV')
43and omi.commission_receiver = '114043' and omi.customer_id = '114043'
44
45group by soi.sub_program_id, cois.district_code, omi.commission_receiver, omi.customer_id
46order by soi.sub_program_id, cois.district_code, omi.commission_receiver, omi.customer_id

 

Best answer by Rusiru Dharmadasa

Are you running this query inside SQL query tool with substitutes (i.e. #END_OF_LAST_MONTH#)? If that is the case, that should be the failure. You have to specify correct values for them. The SQL query tool will not support them. 

 

When it comes to SQL quick report’s Export to excel there are some limitations. 

 

  1. SQL expressions which does not start with "SELECT" clause (Eg: - "WITH") are not supported due to a limitation in the PL/SQL access provider. Output channels could be used in such cases to export data to excel file.
  2. SQL expressions can not have bind arguments in the GROUP BY expression.
  3. SQL expressions can not have non English characters such as Å,Ä,Ö as parameter names. However parameter values can contain such characters.

See if your query has one of these when it fails to export to excel from quick reports.

 

View original
Did this topic help you find an answer to your question?

2 replies

Rusiru Dharmadasa
Superhero (Employee)
Forum|alt.badge.img+20

Are you running this query inside SQL query tool with substitutes (i.e. #END_OF_LAST_MONTH#)? If that is the case, that should be the failure. You have to specify correct values for them. The SQL query tool will not support them. 

 

When it comes to SQL quick report’s Export to excel there are some limitations. 

 

  1. SQL expressions which does not start with "SELECT" clause (Eg: - "WITH") are not supported due to a limitation in the PL/SQL access provider. Output channels could be used in such cases to export data to excel file.
  2. SQL expressions can not have bind arguments in the GROUP BY expression.
  3. SQL expressions can not have non English characters such as Å,Ä,Ö as parameter names. However parameter values can contain such characters.

See if your query has one of these when it fails to export to excel from quick reports.

 


Forum|alt.badge.img+10
  • Author
  • Sidekick (Customer)
  • 119 replies
  • August 13, 2020

thx!

this works:

1trunc(cois.invoice_date, 'mm') = add_months(trunc(sysdate,'mm'), -1)

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings