Problem Statement:You want to find out totals, subtotals and a grand total in Oracle.Solution:Oracle ROLLUP function performs grouping at multiple levels, using a right to left method of rolling up through intermediate levels to any grand total. To demonstrate the ROLLUP function we will create a table to hold tennis player along with the ATP tour titles and Grandslam titles acheived by the player.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles (   player             VARCHAR2(100) NOT NULL,   title_type ... Read More

Problem Statement: You want to find and replace a string in Oracle.Solution:Function: TRANSLATESyntax: TRANSLATE(expr, from_string, to_string)TRANSLATE funtion in oracle allows you to make many single character, one on one substitutions in a single operation.However, the to_string and from_string values must not be empty, if an empty string is passed to TRANSLATE Function, Oracle Database interprets the empty string as null and returns null./* replace all occurances of b with j in below string */ SELECT 'back and bill ' AS original_string        , TRANSLATE('back and bill', 'b', 'j') AS replaced_string   FROM dual;Outputback and bill   jack and ... Read More

Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.SQL:/*   Function - Example to show if the row is parent of any other rows or not   Tables Used - students Data - Documented below */ SELECT student_id,        level,        CASE WHEN connect_by_isleaf = 0             THEN 'Yes'         ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom but doesn’t want a particular branch in the output.Solution: We will look at couple of examples for this problem statement.Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom and sort the rows at the same level in a hierarchy.Solution: The usual ORDER BY clause will not sort the rows at the same hierarchy level. We need to use SIBLINGS with in the ORDER BY Clause.Additionally, Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.Solution:Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have ... Read More

To set the line breakpoint in the script, we can use the Set-PSBreakpoint command with -Line parameter and need to give the path of the script on which the Line breakpoint needs to set.Consider we have the script below which retrieves the value up to 99, starting from 1, We will set the Line Breakpoint at line number 3 so we will use the below command. Here the script name is WhieScript.ps1 and stored at C:\temp.Set-PSBreakpoint C:\temp\WhileScript.ps1 -Line 3Once you run the above command, you will get the output with the details as shown below.ID Script    Line Command Variable ... Read More

Breakpoint in the PowerShell is the part of the debugger in the PowerShell commands. We use the breakpoints in PowerShell mainly for troubleshooting and logging purpose.There are three ways to set the breakpoint in PowerShell.Line BreakPoint (Can set the breakpoint for single or multiple lines)Command BreakPoint (Can set the breakpoint for commands or functions)Variable Breakpoint (Can set the breakpoint on a variable or multiple variables).We can’t set the breakpoint for the remote computer. To set the breakpoint for the remote computer, we first need to copy the script to the remote computer and then need to set the breakpoint as ... Read More

To check if the file is empty using PowerShell, we can use the string method called IsNullorWhiteSpace(). This method provides result true if the file is empty or only contains the white spaces otherwise false.For example, We have a test2.txt text file which has whitespaces.Example[String]::IsNullOrWhiteSpace((Get-content C:\Test2.txt))OutputTrueBut if you have a file like CSV which contains few headers but the data is empty, in that case, Get-Content will show the wrong output because it will consider headers. For example, Example[String]::IsNullOrWhiteSpace((Get-content C:\Temp\NewUsers.csv))OutputFalse Because the file has headers.PS C:\> Get-Content C:\Temp\NewUsers.csv Name, FirstName, Surname, EMPNumber, CountryIn that case, we can use the Import-CSV ... Read More

To open any file using its default application, we can use the Invoke-Expression command. For example, we want to open a file on the C:\temp and the file name is NewUsers.CSV then you can run the below command.Invoke-Expression C:\Temp\NewUsers.csvThe above command will open the file from that location. If the default application is not set then Windows will ask for the default application to select.If you know any application name and which can be opened with the shortcut then you can directly type the name of the application. For example, Notepad.exe, Calc.exeGenerally, they can be opened directly but this command ... Read More