" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi

How To Use custom Formula For Setting Condition Of Any Trigger On SuiteFlow Actions

Posted on November 24th, 2015 by Puja Roy

In this tip, we will see, how to use custom formula for setting condition of any trigger on SuiteFlow actions. We can set custom formula using Javascript/Suitescript(Client Trigger) and using SQL functions(Server Trigger). Here, I am explaining with simple examples of SuiteFlow action. Suppose, there is a requirement such as- when an Item record is created, […]

Transact-SQL :- ROW_NUMBER()

Posted on November 24th, 2015 by Rashmita Devi

While creating query we may need a column which will hold the sequential number like 1, 2, 3… Also we may need to show that sequential number according to a particular partition. In that situation we can use the ‘ROW_NUMBER’ keyword. Below syntax can be followed :-   To get sequential number irrespective of any partition.  […]

Which one to use in a SQL query:-“Not Exists”/ Left Join / Not In”?

Posted on November 24th, 2015 by Pallavi Kar

It is quite a common request to create a Transact-SQL query to compare a parent and a child table and find out if there are any parent records that don’t have a corresponding record in the child table. Generally, this can be done with one of the three ways: So which one of the below is a better practice.Lets take a look. # […]

Customized deletion of Users from core tables in database of a DNN application.

Posted on November 24th, 2015 by CHANDAN KUMAR

Customized deletion of  Users  from core database tables of a DNN application . Core tables which contain dnn application user informations are :: aspnet_Membership,  aspnet_Users ,UserProfile ,UserPortals ,UserRoles ,Users .Note:: This is needed in many cases such as database cleanup of all test users created .As it’s quite hectic to delete each users one by […]

Deleting Duplicate records in SQL Server

Posted on November 24th, 2015 by Rashmita Devi

As I have mentioned in one of my previous tips that using ROW_NUMBER() we can get the serial number which is here. Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table. Lets say I have a Users Table which is having duplicate records. I need to delete […]

Top with ties option in SQL Server

Posted on November 24th, 2015 by Madhusmita Rout

If you are required to select TOP N records from a table being ordered by a column,  you can use the following query:         SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]        But there is one problem with the above statement is that if the table has multiple records having the same value  as that of the selected list […]

How to use UPDATE with JOIN in SQL queries

Posted on November 24th, 2015 by Mritunjay Kumar

We use JOIN in the SELECT statement often to retrieve data from multiple related tables. But, some times we can get into a situation where we may need to use UPDATEstatement by joining multiple tables. Consider a situation: A table named as USER_DATA is there which contains user details. This table has one column(along with other columns) named as  IS_ACTIVE […]

TRY- CATCH in SQL SERVER 2005/2008

Posted on November 24th, 2015 by Amit Ranjan

With SQL Server 2005’s TRY – CATCH block error handling is now  a cheese bite. Lets have an example CREATE PROCEDURE spGetAccountDetails @bookName varchar(100), @ISBN bigint AS BEGIN END GO BEGIN TRY –Starts the Try Block.. BEGIN TRANSACTION — transaction..starts UPDATE BookShelf  SET BookName= @bookName WHERE ISBN= @ISBN UPDATE Student SET Withdrawed_Book= @bookName WHERE StudentID= @StudentID COMMIT TRAN — Success! END […]

How to Escape Wildcard Characters While Searching in SQL Server

Posted on November 24th, 2015 by Devi Das

This tip demonstrates how to use special characters like _, %, [  to perform a wildcard search on string values stored in database (SQL Server in this instance) Let us take the example of a table named “TIPS“, which contains a TITLEcolumn (that holds the title of all Tips ). Now, If you want to search a […]

Using SQL JOIN to get data from different tables conditionally

Posted on November 24th, 2015 by Mritunjay Kumar

Last day i came across a situation, where i needed to write a SELECTstatement which will use join with different tables but according to some conditions.  Lets take the below situation:   We have three tables as below : Table : MEMBERS; Columns : MEMBERID, MEMBERNAME, MEMBERPHONE Table : COMPANY; Columns : COMPANYID, FKMEMBERID, WORKPHONE Table : […]

Why not to use TEXT data type in SQL Server 2000

Posted on November 24th, 2015 by Devi Das

1st Tip:   In SQL Server 2000, VARCHAR data type can’t hold long strings. The maximum length it supports is 8000 characters, So if you want to store data more than that, then you can’t use VARCHAR. If you are using SQL Server 2005, then you have an alternative to useVARCHAR(MAX), but in SQL Server 2000 it […]

SET and SELECT in SQL Server

Posted on November 24th, 2015 by Dipali Chakra

As most of us know we  have two ways of assigning values to local variables –  SET and SELECT statements. DECLARE @Var1  INT, @Var2 INT SET  @Var1  = 1 SELECT @Var2 = 2   However, there are some differences between the two statements.   1. SET is the ANSI standard which can assign only one variable at a time. If […]

Retrieving List of DB created by user from SQL Express 2005

Posted on November 24th, 2015 by Shibani Shubhadarshini

To populate the databases in SQL Express 2005 we use SQL Server Management object(SMO). This process retrieves all databases created by user along with the system databases. For getting the list of databases created by users only we have to use IsSystemObject property of databases. If the property is False then these are the databases […]

Using “WITH TIES” SQL Clause

Posted on November 24th, 2015 by Dipali Chakra

If we are using SELECT TOP N query, then it returns exactly N records, and drops any record arbitrarily that has the same value as the last record in the result set. Suppose we want a report showing top 50 costly items. There may be a situation in which the price of 50th item is […]

Using CASE expression in SQL queries

Posted on November 24th, 2015 by Santosh Bisoyi

Use of CASE Expressions in SQL query is sometimes extremely useful.For example Using CASE in SELECT statement provides developer the power to manipulates the data at presentaion layer without changing data at backend.So there are various use of CASE Expressions and it can be used in, including in statements like [SELECT, UPDATE, DELETE, SET ] and […]

Sort data according to value of two columns

Posted on November 24th, 2015 by Narendra Mallik

This tip demonstrates how to sort data according to the values of two columns. In the following example we have a table called “EVENTS” which has 4 fieldsSrNo, EventName, StartDate and EndDate SrNo EventName StartDate EndDate 1 Event1 14/03/2010 18/03/2010 2 Event2 20/03/2010 null 3 Event3 02/03/2010 21/03/2010 The requirement is that the events should […]

INSERT, UPADTE or DELETE a row and return column/columns value with single query

Posted on November 24th, 2015 by Santosh Bisoyi

At times you may need to UPDATE a row from a Table and at the same time you would also want to get the column value from the same SQL UPDATE query. Following is a solution using SQL OUTPUT clause . SQL OUTPUT clause :     The OUTPUT Clause from SQL server provides a very […]

INSERT data from one table into another

Posted on November 24th, 2015 by Santosh Bisoyi

Sometimes you need to insert data from one table to another and there are different solutions for this. Following is the one of them. Data insertion from one table to another can be done either of the following ways : CASE-I ( When both source and destination table are exist ) Example : INSERT INTO Destination(FullName,ContactNum) […]

Some Useful SQL Queries

Posted on November 24th, 2015 by Palash Mondal

Some Useful SQL Queries Here are some SQL queries that I like to use. TO COUNT NUMBER OF TABLES IN A DB SELECT [TABLECOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’U’ TO COUNT NUMBER OF STORED PROCEDURES IN A DB SELECT [SPCOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’P’ GET LIST OF TABLES NAMES AND […]

How to Prevent Duplicate UserID Creation Without Executing SELECT SQL Query

Posted on November 23rd, 2015 by Tapaswi Panda

While registering a new user to our application we generally check if the userId already exist by doing a select query. If the userId exists we should not allow to create another user with same ID. Below is a handy script which explains how we can avoid doing the select query and register a new […]

Using ExecuteXmlReader

Posted on November 23rd, 2015 by Amrita Dash

There is an issue of reading the xml result set returned from SQL with ExecuteScalar method. The xml content gets truncated after some characters. For large XML string , SQL returns the result in multiple rows of XML each of 2033 characters , so using ExecuteScalar to read XML result string returns the first 2033 […]