Posts

Showing posts from July, 2013

" DUAL " KEYWORD IN SQL SERVER / ORACLE

What is DUAL in Oracle? Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X. You can check the content of the DUAL table using the following syntax. SELECT * FROM dual It will return only one record with the value ‘X’. What is the reason for following error in SQL Server? Msg 208, Level 16, State 1, Line 1 Invalid object name ‘dual’. The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround. What is the Equivalent of DUAL in SQL Server to get current datetime? Oracle: select sysdate from dual SQL Server: SELECT GETDATE() What is the equivalent of DUAL in SQL Server? None. There is no need of Dual table in SQL Server at all. Oracle: select ‘something’ from dual SQL Server: SELECT ‘something’ I have to ha

WHAT IS - > " SELECT * FROM [TABLE_NAME] WHERE 1=1 " ??

What is select * from xxx where 1=0 what does this mean? also there is one select * from yyy where 1=1 ?? ?? ANSWER -> 1. Select * from table where 1=0 return just header of the fields (attribute) but with 0 rows 2. Select * from table where 1=1 return whole table entries this is same as select * from table Simple ans. Logical ? If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like: and < condition >   and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with. I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

How to get top " n " number of rows from table

Suppose you want top n values from table , so execute following query with replacement of following item as mention below : 1 )  Replace your our tablename with [table_name] 2 )  Replace your "n" with row numbers  (how many rows you want ) [ Note :  You can do multiply and division operations also ] Query for Top rows : SELECT MainSQL .* FROM ( SELECT t .*, ROW_NUMBER () OVER ( ORDER BY 1 ) Row_Number , COUNT ( 1 ) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count FROM (SELECT * FROM [table_name] )  t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1 AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number ; Query for Bottom  rows : SELECT MainSQL .* FROM ( SELECT t .*, ROW_NUMBER () OVER ( ORDER BY 1 ) Row_Number , COUNT ( 1 ) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count FROM (SELECT * FROM [table

Removing Leading Zeros From Column in Table

Step 1:  Improving the ResultSet I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well. USE tempdb GO -- Create sample table CREATE TABLE Table1 ( Col1 VARCHAR ( 100 )) INSERT INTO Table1 ( Col1 ) SELECT '0001' UNION ALL SELECT '000100' UNION ALL SELECT '100100' UNION ALL SELECT '000 0001' UNION ALL SELECT '00.001' UNION ALL SELECT '01.001' UNION ALL SELECT '0000' Step 2: SELECT CASE PATINDEX ( '%[^0 ]%' , Col1 + ' ‘' ) WHEN 0 THEN '' ELSE SUBSTRING ( Col1 , PATINDEX ( '%[^0 ]%' , Col1 + ' ' ), LEN ( Col1 )) END FROM Table1 See the result.........

Difference between inner join and outer join

Assuming you're joining on columns with no duplicates, which is by far the most common case: An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection. An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union. Examples Suppose you have two Tables, with a single column each, and data as follows: A B - - 1 3 2 4 3 5 4 6 Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B. Inner join An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common. select * from a INNER JOIN b on a . a = b . b ; select a .*, b .* from a , b where a . a = b . b ; a | b --+-- 3 | 3 4 | 4 Left outer join A left outer join will give all rows in A, plus any common rows in B. select * from a LEFT OUTER JOIN b on a . a = b . b ; select a .*, b .* from a , b w

How to get primary key column in Oracle?

Step 1:   Create sample table with one of the column defined as primary key constraint   Step 2:   Execute following command : SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols WHERE cols.table_name = 'TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; :):)  Enjoy

Working with Oracle's BLOB and Microsoft's C#

We used this title for describing technologies , we are working in. Here we will see "  How to store files in oracle database using c# ?   " Step 1:           Use file uploader controller to browse directories. Step 2:          Before uploading file or its path , we need to understand BLOB of Oracle          BLOB  ( Binary Large Objects ) :                                                    File content store in binary format.In Oracle , BLOB and    CLOB  (Character Large Object) used.Technically BLOB prefered.For more info regarding BLOB click here  . Step 3 :        In C# , use System.IO namespace for file operations.     Step 4:         string path = FileUpload1.PostedFile.FileName.ToString();         string FileName = FileUpload1.FileName.ToString();         string FileSize = FileUpload1.PostedFile.ContentLength.ToString();         string FileContent = FileUpload1.PostedFile.ContentType.ToString();         string filename = Path.GetFileName(path