Commit Pop-Up in JavaScript

 HttpContext context = HttpContext.Current;
                            context.Response.Write("<script type='text/javascript'>alert('Access Denied.This is not your request'); window.frameElement.commitPopup();</script>");
                            context.Response.Flush();
                            context.Response.End();

Get-Set All the Controls in the Form

DataTable dtCtrl = new DataTable();
            dtCtrl.Columns.Add("ctrlID");
            dtCtrl.Columns.Add("ctrlValue");

            ArrayList controlList = new ArrayList();
            GetControls(Page.Controls, controlList);
            foreach (string str in controlList)
            {
                if (this.Page.FindControl(str) != null)
                {
                    Control ctrl = Page.FindControl(str);
                    if (ctrl.GetType().ToString() == "System.Web.UI.WebControls.TextBox")
                    {
                        TextBox txtBox = ctrl as TextBox;
                        if (txtBox.ReadOnly == true && txtBox.Visible == true)
                        {
                            DataRow dRow = dtCtrl.NewRow();
                            dRow["ctrlID"] = txtBox.UniqueID;
                            dRow["ctrlValue"] = Request[txtBox.UniqueID];
                            dtCtrl.Rows.Add(dRow);
                        }
                    }
                }
            }
            foreach (DataRow drow in dtCtrl.Rows)
            {
                (Page.FindControl(drow["ctrlID"].ToString()) as TextBox).Text = drow["ctrlValue"].ToString();
            }

PostBackTrigger in ASP.Net

<asp:Panel ID="panel_Buttons" runat="server">
                <div class="btnslist">
                <asp:UpdatePanel ID="UpdatePanel_Buttons" runat="server">
                <ContentTemplate>
                    <asp:Button ID="btnSave" runat="server" CssClass="btns" OnClick="btnSave_Click" Text="Save as Draft" Visible="false"/>
                </ContentTemplate>
                <Triggers>
                    <asp:PostBackTrigger ControlID="btnSave" />
                </Triggers>
                </asp:UpdatePanel>
                </div>
            </asp:Panel>

SPSite,SPWeb as Strings

sRootSite = SPContext.Current.Site.Url;
sSiteUrl = SPContext.Current.Web.Url;

Operations On Date Control When they Selected

function CheckDate(sender, args) {

            var selectedDate = new Date();

            selectedDate = sender._selectedDate;

            var todayDate = new Date();

            var one_day = 1000 * 60 * 60 * 24;


            if (selectedDate.getDateOnly() > todayDate.getDateOnly()) {

                sender._selectedDate = todayDate;

                sender._textbox.set_Value(sender._selectedDate.format(sender._format));

                alert("Sorry, Future dates are not allowed");
                sender._textbox.set_Value("");
            }
            else {
                var NoOfDays = Math.ceil((todayDate.getTime() - selectedDate.getTime()) / (one_day));
                if (NoOfDays > 90) {
                    //alert("Sorry, Voucher can not be old than 90 days");
                    //sender._textbox.set_Value("");
                }
            }

        }

Allow Only Numbers in TextBox

<asp:TextBox ID="txtFareAmount_Travel_TEB" runat="server" MaxLength="12" CssClass="inptxt"
                                                            onBlur="CalculateGrandTotal_TEB(this.id)" onKeyup="ValidateDeciPointRevCalc(this.id)" ></asp:TextBox>
                                                            <asp:FilteredTextBoxExtender ID="ftbEx_txtFareAmount_Travel_TEB" runat="server" TargetControlID="txtFareAmount_Travel_TEB"
                                                                            FilterType="Custom, Numbers" ValidChars="."></asp:FilteredTextBoxExtender>
                         

Calculate Amount In Words

function CalculateAmountInWords_ER() {
            var txtAmount_ER = document.getElementById('<%=txtAmount_ER.ClientID%>');
            var txtAmountInWords_ER = document.getElementById('<%=txtAmountInWords_ER.ClientID%>');

            var Round_Amount = Math.round(txtAmount_ER.value * 100) / 100;
            if (trim(txtAmount_ER.value, " ") != "") {
                txtAmount_ER.value = Round_Amount.toFixed(2);
                ConvertToWords(Math.round(txtAmount_ER.value), txtAmount_ER.id, txtAmountInWords_ER.id);
            }
            else {
                txtAmount_ER.value = "";
                txtAmountInWords_ER.value = "";
            }
        }

 function ConvertToWords(s, ClientID_Number, ClientID_words) {
            var num = s;
            num = Math.floor(s);
            var obStr = new String(num);
            numReversed = obStr.split("");
            actnumber = numReversed.reverse();

            if (Number(num) >= 0) {
                //do nothing
            }
          
            else {
                alert('Please enter a valid number!');
                if (ClientID_Number != "") {
                    document.getElementById(ClientID_Number).value = "";
                }
                if (ClientID_words != "") {
                    document.getElementById(ClientID_words).value = "";
                }
                return false;
            }
            if (Number(num) == 0) {
                document.getElementById(ClientID_Number).value = "";
                document.getElementById(ClientID_words).value = "";
                return false;
            }
            if (actnumber.length > 9) {
                alert('the Number is too big to covertes');
                return false;
            }

            var iWords = ["Zero", " One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine"];
            var ePlace = ['Ten', ' Eleven', ' Twelve', ' Thirteen', ' Fourteen', ' Fifteen', ' Sixteen', ' Seventeen', ' Eighteen', ' Nineteen'];
            var tensPlace = ['dummy', ' Ten', ' Twenty', ' Thirty', ' Forty', ' Fifty', ' Sixty', ' Seventy', ' Eighty', ' Ninety'];

            var iWordsLength = numReversed.length;
            var totalWords = "";
            var inWords = new Array();
            var finalWord = "";
            j = 0;
            for (i = 0; i < iWordsLength; i++) {
                switch (i) {
                    case 0:
                        if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                            inWords[j] = '';
                        }
                        else {
                            inWords[j] = iWords[actnumber[i]];
                        }
                        inWords[j] = inWords[j] + ' Only';
                        break;
                    case 1:
                        tens_complication(ClientID_words);
                        break;
                    case 2:
                        if (actnumber[i] == 0) {
                            inWords[j] = '';
                        }
                        else if (actnumber[i - 1] != 0 && actnumber[i - 2] != 0) {
                            inWords[j] = iWords[actnumber[i]] + ' Hundred and';
                        }
                        else {
                            inWords[j] = iWords[actnumber[i]] + ' Hundred';
                        }
                        break;
                    case 3:
                        if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                            inWords[j] = '';
                        }
                        else {
                            inWords[j] = iWords[actnumber[i]];
                        }
                        if (actnumber[i + 1] != 0 || actnumber[i] > 0) {
                            inWords[j] = inWords[j] + " Thousand";
                        }
                        break;
                    case 4:
                        tens_complication(ClientID_words);
                        break;
                    case 5:
                        if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                            inWords[j] = '';
                        }
                        else {
                            inWords[j] = iWords[actnumber[i]];
                        }
                        if (iWordsLength <= 7) {
                            inWords[j] = inWords[j] + " Lakh";
                        }
                        else {
                            if (actnumber[i] == 0 && actnumber[i + 1] != 0) {
                                inWords[j] = inWords[j] + " Lakh";
                            }
                            if (actnumber[i] != 0 && actnumber[i + 1] == 0) {
                                inWords[j] = inWords[j] + " Lakh";
                            }
                        }                      
                        break;
                    case 6:
                        tens_complication(ClientID_words);
                        break;
                    case 7:
                        if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                            inWords[j] = '';
                        }
                        else {
                            inWords[j] = iWords[actnumber[i]];
                        }
                        inWords[j] = inWords[j] + " Crore";
                        break;
                    case 8:
                        tens_complication(ClientID_words);
                        break;
                    default:
                        break;
                }
                j++;
            }

To Check Decimal Point Validation, calculate Amount in Grid control based on selected DropDown Value

<script language="javascript">
    
        function CalculateTotalsInGrid(id) {
            var txtControl = document.getElementById(id);
                var NumberEntered = txtControl.value;
                var count = 0;
                var LastIndexOfDot = 0;
                for (var i = 0; i < NumberEntered.length; i++) {
                    if (NumberEntered[i] == '.') {
                        count = count + 1;
                        if (count == 2) {
                            LastIndexOfDot = i;
                        }
                    }
                }
                if (LastIndexOfDot > 0) {
                    document.getElementById(id).value = NumberEntered.substring(0, LastIndexOfDot);
                }
                var totalDoll=0;
                var val1=1;
                if(efg != "INR")
                {                  
                    val1 = parseFloat(document.getElementById('<%=txtExchangerate_TEB.ClientID%>').value);
                }
                var gdv = document.getElementById('<%=gdvExpenseSummary_Travel_TEB.ClientID%>');
                for (var i = 1; i < gdv.rows.length; i++) {
                    var Curramnt =parseFloat(gdv.rows[i].cells[8].childNodes.item(0).value);
                    var date = gdv.rows[i].cells[1].childNodes.item(0).value;
                    var ModeOfTransport = gdv.rows[i].cells[4].childNodes.item(0).value;
                    if (trim(date, " ") != "" && trim(ModeOfTransport, " ") != "")
                    {
                        totalDoll+=Curramnt;
                        gdv.rows[i].cells[9].childNodes.item(0).value =parseFloat(val1 * Curramnt);
                        gdv.rows[i].cells[9].childNodes.item(0).focus();
                    }
                    else
                    {
                        if(date == "")
                        {
                        alert('Please Select Date at Sr.No'+i);
                        }
                        else if(ModeOfTransport == "")
                        {
                        alert('Please Select ModeOfTransport at Sr.No'+i);
                        }
                    }
                }

                var efg=document.getElementById('<%=ddlCurr_Travel_TEB.ClientID%>').value;
                if(efg != "INR")
                {
                var gdv2 = document.getElementById('<%=txtConveyExpenseDyn_Travel_TEB.ClientID%>');
                gdv2.value=parseFloat(totalDoll);
                }
        }

Get Correct Element ID based on fired Events

function ValidateIDofControlRevCalc(id) {
               
                var val2 = parseFloat(NumberEntered);
                var ansD;
                if(event.srcElement.id == "<%= txtMiscellaneousAmount_Travel_TEB.ClientID%>")
                {
                    ansD = document.getElementById('<%=txtMiscExpenseDyn_Travel_TEB.ClientID%>');
                }
               
            }

Get Selected row from GridView

function GetSelectedRow(lnk) {
            var row = lnk.parentNode.parentNode;
            var rowIndex = row.rowIndex - 1;

            var start = row.cells[5].getElementsByTagName("input")[0].value;
            var end= row.cells[6].getElementsByTagName("input")[0].value;

            alert(start);
            alert(end);

            var t1=0; var t2=-0;
            var _txt3=0;

            if (start.value != "")
            t1 = start.value;
            alert(t1);
           

            if (end.value != "")
             t2 = end.value;
               alert(t2);
      
           if(t2 >= t1)
            _txt3.value = parseInt(t1) - parseInt(t2);
            alert(_txt3.value)
}

Fetch Sample data in Teradata

We can fetch sample data from Teradata table as shown below:-

SELECT *  FROM PROD.EMPLOYEE SAMPLE 10;

Drop Macro in Teradata

We can drop Macro in Teradata as:-

DROP MACRO MY_MACRO;

Parameterize Macro in Teradata

We can have parameterized Macro in Teradata as:-

CREATE MACRO MY_MACRO(PAR_EMP_NO INTEGER) AS
(
SELECT *  FROM PROD.EMPLOYEE
WHERE EMP_NO=PAR_EMP_NO;
);


To execute parameterized Macro:-

EXEC MY_MACRO(10);

Execute Macro in Teradata

After creating Macro we can execute it as:-

CREATE MACRO MY_MACRO AS
(
SELECT *  FROM PROD.EMPLOYEE;
);

EXECUTE MY_MACRO;

Create Macro in Teradata

A macro allows us to name a set of one or more statements and execute it as a group.

The syntax of creating Macro is:-

CREATE MACRO MACRO_NAME AS
(
 SQL_STATEMENTS;
) ;

Sample Macro is:-

CREATE MACRO MY_MACRO AS
(
SELECT * FROM PROD.EMPLOYEE;
) ;

Executing procedure in Teradata

We can execute procedure in Teradata as shown below:-

CALL PROCEDURE PROCEDURE_NAME;

If you have procedure called PROC_UPSERT then:-

CALL PROCEDURE PROC_UPSERT;

If your procedure is parameterize then:-

CALL PROCEDURE PROCEDURE_NAME(PARAMETER (S)) ;

Truncate in Teradata

In teradata, we do not have Truncate command as such but below is the command which work similar to the Truncate command:-

DELETE FROM DATABASE_NAME.TABLE_NAME ALL;

If you have HR database and Employee table then:-

DELETE FROM HR.EMPLOYEE ALL;

Release Mload in Teradata

While inserting data from multi load into teradata table, multi load may fail and your main table may get locked. This lock can be in Acquisition phase or in Application phase.

To release the lock which is in Acquisition phase:-

RELEASE MLOAD TABLE_NAME;

To release the lock which is in Application phase:-

RELEASE MLOAD TABLE_NAME IN APPLY;


 

RTRIM in Teradata

We can trim extra spaces from right hand side from a string using RTRIM function:-

SELECT RTRIM('This Is Me.   ');

Output is:- This is me. (Without trailing spaces)

LTRIM in Teradata

Using LTRIM, We can remove extra spaces from a string from the left hand side of the string:-

SELECT LTRIM('  This Is Me.');

Output is:- This Is Me. (without leading spaces)

Trim in Teradata

We can trim a character in teradata as shown below:-

If you want to trim particuler character from both the end (Leading and Trailing) then you need to use 'BOTH' keyword:-

SELECT TRIM (BOTH '1' FROM '12341');

Output is:- 234

You can trim leading character from a string:-

SELECT TRIM (LEADING '1' FROM '11234');

Output is:- 234

You can trim trailing character from a string:-

SELECT TRIM (TRAILING '4' FROM '12344');

Output is:- 123

Add column in table in Teradata

We can add column in existing table in Teradata. But you may require it in the middle of the table of it is okey if you add it at the last of the table. When you required to add it in the middle of the table then you need to take a backup of table and then drop the table and recreate it and again insert the data from the backup table. But if you want to add column in the last of the table then you can simply add this by simple altering the definition of the table. Let's see how we can do it:-

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE (VALUE_LENGTH);

Let's say you have EMPLOYEE table and you need to add column ADDRESS column into it then you can do it like:-

ALTER TABLE EMPLOYEE ADD ADDR VARCHAR(20);

You can add multiple column in a table:-

Let's say you need to add ADDRESS AND PHONE NUMBER column to employee table then you can do it like:-

ALTER TABLE EMPLOYEE ADD ADDR VARCHAR(20), PHONE_NO INTEGER;

Creating Global Temporary Table in Teradata

When you need to perform some DML (Data Manipulation Language) operation and then want to delete all the data from the table but don't want to DROP the table as you may require it for next time to use, you can create GLOBAL TEMPORARY TABLE in teradata which delete all the data from the table when you log out from the session but keeps the definition of the table intact.

Let's see how we can create Global Temporary Table:-

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
.
.
)
 ON COMMIT PRESERVE ROWS;

Note:- When you are creating global temporary table and forget to mention ON COMMIT PRESERVE ROWS then you would not be able to see the data as teradata create global temporary table as ON COMMIT DELETE ROWS by default. So, work accordingly as per your requirement.

Creating Volatile table in teradata

Volatile table is a table when you log out, table's definition and data gets vanished. It is like temporary tables except temporary tables definition remains in the data dictionary whereas volatile tables gets vanished.
Let's see how to create volatile table in teradata:-

CREATE VOLATILE TABLE VOL_TABLE_NAME
(
 COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
.
.
)
ON COMMIT PRESERVE ROWS; 

Note:- When you are creating volatile table and forget to mention ON COMMIT PRESERVE ROWS then you would not be able to see the data as teradata create volatile table as ON COMMIT DELETE ROWS by default. So, work accordingly as per your requirement.

Find out list of tables in Teradata

We can fetch all the tables present in teradata database using below query:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T';

 Below command will show you the number of tables in all the Databases:-

SELECT  COUNT(*) FROM DBC.TABLES WHERE TABLEKIND ='T';

If you want to find the table list for a particular database then just add another where condition to it:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T' AND DATABASENAME ='UAT';

Below command will show you the number of tables in particular Databases:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T' AND DATABASENAME ='UAT';


As shown above you can find out Views, Procedures etc by altering where condition.

Assign sequence number to variable in Oracle

In Oracle, Upto 10G, We need to SELECT the sequence next value from DUAL table. But in Oracle 11G onwards, We can directly assign a sequence next value to a variable which will avoid round trip to the Oracle server. Let's see how it is:-

First create a sequence in Oracle:-

CREATE SEQUENCE TEST_SEQ
START WITH 1
MIN VALUE 1
INCREMENT BY 1
MAX VALUE 9999999999999999
NO RECYCLE
NO CACHE;

Now, in your block, declare a variable in DECLARE section and assign value to that variable in EXECUTABLE section:-

DECLARE
SEQ_VALUE NUMBER;
BEGIN
SEQ_VALUE :=TEST_SEQ.NEXTVAL;
END;

Above code will directly assign the sequence number to variable without selecting the value from DUAL table.

SQL Tuning Tip

While creating procedure, Function, Package or anonymous block, sometimes we need to use LOOP based on some conditions. While using that condition we can apply some tactics to make the condition better and improve the performance. Let's look at the below example:-

Say, I have to create a loop based on some condition below:-

I have to see that is there any records in the table based on condition, If yes then go to the LOOP otherwise don't do anything. Code can be below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A

FROM EMPLOYEE;

IF VAR_A >0 THEN

LOOP

----

----DO SOMETHING

----

END LOOP;

END IF;

END;

In above code there is no problem with logic as we need to perform some task if my variable is having count more than 0. But it can be improved by using ROWNUM column in where condition as shown below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A
FROM EMPLOYEE
WHERE ROWNUM<=2;

IF VAR_A >0 THEN

LOOP
----
----DO SOMETHING
----

END LOOP;

END IF;

END;

Above code will also do the same thing what the earlier code could have done but while counting it can take little time than first code.

Find out version in Greenplum

We can find out version of the Greenplum database using below query:-

gpstate -i

Here are 5 frequently asked questions (FAQs) about finding out the version in Greenplum:-

1. How can I check the version of Greenplum Database?

   - You can check the version of Greenplum Database by connecting to the database and running a SQL query to retrieve the version information. Here's an example query:

     

     SELECT version();

     

     This query will return the version information of the Greenplum Database.

2. Is there a command-line tool to check the Greenplum version?

   - Yes, you can use the `gpversion` command-line utility to check the version of Greenplum. Simply run the following command in your terminal:

     bash

     gpversion

     

     This command will display the version information of the Greenplum Database installed on your system.

3. Can I check the Greenplum version from the Greenplum Command Center (GPCC)?

   - Yes, if you have Greenplum Command Center (GPCC) installed and configured, you can view the Greenplum version from the GPCC web interface. The version information is typically available on the dashboard or in the system information section.

4. How do I interpret the version information returned by Greenplum?

   - The version information returned by Greenplum typically includes the major version number, minor version number, and any additional release or build information. For example, a version string might look like "Greenplum Database 6.0.0 build version".

5. Is it important to know the Greenplum version?

   - Yes, knowing the Greenplum version is important for various reasons, including compatibility with client tools and applications, understanding feature availability and limitations, and planning for upgrades or patches. Additionally, different versions may have different performance characteristics or bug fixes that could affect your deployment.

These FAQs should help you understand how to find out the version of Greenplum Database and why version information is important for managing and maintaining your Greenplum environment.

Find out version in Teradata

We can find out version of the teradata by using below query:-

SELECT * FROM DBC.DBCINFO;




Read Also:-  Find out version Greenplum

Find out version in Oracle

We can find out version of the Oracle database by using below query:-

SELECT BANNER FROM V$VERSION;

SELECT * FROM V$VERSION;

Here are 5 frequently asked questions (FAQs) about finding out the version in Oracle:-

1. How do I check the Oracle Database version?

   - You can check the Oracle Database version by querying the `v$version` or `v$instance` data dictionary views. These views provide information about the database version, edition, and other details. For example:

     

     SELECT * FROM v$version;

     

     This query will return the version information for various components of the Oracle Database.

2. Can I determine the Oracle Database version without connecting to the database?

   - No, you cannot determine the Oracle Database version without connecting to the database. Version information is stored within the database and can only be accessed through a database connection.

3. What does the Oracle Database version number consist of?

   - The Oracle Database version number typically consists of several components, including the major version number, release number, and patch set update (PSU) number. For example, Oracle Database 19c has a major version number of 19, a release number of 1, and may have a PSU number indicating a specific patch set update.

4. How often should I check the Oracle Database version?

   - It's a good practice to check the Oracle Database version periodically, especially when planning upgrades, applying patches, or troubleshooting compatibility issues with applications or third-party tools. Additionally, version information can help ensure that your database is supported by Oracle and receives necessary updates and security patches.

5. Can I determine the Oracle Database version from SQL*Plus or SQL Developer?

   - Yes, you can determine the Oracle Database version from SQL*Plus or SQL Developer by executing the same SQL query against the `v$version` or `v$instance` views. Both SQL*Plus and SQL Developer provide a SQL interface to interact with the database and execute queries.

These FAQs should provide a comprehensive understanding of how to find out the version in Oracle Database and why version information is important for managing and maintaining databases.

Find out version in MySql

We can find out version of the MySql database by using below query:-

mysql> SHOW VARIABLES LIKE "%version%";





Read Also:-  Find out version in MariaDB

Find out version in SQL

We can find out version of the SQL database by using below query:-

SELECT @@VERSION




Read Also:-  Find out version in Oracle

single-row subquery returns more than one row in Oracle

ORA-01427: single-row subquery returns more than one row error occures when we are comparing two or more values with one using equals to (=) sign in subquery.

We can avoid this error using IN clause shown below:-

CREATE TABLE P_TEST
(
 EMP_NO   NUMBER,
 EMP_NAME  VARCHAR2(10),
 DEPTNO   NUMBER
);

CREATE TABLE P_TEST_2
(
 DEPTNO   NUMBER,
 DEPTNAME  VARCHAR2(10)
);

INSERT INTO P_TEST
VALUES (1,'CHANCHAL',10);

INSERT INTO P_TEST
VALUES (2,'WANKHADE',20);

INSERT INTO P_TEST
VALUES (3,'SCOTT',30);

INSERT INTO P_TEST_2
VALUES (10,'FIANANCE');

INSERT INTO P_TEST_2
VALUES (20,'HR');

INSERT INTO P_TEST_2
VALUES (30,'IT');

SQL>
SQL> SELECT DEPTNO FROM P_TEST
  2  WHERE DEPTNO =
  3  (SELECT DEPTNO FROM P_TEST_2);
(SELECT DEPTNO FROM P_TEST_2)
 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

Above sql statement is not working. Let's try to use below on with IN clause:-

SQL>
SQL>
SQL> SELECT * FROM P_TEST
  2  WHERE DEPTNO IN
  3  (SELECT DEPTNO FROM P_TEST_2);
    EMP_NO EMP_NAME       DEPTNO
---------- ---------- ----------
         1 CHANCHAL           10
         2 WANKHADE           20
         3 SCOTT              30
3 rows selected.

Executing Windows command from Oracle Script

We can execute windows command from Oracle script. To achieve this we have a command called HOST which execute the command on host and again return to the Oracle prompt.

Let's take a scenario, you have to delete file automatically from windows directory while you are running the Oracle script and you don't want manual intervention. You can use HOST command to do so.

Say, you have 5 procedure that you needs to execute, but after executing 3 procedure you need to DELETE the file from the windows directory, to do this you can perform steps shown below:-

SQL> EXEC PROCEDURE_1;
      procedure executed successfully.
SQL> EXEC PROCEDURE_2;
      procedure executed successfully.
SQL> EXEC PROCEDURE_3;
      procedure executed successfully.
SQL> HOST 'DEL D:/CHANCHAL/TEST.CSV'
SQL> EXEC PROCEDURE_4;
      procedure executed successfully.
SQL> EXEC PROCEDURE_5;
      procedure executed successfully.


Like shown above, you can perform many other windows operations like creating windows directory, file , rename file and many more.....

COALESCE function in MariaDB

COALESCE function in MariaDB return default value if there is NULL value in the second expression.

Syntax:-

COALESCE (DEFAULT_VALUE,'EXPRESSION,'')

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT COALESCE(EMPNAME,'CHANCHAL') AS EMPNAME FROM EMPLOYEE WHERE EMPNO=7;

--> CHANCHAL

SELECT COALESCE(NULL,'CHANCHAL') AS EMPNAME FROM EMPLOYEE WHERE EMPNO=7;

--> CHANCHAL

SELECT COALESCE(EMPNO,100) FROM EMPLOYEE WHERE EMPNO=7;

--> 7




Read Also:-  IFNULL function in MySql

COALESCE function in Greenplum

COALESCE function in Greenplum return default value if there is NULL value in the second expression.

Syntax:-

COALESCE (DEFAULT_VALUE,'EXPRESSION,'')

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT COALESCE(EMPNO,EMPNAME,'') AS EMPNAME FROM EMPLOYEE;;

--> 7

In above example, if EMPNAME is null then it will show EMPNO values.

Here are 5 frequently asked questions (FAQs) about the COALESCE function in Greenplum, along with their answers:-

1. What is the COALESCE function in Greenplum?

   - The COALESCE function in Greenplum is used to return the first non-null expression in a list of expressions. It evaluates the arguments in order and returns the value of the first expression that is not null. If all expressions are null, COALESCE returns null.

2. How do I use the COALESCE function in Greenplum?

   - To use the COALESCE function, simply provide a list of expressions separated by commas as arguments. For example:

     

     SELECT COALESCE(column1, column2, 'default') AS result FROM table_name;

     

     This query will return the first non-null value from column1, column2, or the string 'default'.

3. Can I use COALESCE with aggregate functions in Greenplum?

   - Yes, you can use COALESCE with aggregate functions in Greenplum. It's often used in conjunction with aggregate functions to handle null values gracefully. For example:

     

     SELECT SUM(COALESCE(column1, 0)) AS total FROM table_name;

     

     This query will return the sum of column1, replacing any null values with 0.

4. What happens if all expressions in COALESCE are null?

   - If all expressions provided to the COALESCE function are null, it will return null. However, you can provide a default value as the last argument to COALESCE, which will be returned if all expressions evaluate to null.

5. Are there any performance considerations when using COALESCE in Greenplum?

   - While COALESCE is a convenient function for handling null values, excessive use of it, especially in complex queries or with large datasets, can impact performance. It's important to use COALESCE judiciously and optimize queries where possible to ensure optimal performance.

These FAQs should provide a good understanding of the COALESCE function in Greenplum and how it can be used effectively in SQL queries.

COALESCE function in Teradata

COALESCE function in Teradata return default value if there is NULL value in the first expression.

Syntax:-

COALESCE(EXPRESSION,'DEFAULT_VALUE);

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null

SELECT COALESCE(EMPNAME,'CHANCHAL') as EMPNAME;

EMPNAME
-------------------
CHANCHAL


You can have many default values. Teradata will check if first expression is null then it will show second expression if it is not null, if second
is also null then it will show third expression likewise.

SELECT EMPNO, COALESCE (EMPNAME,NULL,'CHANCHAL') FROM  EMPLOYEE;

-->  CHANCHAL

SELECT EMPNO, COALESCE (EMPNAME,NULL,NULL,'WANKHADE') FROM  EMPLOYEE;

-->  WANKHADE




Read Also:-  COALESCE function in Greenplum

NVL function in Oracle

NVL function in Oracle return default value if there is NULL value in the first expression.

Syntax:-

NVL(EXPRESSION,'DEFAULT_VALUE);

Example:-

SELECT EMPNO,NVL(EMPNAME,'CHANCHAL') AS EMPNAME,NVL(SALARY,9000) AS SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

SQL> SELECT * FROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7

7 rows selected.

SQL> SELECT EMPNO,NVL(EMPNAME,'CHANCHAL') AS EMPNAME,NVL(SALARY,9000) AS SALARY
  2  FROM EMPLOYEE
  3  WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 CHANCHAL                   9000




Read Also:-  ISNULL function in SQL

IFNULL function in MySql

IFNULL function in MySql return default value if there is NULL value in the first expression.

Syntax:-

IFNULL (EXPRESSION,'DEFAULT_VALUE');

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT EMPNO,IFNULL(EMPNAME,'CHANCHAL') EMPNAME,IFNULL(SALARY,2000) SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000


or we can use the COALESCE() function, like this:

SELECT EMPNO,COALESCE(EMPNAME,'CHANCHAL') AS EMPNAME,COALESCE(SALARY,5000) AS SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000




Read Also:-  COALESCE function in MariaDB

ISNULL function in SQL

ISNULL function in SQL return default value if there is NULL value in the first expression.

Syntax:-

ISNULL (EXPRESSION,'DEFAULT_VALUE');

EXAMPLE:-

SELECT * FROM EMPLOYEE;


SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null

SELECT EMPNO,ISNULL(EMPNAME,'SCOTT') AS EMPNAME,ISNULL(SALARY,10000) SALARY
FROM EMPLOYEE
WHERE EMPNO=7;


     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000


SELECT ISNULL(NULL,'CHANCHAL WANKHADE');

--> CHANCHAL WANKHADE

SELECT ISNULL('CHANCHAL','WANKHADE');

--> CHANCHAL

We can use it to handle the date as well.

SELECT ISNULL(NULL, '2010-01-01');

--> '2010-01-01'




Read Also:- NVL function in Oracle

ALIASES in MariaDB

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in MySql

ALIASES in Greenplum

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E INNER JOIN DEPARTMENT D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Teradata

ALIASES in Teradata

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Greenplum

ALIASES in Oracle

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO FROM EMPLOYEE;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in SQL

ALIASES in MySQL

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in MairaDB

ALIASES in SQL

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E , DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E , DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Oracle

INITCAP function in MariaDB

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

Here are some common questions related to initializing the capitalization of strings in MariaDB:-

1. How can I capitalize the first letter of each word in a string in MariaDB?

   - You can achieve this by using a combination of functions like CONCAT, UPPER, LOWER, and SUBSTRING_INDEX. Here's an example:

     

     SELECT CONCAT(UPPER(SUBSTRING_INDEX(column_name, ' ', 1)), ' ', 

                   UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ' ', 2), ' ', -1)), ' ',

                   UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ' ', 3), ' ', -1))) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in MariaDB like in other databases?

   - No, MariaDB doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in MariaDB?

   - Yes, you can create a stored function to implement INITCAP functionality. Here's an example of how you might implement it:

     

     DELIMITER //

     CREATE FUNCTION INITCAP(str VARCHAR(255)) RETURNS VARCHAR(255)

     BEGIN

       DECLARE result VARCHAR(255);

       SET result = CONCAT(UPPER(SUBSTRING(str, 1, 1)), LOWER(SUBSTRING(str, 2)));

       RETURN result;

     END //

     DELIMITER ;

     

     You can then use this INITCAP function in your queries.

4. Does MariaDB provide any alternatives for capitalizing strings?

   - While MariaDB doesn't have an INITCAP function, you can still use other string functions like UCASE and LCASE to convert strings to uppercase or lowercase, respectively.

5. Are there any performance considerations when using custom functions for string manipulation in MariaDB?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment.

INITCAP function in Greenplum

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

Here are some common questions related to capitalizing strings in Greenplum:-

1. How can I capitalize the first letter of each word in a string in Greenplum?

   - You can achieve this by using a combination of functions like INITCAP, SPLIT_PART, UPPER, and SUBSTRING. Here's an example:

     

     SELECT INITCAP(SPLIT_PART(column_name, ' ', 1)) || ' ' ||

            INITCAP(SPLIT_PART(column_name, ' ', 2)) || ' ' ||

            INITCAP(SPLIT_PART(column_name, ' ', 3)) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in Greenplum like in other databases?

   - No, Greenplum doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in Greenplum?

   - Yes, you can create a custom user-defined function (UDF) to implement INITCAP functionality. You would typically write this function in a programming language like Python or PL/pgSQL and then load it into Greenplum as an extension.

4. Are there any alternative methods for capitalizing strings in Greenplum?

   - While Greenplum doesn't have an INITCAP function, you can still use other string functions like UPPER and SUBSTRING to manipulate strings as needed.

5. Are there any performance considerations when using custom functions for string manipulation in Greenplum?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment. Additionally, in Greenplum, distributing data efficiently across segments can help improve query performance.

INITCAP function in Teradata

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

In Teradata, there isn't a built-in INITCAP function like in some other database systems. However, you can achieve similar functionality using a combination of string functions. Here are some common questions related to capitalizing strings in Teradata:

1. How can I capitalize the first letter of each word in a string in Teradata?

   - You can achieve this by using a combination of functions like INITCAP, SUBSTRING, and UPPER. Here's an example:

     

     SELECT INITCAP(SUBSTRING(column_name FROM 1 FOR 1)) || 

            LOWER(SUBSTRING(column_name FROM 2)) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in Teradata like in other databases?

   - No, Teradata doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in Teradata?

   - Yes, you can create a custom function using Teradata's procedural SQL (PL/SQL) or External Stored Procedures (XSP). However, creating custom functions may involve more complexity compared to using built-in functions.

4. Are there any alternative methods for capitalizing strings in Teradata?

   - While Teradata doesn't have an INITCAP function, you can still use other string functions like UPPER and LOWER to manipulate strings as needed.

5. Are there any performance considerations when using custom functions for string manipulation in Teradata?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment. Additionally, in Teradata, optimizing queries and proper indexing can help improve performance.