Toad World® Forums

Re: [Code Tester for Oracle - Discussion Forum] How to declare local and global variables and execute a script with toad


#1

Run the script with (F5)

On Tue, May 23, 2017 at 3:10 PM, haifaG bounce-haifaG@toadworld.com wrote:

How to declare local and global variables and execute a script with toad

Thread created by haifaG
Good evening everybody, I’m a new toad’s user. Actually I’m doing a BI solution for commercial activity of a company.

I already construct my datawarehouse with 3dimensions (customers, products and structure) now all I have to do is to add a dimension for date to make my events organised and to add a calendar in my qlikview sheets. I started with creating a table DIMTIME :

CREATE TABLE DimDate

(DateKey NUMBER(10) primary key, Datt date, FullDateTN CHAR(10), DayOfMonth VARCHAR2(2), DaySuffix VARCHAR2(4), DayName VARCHAR2(9), DayOfWeekTN CHAR(1), DayOfWeekInMonth VARCHAR2(3), DayOfWeekInYear VARCHAR2(2), DayOfQuarter VARCHAR2(3), DayOfYear VARCHAR2(3), WeekOfMonth VARCHAR2(1), WeekOfQuarter VARCHAR2(2), WeekOfYear VARCHAR2(2), Month VARCHAR2(2), MonthName VARCHAR2(9), MonthOfQuarter VARCHAR2(2), Quarter CHAR(1), QuarterName VARCHAR2(9), Year CHAR(4), YearName CHAR(7), MonthYear CHAR(10), MMYYYY CHAR(6), FirstDayOfMonth DATE, LastDayOfMonth DATE,

FirstDayOfQuarter DATE,

LastDayOfQuarter DATE,

FirstDayOfYear DATE,

LastDayOfYear DATE,

IsHolidayTN NUMBER(1),

IsWeekday NUMBER(1),

HolidayTN VARCHAR2(50));

Now I have to declare a new local variable and table to generate my table

My boss said that he wants the years in the calendar generated automatically, I found this script in t-sql (to populate my time dimension) I already converted it to plsql but when I execute it on toad it didn’t stop showing errors messages:

–Specify Start Date and End date here

–Value of Start Date Must be Less than Your End Date

v_StartDate TIMESTAMP(3) := ‘01/01/2013’ --Starting value of Date Range

v_EndDate TIMESTAMP(3) := ‘01/01/2015’ --End Value of Date Range

–Temporary Variables To Hold the Values During Processing of Each Date of Year

v_DayOfWeekInMonth NUMBER(10);

v_DayOfWeekInYear NUMBER(10);

v_DayOfQuarter NUMBER(10);

v_WeekOfMonth NUMBER(10);

v_CurrentYear NUMBER(10);

v_CurrentMonth NUMBER(10);

v_CurrentQuarter NUMBER(10)

/Table Data type to store the day of week count for the month and year/

DECLARE @DayOfWeek TABLE (DOW NUMBER(10), MonthCount NUMBER(10), QuarterCount NUMBER(10), YearCount NUMBER(10))

INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)

–Extract and assign various parts of Values from Current Date to Variable

v_CurrentDate TIMESTAMP(3) := v_StartDate

v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))

v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))

v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))

/********************************************************************************************/

–Proceed only if Start Date(Current date ) is less than End date you specified above

WHILE v_CurrentDate < v_EndDate

LOOP

/Begin day of week logic/

/*Check for Change in Month of the Current date if Month changed then

Change variable value*/

IF v_CurrentMonth != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))

THEN

UPDATE @DayOfWeek

SET MonthCount = 0

v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’));

END IF;

/* Check for Change in Quarter of the Current date if Quarter changed then change

Variable value*/

IF v_CurrentQuarter != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))

THEN

UPDATE @DayOfWeek

SET QuarterCount = 0

v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’));

END IF;

/* Check for Change in Year of the Current date if Year changed then change

Variable value*/

IF v_CurrentYear != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))

THEN

UPDATE @DayOfWeek

SET YearCount = 0

v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’));

END IF;

– Set values in table data type created above from variables

UPDATE @DayOfWeek

SET

MonthCount = MonthCount + 1,

QuarterCount = QuarterCount + 1,

YearCount = YearCount + 1

WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))

SELECT

MonthCount,

QuarterCount,

YearCount INTO v_DayOfWeekInMonth, v_DayOfQuarter, v_DayOfWeekInYear

FROM @DayOfWeek

WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’));

/End day of week logic/

/* Populate Your Dimension Table with values*/

INSERT INTO DimDate

SELECT

TO_CHAR (v_CurrentDate,112) as DateKey,

v_CurrentDate AS Date,

TO_CHAR (v_CurrentDate,103) as FullDateUK,

TO_CHAR (v_CurrentDate,101) as FullDateUSA,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)) AS DayOfMonth,

–Apply Suffix values like 1st, 2nd 3rd etc…

CASE

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)) IN (11,12,13) _

THEN FROM dual TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘th’

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 1 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘st’

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 2 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘nd’

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 3 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘rd’

ELSE TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘th’

END LOOP; AS DaySuffix,

TO_CHAR(v_CurrentDate, ‘DW’) AS DayName,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’)) AS DayOfWeekUSA,

– check for day of week as Per US and change it as per UK format

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))

WHEN 1 THEN 7

WHEN 2 THEN 1

WHEN 3 THEN 2

WHEN 4 THEN 3

WHEN 5 THEN 4

WHEN 6 THEN 5

WHEN 7 THEN 6

END

AS DayOfWeekUK,

v_DayOfWeekInMonth AS DayOfWeekInMonth,

v_DayOfWeekInYear AS DayOfWeekInYear,

v_DayOfQuarter AS DayOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DY’)) AS DayOfYear,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘WW’)) + 1 - TO_NUMBER(TO_CHAR(TO_CHAR, ‘WW’ (_

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))) + ‘/1/’ || TO_CHAR (_

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’)))) AS WeekOfMonth,

(_ - DATEDIFF * INTERVAL ‘1’ QQ(QQ, 0, v_CurrentDate) + 0 _

v_CurrentDate) / 7) + 1 AS WeekOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘WW’)) AS WeekOfYear,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) AS Month,

TO_CHAR(v_CurrentDate, ‘MM’) AS MonthName,

CASE

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (1, 4, 7, 10) THEN 1

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (2, 5, 8, 11) THEN 2

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (3, 6, 9, 12) THEN 3

END AS MonthOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’)) AS Quarter,

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))

WHEN 1 THEN ‘First’

WHEN 2 THEN ‘Second’

WHEN 3 THEN ‘Third’

WHEN 4 THEN ‘Fourth’

END AS QuarterName,

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YEAR’)) AS Year,

'CY ’ || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YEAR’))) AS YearName,

SUBSTR(TO_CHAR(v_CurrentDate, ‘MM’), 1, 3) + ‘-’ || TO_CHAR (_

TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))) AS MonthYear,

SUBSTR(‘0’ || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))), GREATEST(-LENGTH(‘0’ || TO_CHAR(VARCHAR, (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’, v_CurrentDate)))),-2)) + _

TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))) AS MMYYYY,

CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (TO_NUMBER(TO_CHAR(_, ‘DD’

v_CurrentDate) - 1), v_CurrentDate))) AS FirstDayOfMonth,

CONVERT(DATETIME, CONVERT(DATE, - * INTERVAL ‘1’ DD(5)(TO_NUMBER(TO_CHAR(_, ‘DD’

(INTERVAL ‘1’ MM + v_CurrentDate)))) + INTERVAL ‘1’ MM + _

v_CurrentDate)))) AS LastDayOfMonth,

DATEDIFF * INTERVAL ‘1’ QQ(QQ, 0, v_CurrentDate) + 0 AS FirstDayOfQuarter,

DATEDIFF * INTERVAL ‘1’ QQ(QQ, -1, v_CurrentDate) + -1 AS LastDayOfQuarter,

CONVERT(DATETIME, ‘01/01/’ || TO_CHAR (TO_NUMBER(TO_CHAR(_, ‘YY’

v_CurrentDate))) AS FirstDayOfYear,

CONVERT(DATETIME, ‘12/31/’ || TO_CHAR (TO_NUMBER(TO_CHAR(_, ‘YY’

v_CurrentDate))) AS LastDayOfYear,

NULL AS IsHolidayUSA,

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))

WHEN 1 THEN 0

WHEN 2 THEN 1

WHEN 3 THEN 1

WHEN 4 THEN 1

WHEN 5 THEN 1

WHEN 6 THEN 1

WHEN 7 THEN 0

END AS IsWeekday,

NULL AS HolidayUSA, Null, Null

v_CurrentDate := INTERVAL ‘1’ DD(5) + v_CurrentDate;

END

/********************************************************************************************/

Step 3.

Update Values of Holiday as per UK Government Declaration for National Holiday.

/Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday/

– Good Friday April 18

UPDATE DimDate

SET HolidayUK = ‘Good Friday’

WHERE Month = 4 AND DayOfMonth = 18

– Easter Monday April 21

UPDATE DimDate

SET HolidayUK = ‘Easter Monday’

WHERE Month = 4 AND DayOfMonth = 21

– Early May Bank Holiday May 5

UPDATE DimDate

SET HolidayUK = ‘Early May Bank Holiday’

WHERE Month = 5 AND DayOfMonth = 5

– Spring Bank Holiday May 26

UPDATE DimDate

SET HolidayUK = ‘Spring Bank Holiday’

WHERE Month = 5 AND DayOfMonth = 26

– Summer Bank Holiday August 25

UPDATE DimDate

SET HolidayUK = ‘Summer Bank Holiday’

WHERE Month = 8 AND DayOfMonth = 25

– Boxing Day December 26

UPDATE DimDate

SET HolidayUK = ‘Boxing Day’

WHERE Month = 12 AND DayOfMonth = 26

–CHRISTMAS

UPDATE DimDate

SET HolidayUK = ‘Christmas Day’

WHERE Month = 12 AND DayOfMonth = 25

–New Years Day

UPDATE DimDate

SET HolidayUK = ‘New Year’‘s Day’

WHERE Month = 1 AND DayOfMonth = 1

–Update flag for UK Holidays 1= Holiday, 0=No Holiday

UPDATE DimDate

SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _

THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END

Step 4.

Update Values of Holiday as per USA Govt. Declaration for National Holiday.

/Update HOLIDAY Field of USA In dimension/

/THANKSGIVING - Fourth THURSDAY in November/

UPDATE DimDate

SET HolidayUSA = ‘Thanksgiving Day’

WHERE

Month = 11

AND DayOfWeekUSA = ‘Thursday’

AND DayOfWeekInMonth = 4

/CHRISTMAS/

UPDATE DimDate

SET HolidayUSA = ‘Christmas Day’

WHERE Month = 12 AND DayOfMonth = 25

/4th of July/

UPDATE DimDate

SET HolidayUSA = ‘Independance Day’

WHERE Month = 7 AND DayOfMonth = 4

/New Years Day/

UPDATE DimDate

SET HolidayUSA = ‘New Year’‘s Day’

WHERE Month = 1 AND DayOfMonth = 1

/Memorial Day - Last Monday in May/

UPDATE DimDate

SET HolidayUSA = ‘Memorial Day’

FROM dbo.DimDate

WHERE DateKey IN

(

SELECT

MAX(DateKey)

FROM dbo.DimDate

WHERE

MonthName = ‘May’

AND DayOfWeekUSA = ‘Monday’

GROUP BY

Year,

Month

)

/Labor Day - First Monday in September/

UPDATE DimDate

SET HolidayUSA = ‘Labor Day’

FROM dbo.DimDate

WHERE DateKey IN

(

SELECT

MIN(DateKey)

FROM dbo.DimDate

WHERE

MonthName = ‘September’

AND DayOfWeekUSA = ‘Monday’

GROUP BY

Year,

Month

)

/Valentine’s Day/

UPDATE DimDate

SET HolidayUSA = ‘Valentine’‘s Day’

WHERE

Month = 2

AND DayOfMonth = 14

/Saint Patrick’s Day/

UPDATE DimDate

SET HolidayUSA = ‘Saint Patrick’‘s Day’

WHERE

Month = 3

AND DayOfMonth = 17

/Martin Luthor King Day - Third Monday in January starting in 1983/

UPDATE DimDate

SET HolidayUSA = ‘Martin Luthor King Jr Day’

WHERE

Month = 1

AND DayOfWeekUSA = ‘Monday’

AND Year >= 1983

AND DayOfWeekInMonth = 3

/President’s Day - Third Monday in February/

UPDATE DimDate

SET HolidayUSA = ‘President’‘s Day’

WHERE

Month = 2

AND DayOfWeekUSA = ‘Monday’

AND DayOfWeekInMonth = 3

/Mother’s Day - Second Sunday of May/

UPDATE DimDate

SET HolidayUSA = ‘Mother’‘s Day’

WHERE

Month = 5

AND DayOfWeekUSA = ‘Sunday’

AND DayOfWeekInMonth = 2

/Father’s Day - Third Sunday of June/

UPDATE DimDate

SET HolidayUSA = ‘Father’‘s Day’

WHERE

Month = 6

AND DayOfWeekUSA = ‘Sunday’

AND DayOfWeekInMonth = 3

/Halloween 10/31/

UPDATE DimDate

SET HolidayUSA = ‘Halloween’

WHERE

Month = 10

AND DayOfMonth = 31

/Election Day - The first Tuesday after the first Monday in November/

BEGIN

DECLARE @Holidays TABLE (ID NUMBER(10) , _

DateID int, Week TINYINT, YEAR CHR(4), DAY CHR(2))

INSERT INTO @Holidays(DateID, Year,Day)

SELECT

DateKey,

Year,

DayOfMonth

FROM DimDate

WHERE

Month = 11

AND DayOfWeekUSA = ‘Monday’

ORDER BY

YEAR,

DayOfMonth

v_CNTR NUMBER(10); v_POS NUMBER(10); v_STARTYEAR NUMBER(10); v_ENDYEAR NUMBER(10); v_MINDAY NUMBER(10);

SELECT

MIN(Year)

, MIN(Year)

, MAX(Year) INTO v_CurrentYear, v_STARTYEAR, v_ENDYEAR

FROM @Holidays

WHILE; v_CurrentYear <= v_ENDYEAR

BEGIN

SELECT COUNT([Year]) INTO v_CNTR

FROM @Holidays

WHERE Year = v_CurrentYear;

v_POS := 1;

WHILE v_POS <= v_CNTR

LOOP

SELECT MIN(DAY) INTO v_MINDAY

FROM @Holidays

WHERE

Year = v_CurrentYear

AND Week IS NULL;

UPDATE @Holidays

SET Week = v_POS

WHERE

Year = v_CurrentYear

AND Day = v_MINDAY

v_POS := v_POS + 1 FROM dual;

END LOOP;

v_CurrentYear := v_CurrentYear + 1 FROM dual;

END

UPDATE DimDate

SET HolidayUSA = ‘Election Day’

FROM dbo.DimDate DT

JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey

WHERE

Week = 1

END

–set flag for USA holidays in Dimension

UPDATE DimDate

SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END

/*****************************************************************************************

Does anyone know how to :

  1. create and run a script file on toad
  1. The syntax of creating a new local and global variables and tables

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Code Tester for Oracle Forum notifications altogether.

Code Tester for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.