SQL Tips
Wednesday, September 4, 2019
Friday, July 15, 2016
Escaping ampersand. Getting rid of "Enter Substitution Variable" message.
If you want to bypass "Enter Substitution Variable" message when there is '&' or '&&' sign on your oracle SQL then all you need to do is add following line at the top of your SQL.
Exmaple ;
This SQL has ampersand and it will prompt to enter a substitution variable.
select 'dfd&dfd' from dual
where DUMMY ='E&LE';
This issue can be fixed by adding following line into you sql.
SET DEFINE OFF
select 'dfd&dfd' from dual
where DUMMY ='E&LE'
Hope this helps.
Exmaple ;
This SQL has ampersand and it will prompt to enter a substitution variable.
select 'dfd&dfd' from dual
where DUMMY ='E&LE';
This issue can be fixed by adding following line into you sql.
SET DEFINE OFF
select 'dfd&dfd' from dual
where DUMMY ='E&LE'
Hope this helps.
Friday, August 16, 2013
GetLovField Error
When you try running DEKi report on Infoview, you might end up
getting “GetLovField” Error message.
This issue can occur when you change prompts from mono to multi or from multi
to mono. In order to get rid of this issue you will need to remove the existing
prompt and re-do it. This should fix your issue.
Wednesday, June 6, 2012
Missing Msvcr100.dll
When you first try to launch SQL developer on your PC you
might run into “Msvcr100.dll is missing” error message. When
you encounter above error massage, you will not be able to launch SQL
developer. However; there is a very simple
step to fix this issue.
First thing you need to do is find MSvcr100.dll on your
machine. Normally it is available on “\Program
Files\Java\jsdk1.7.0_04\bin” folder. Simply copy it and paste it to “\sqldeveloper\sqldeveloper\bin”
folder. This should fix the issue.
Friday, May 25, 2012
Date Functions
I always find date functions difficult to code as they can be confusing. Here are some of the date functions that can come handy while working with dates in Business Objects.
Last day of month =LastDayOfMonth(< Month>)
First day of month =DayNumberOfWeek(< Month>)
Days in a month =DaysBetween(< Month> ,<last day of month >)+1
Weekends =(Truncate((DayNumberOfWeek(< Month>)+<Days in a month >)/7 ,0))
Working Days =Floor(<Days in a month>-(<Weekends>))
6 working Days =If <first day of month > = 7 Then (< Working Days >+1) Else < Working Days >
Thursday, December 15, 2011
How to prompt for Quarters (Q1, Q2, Q3, and Q4) in BusinessObjects
How to prompt for Quarters (Q1, Q2, Q3, and Q4) in Business objects using designer?
Simple Method…
You can use @ Variable function to prompt for Quarter. Here is one example.
In that case Syntax should be something as below
Simple Method…
You can use @ Variable function to prompt for Quarter. Here is one example.
TO_CHAR (CALENDAR_DATE,'Q') =@Variable('Which Quarter?')But Coding can be little complicated when users want to select Q1,Q2,Q3,Q4 from the list, instead.
In that case Syntax should be something as below
( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'Now let’s assume requirement is to select year and quarter and retrieve date up to next 36 months... In this example I am going to use @variable as well as @Prompt.
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
TABLE.CALENDAR_DATE BETWEEN (SELECT Distinct MIN(CALENDAR_DATE) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
AND
(SELECT Distinct MIN(LAST_DAY(ADD_MONTHS(CALENDAR_DATE,35))) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
Friday, March 25, 2011
Fun with Xcelsius
I used my Facebook friends profile to create a simple dashboard using Xcelsius BI reporting tool. The purpose of this dashboard is just for fun and i believe friends privacy has not been compromised ! Have fun !
Subscribe to:
Posts (Atom)