Procedure To Check FOR ITEM LEVEL FROM THE ITEMS TABLE

Below I have tried to explain Creation of Items_table and Procedure to Find Items less in Quantity from the Items Table; 

SQL> CREATE TABLE ITEMS_TABLE(

  2  ITEM_CODE VARCHAR2(12),

  3  MINLEVL NUMBER(5),

  4  QTYNOW NUMBER(5),

  5  MAXLEVL NUMBER(5),

  6  RATE NUMBER(9,2)

  7  );

 TABLE CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES ('ITEM_01',100,80,175,120.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_02',125,100,250,200.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_03',100,200,325,200.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_05',100,200,325,200.00);

1 ROW CREATED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100         80        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200

SQL>  CREATE OR REPLACE PROCEDURE ITEMS_PROC(ITEM VARCHAR2)

  2   IS

  3   QTY_NOW NUMBER;

  4   MIN_LEVEL NUMBER;

  5   MAX_LEVEL NUMBER;

  6   BEGIN

  7   SELECT

  8   QTYNOW,

  9   MINLEVL,

 10   MAXLEVL INTO QTY_NOW,MIN_LEVEL,MAX_LEVEL FROM

 11   ITEMS_TABLE WHERE ITEM_CODE=ITEM;

 12   IF QTY_NOW < MIN_LEVEL THEN

 13    UPDATE ITEMS_TABLE SET QTYNOW=MINLEVL+QTYNOW WHERE ITEM_CODE=ITEM;

 14   DBMS_OUTPUT.PUT_LINE('QTY UPDATED');

 15     ELSE

 16     DBMS_OUTPUT.PUT_LINE('ITEM PRESENT');

 17     END IF;

 18     EXCEPTION

 19     WHEN NO_DATA_FOUND THEN

 20     DBMS_OUTPUT.PUT_LINE('NO DATA RETURNED');

 21    WHEN TOO_MANY_ROWS THEN

 22     DBMS_OUTPUT.PUT_LINE('MANY ITEMS WITH SAME ITEM CODE');

 23    WHEN OTHERS THEN

 24     DBMS_OUTPUT.PUT_LINE('SOME OTHER ERROR');

 25     END;

 26   /

PROCEDURE CREATED.

SQL> EXEC ITEMS_PROC('ITEM_01');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100        180        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200

SQL> EXEC ITEMS_PROC('ITEM_03');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100        180        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200

How To Create A Flat File And Import As Source Table From The File

• Creating a Flat File is Easy. Just open a notepad(or csv) and you can enter column headers and the values just below that in comma delimited format or any other required format.

• Go to Source Analyzer select the option“Import from File”

• Select the required Flat file and Import it. When you import you will get the below screen

• You can check the option “Import Field names From First Line” to Take the first column as headers.

Give next and Give the delimiter

• Once the import is finished you can see the flat file source definition in the Source Analyzer with first row as Header

SQL Functions: MONTHS_BETWEEN

MONTHS_BETWEEN

Returns the number of months between two date values. The return value can be negative or positive depending on

Select MONTHS_BETWEEN (end_date,start_date) from emp;

SQL Functions:LEAST

LEAST:

This function used to get the least value from a list of one or more expressions

Select LEAST (‘XXXY’,’XXXX’) FROM DUAL;

Output: XXXX

SQL Functions: LAST_DAY

LAST_DAY:

Takes date as input and returns the last day of month corresponding to that date. Return type is always DATE.

Select LAST_DAY (LAST_ACCESS_DATE) FROM EMP;

SQL Functions: GREATEST

GREATEST:

This function used to get the greatest value from a list of one or more expressions

Select GREATEST (‘XXXY’,’XXXX’) FROM DUAL;

Output: XXXY

SQL Functions:CONCAT

CONCAT:This function is same as concatenation operator (||).Used to concantenate two characters, which can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Select CONCAT(char01,char02) from table Name;

Select CONCAT(First_name,Last_name) “Full Name” from EMP;

SQL Functions: CARDINALITY

CARDINALITY:

Returns the number of elements in a nested table column. Returns NULL if the table is empty.

Select emp, CARDINALITY (COL) FROM emp;

This will give the number of elements in the nested table column COL

SQL Functions: ADD_MONTHS

ADD_MONTHS
This function is used to add integer months to existing date. The return data type is always date function.

SELECT ADD_MONTHS (END_DATE, 1), emp  FROM EMP;

Output: Returns one month after the END_DATE for each item

SQL Functions:ABS

ABS: Returns absolute value for argument. The data type of the output remains the same.

SELECT ABS (input_value)  FROM  Table;

This query will return the absolute value of input_value

SELECT ABS(-250 ) FROM  Table;

Output: 250

Very Large Database or VLDB

What is a VLDB?

It means very large database consisting of large information (TB) managed by relational database (RDBMS). Such database contains high storage space and physical file systems.

The size of around Terabytes will suit VLDB. These are mostly used in DSS(decision support system).Administration of VLDB requires more attention than smaller database.

For example Oracle Database 11g Release 2 supports VLDB

Different Ways of Loading Dimension Tables

Before talking about ways of loading dimension tables let us check how the data in dimension tables are. Loading depends on whether dimension stores historical data or not. If it’s not a changing dimension then simply insert. If it’s a changing dimension then we have,

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table. Thereby, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.

There are basically two types of Load

Direct Load:

Before loading the data all the constraints will be disabled. After constraints are disabled data is loaded. Once loading is done the bad data is taken out and skipped from further process. This is faster method of loading

Conventional Load:

The data is loaded keeping data integrity (constraints).Here we don’t have to remove the constraints. This is slow method of loading.