Posts

Showing posts from April, 2010

Get the Running Total in Oracle

Image
This very frequent needful thing for developers as they need to create so many reports based on this concept. For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This would add each and every vale with previous value and give like Running Total. Query for this, SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY, SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL FROM PRODUCT_LOCATION WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘ row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set. And ordering results by PRODUCT_NO The result of the above query shown below.

Get table structure using SQL query in SQL Server

Here it s the query for retrieve the table structure in sql server, SELECT Ordinal_Position,Column_Name,Data_Type,Is_Nullable,Character_Maximum_Length INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table Name'