Tutorial by Examples

Transform nested JSON fields into set of rows

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. If the values in input object are nested, additional mapping parameter can be specified in each column in WITH clause: declare @json nvarchar(4000) = N'[ {"data":{"num":&quot...

Extracting inner JSON sub-objects

OPENJSON can extract fragments of JSON objects inside the JSON text. In the column definition that references JSON sub-object set the type nvarchar(max) and AS JSON option: declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00"...

Working with nested JSON sub-arrays

JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00", "Items":[{"Price"...

RANK()

A RANK() Returns the rank of each row in the result set of partitioned column. Eg : Select Studentid,Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From Exam order by name,subject Studentid Name Subject Marks Rank 101 Ivan Maths ...

DENSE_RANK ()

Same as that of RANK(). It returns rank without any gaps: Select Studentid, Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From Exam order by name Studentid Name Subject Marks Rank 101 Ivan Science 80 1 101 Ivan ...

Types and classifications of Trigger

In SQL Server, there are two categories of triggers: DDL Triggers and DML Triggers. DDL Triggers are fired in response to Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER and DROP. DML Triggers are fired ...

How to create a template

Setting up a template for one application Create a file named template.xhtml under the /WEB-INF folder, that way the template files will be accessible only for the framework. /WEB-INF/template.xhtml <!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml&...

TRY PARSE

SQL Server 2012 It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to covert to date type. If conversion attempt fails it returns NULL value. Syntax: TRY_PARSE (string_value AS data_type [ USING culture ]) String_value – This is...

TRY CONVERT

SQL Server 2012 It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same. Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) TRY_CON...

TRY CAST

SQL Server 2012 It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it. Syntax: TRY_CAST ( expression AS data_type [ ( length ) ] ) TRY_CAST() retu...

Cast

The Cast() function is used to convert a data type variable or data from one data type to another data type. Syntax CAST ( [Expression] AS Datatype) The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source ty...

Convert

When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in v...

RESTful API Routes for Multi Module Application

// Define new router group $api = new \Phalcon\Mvc\Router\Group([ 'module' => 'api', ]); $api->setPrefix('/api/v1'); // API routes (Maps to Cotnroller::Action) $api->addGet('/users', 'Users::index'); $api->addGet('/users/search/{query}', 'Users::search'); $api->addGet('/...

Dynamically set module routes

$router = new \Phalcon\Mvc\Router(false); $router->removeExtraSlashes(true); $request = new \Phalcon\Http\Request(); $action = strtolower($request->getMethod()); // get, post, etc. $modules = ['calendar', 'main', 'user']; // names of the modules you create // you can define other static...

Stacking Context

In this example every positioned element creates its own stacking context, because of their positioning and z-index values. The hierarchy of stacking contexts is organized as follows: Root DIV #1 DIV #2 DIV #3 DIV #4 DIV #5 DIV #6 It is important to note that DIV #4, DIV #5 and D...

Bullet Position

A list consists of <li> elements inside a containing element (<ul> or <ol>). Both the list items and the container can have margins and paddings which influence the exact position of the list item content in the document. The default values for the margin and padding may be differe...

Removing Bullets / Numbers

Sometimes, a list should just not display any bullet points or numbers. In that case, remember to specify margin and padding. <ul> <li>first item</li> <li>second item</li> </ul> CSS ul { list-style-type: none; } li { margin: 0; pad...

Block Formatting Context Created with Overflow

Using the overflow property with a value different to visible will create a new block formatting context. This is useful for aligning a block element next to a floated element. CSS img { float:left; margin-right: 10px; } div { overflow:hidden; /* creates block formatting context...

Tuning PhpStorm performance by editing custom VM options

Its possible to change *.vmoptions and idea.properties files without editing them in the PhpStorm installation folder. Follow the steps below: Step 1: Run Help - Edit Custom VM Options... Step 2: Confirm the creation of the configuration file, if prompted Step 3: Add following lines if yo...

onPostSubmit Event

This is an Education From in Symfony to take user education details. We wanted to apply validation on 2 fields, education end date and is currently studying. On Post Submit Event, We will check two things 1 - if the user checks the checkbox of is_currently studying then end date should be empty ...

Page 691 of 1336

logo rip
SUPPORT & PARTNERS
STAY CONNECTED

Get monthly updates about new articles, cheatsheets, and tricks.