Oracle WITH Clause (Subquery Refactoring)

December 17, 2009 By Leave a Comment

WITH clause was introduced in Oracle 9i to provide powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.

WITH Clause Syntax

WITH
 alias_name         -- alias for the aggregate_query
AS
 (aggregate_query_here)
SELECT...            -- Beginning of the query main body

It should be noted that multiple aggregate queries can be defined in the WITH clause as below.

WITH
 alias_name1
AS
 (agg_query1) ,
 alias_name2
AS
 (agg_query2)
SELECT...

When using subquery factoring, think of the aggregate_query as an in-line view. Actually, it is a view but a temporary one. Instead of creating a permanent view accessible in the database, you are creating a temporary one exclusively used by your main query.

Usage

I primarily use this in creating adhoc queries, reports and extractions. Of course, you can just create a view to accomplish this task. However, there are times wherein you are not allowed to create additional objects in the database for one reason or another. In that case, this subquery optimization comes very handy.

Caveat

You can’t use this inside Oracle Forms. It also doesn’t work inside stored procedures or anything with PL/SQL in it. At least I’m talking about until 11g.

Incoming search terms:

  • oracle with clause
  • with clause in oracle
  • oracle with clause syntax
  • oracle sql subquery refactor
  • subquery refactoring oracle
  • subquery optimization pl-sql
  • how to create the subquery in oracle using with clause
  • subquery factoring clause + procedure
  • random pl sql de un subquery
  • subquery sp oracle
Filed Under: Oracle, PL/SQL Tagged With: , , ,

Oracle CASE WHEN Statement

December 8, 2009 By Leave a Comment

Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here’s how it is being used.

Syntax #1


CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END

Example


SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;

Syntax #2


CASE
WHEN [ condition_1 ] THEN result_1
WHEN [ condition_2 ] THEN result_2
...
WHEN [ condition_n ] THEN result_n
ELSE result
END

Example


SELECT m.paper_type ,
m.paper_brand ,
m.paper_grammage,
m.paper_width ,
m.paper_length ,
m.paper_stock_no,
(
CASE
WHEN NVL(smc.qty_reserved_onhand,0) != NVL(st.sum_qty_r_oh,0) THEN 'QROH NOT TALLY'
WHEN NVL(smc.qty_reserved_incom,0) != NVL(st.sum_qty_r_incom,0) THEN 'QRINC NOT TALLY'
WHEN NVL(smc.ton_reserved_onhand,0) != NVL(st.sum_ton_r_oh,0) THEN 'TROH NOT TALLY'
WHEN NVL(smc.ton_reserved_incom,0) != NVL(st.sum_ton_r_incom,0) THEN 'TRINC NOT TALLY'
END
) ERROR_MESSAGE
from paper_stock_master m

Personally, I use Syntax #2 most of the time. I don’t know why but maybe it just happened that I find it more suitable to use in the situations that I needed the CASE statement.

Difference Between DECODE and CASE WHEN

The most fundamental difference between the two oracle statements is that in DECODE, you are comparing a field or value in a given set of values or fields. It’s actually a shorthand for IF..ELSIF..ELSE statement with the condition all set to equals (=). CASE WHEN is almost the same but you can use any conditional operator (i.e, =,!=, >, >= , <, <=, etc) thereby giving you more freedom and flexibility in writing your code.

In may instances CASE WHEN helped me reduce the number of lines of my codes as well as optimize the performance. On top of that, I no longer need a full pl/sql program with multiple queries and even cursors to be able to provide the result that a simple CASE statement can provide. However CASE, like any other sql commands, is only applicable for certain scenarios. It’s not a magic keyword that will solve/apply to everything. So you have to weigh your options if you need it or not.

Ciao!

Incoming search terms:

  • oracle case when
  • oracle case sta
  • sql optimize case statement
  • performance select case statement in oracle
  • oracle case when syntax
  • oracle case when statement
  • oracle case when as
  • case statement in oracle
  • oracle case decode optimization
  • optimize the case statement in orac;e
Filed Under: Oracle, PL/SQL Tagged With: , , , , , ,

String Replace in C++

November 20, 2009 By Leave a Comment

As I’m just a C++ poser, I don’t know a lot of things programming in this language especially if I have to deal with STL (Standard Template Library). As some of you know, when you’re not using third party libraries in C++, programming gets a little harder (note : my harder is harder than your harder).

So I searched the web and found this solution. Unfortunately, I forgot where I got this. Anyway, the original program doesn’t work.

#include
#include

using namespace std;

int main()
{
string str( "The Horse and the Yokaba" );
string searchString( "Yokaba" );
string replaceString( "Kabayo" );

string::size_type pos = 0;
while ( (pos = str.find(searchString, pos)) != string::npos ) {
str.replace( pos, searchString.size(), replaceString );
pos++;
}
cout << str << endl;
return 0;
}

When I'm using the MString library, it was a breeze. There's a ton of methods that makes things easy but when you need to make your program faster and leaner, you wouldn't want the extra baggage that comes with a third party library. MString is good but it's just brings with it too much things I don't need on my program.

If I have time(which most of the time I spend sleeping), I'll update the post to include a line by line explanation. It's not exactly for you but for me. It's a note to myself. :D

Ciao!

Incoming search terms:

  • string replace in c
  • string replace c
  • StringReplace c
Filed Under: Uncategorized Tagged With: , ,

How to Get Oracle DB Version

September 17, 2009 By Leave a Comment

I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows:


SELECT *
FROM product_component_version


SELECT *
FROM v$version

This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.

Incoming search terms:

  • get oracle version
  • oracle select db version
  • select dbversion
Filed Under: Oracle, PL/SQL

TAR : Archiving and Extracting Files and Folders

September 9, 2009 By Leave a Comment

Here’s the common syntax I use in archiving and extracting files or folders.

tar -xvf foo.tar

verbosely extract foo.tar


tar -xzf foo.tar.gz

extract gzipped foo.tar.gz


tar -cjf foo.tar.bz2 bar/

create bzipped tar archive of the directory bar called foo.tar.bz2


tar -xjf foo.tar.bz2 -C bar/

extract bzipped foo.tar.bz2 after changing directory to bar


tar -xzf foo.tar.gz blah.txt

extract the file blah.txt from foo.tar.gz

Incoming search terms:

  • extracting files from various folders
Filed Under: Linux

How to Find PHP.INI Path

September 5, 2009 By Leave a Comment

If you can’t find it in /etc/php.ini, then you have to use ssh and execute the command below.
php -i | grep php.ini

The command will return something like
Configuration File (php.ini) Path => /etc/php.ini

Filed Under: Linux, PHP

Rename Table in Oracle

September 2, 2009 By Leave a Comment

SYNTAX

alter table
table_name
rename to
new_table_name;

Example

alter table
employee
rename to
employee_bak;

Incoming search terms:

  • rename oracle table
Filed Under: Oracle Tagged With: , , ,

FileZilla 421 Too many connections (2) from this IP

August 19, 2009 By 5 Comments

If you encountered this error, you are not alone. I’ve encountering this on my VPS too often that I decided to use FireFTP for a while until I found out that it’s a settings issue.

Here’s what you need to do to solve this.

  1. Go to Site Manager of FileZilla
  2. Select the FTP connection where you are having problem
  3. Click on Transfer Settings tab
  4. Check Limit Number of Simultaneous Connections
  5. Set Maximum Number of Connections to 1
  6. Save and connect to your server

[Read more...]

Incoming search terms:

  • too many connections from this ip filezilla
  • filezilla error 421
  • filezilla too many connections
  • filezilla 421 connection timed out
  • filezilla server 421 connection timed out
  • filezilla server 421 error
  • filezillla server 421 connection timed out
  • ftp 421 too many connections time out
  • ftp only allows 1 connection per ip
  • how to limit connections in ftpzilla
Filed Under: Hosting Tagged With: , ,

Linux Search and Replace in File

August 11, 2009 By Leave a Comment

Here’s the syntax


sed 's/SEARCH_TEXT/REPLACE_TEXT/g' FILENAME > OUTFILE
SEARCH_TEXT is the text to search in filename
REPLACE TEXT is the string to be put in place of SEARCH_TEXT
FILENAME is the file to be searched
OUTFILE is the file where the output

Sample

sed 's/ORDER/VANTIVE_ORDER/g' kwatogtest.sql > kwatogsed.log

I keep on forgetting that syntax although it’s so simple. There might other ways of doing it but as of now, it’s the only way I know. :p

Incoming search terms:

  • glendale web design
Filed Under: Linux Tagged With: , , ,

How To Determine SQL Server Version

April 20, 2009 By Leave a Comment

In case you need to know what version of SQL Server you are currently using, you can use the queries below.

Starting at SQL Server 2000, you can use the following query.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The above query is applicable for SQL Server 2000, SQL Server 2005 and SQL Server 2008. For SQL Server 7 and SQL Server 6.5 series. The code below should be used.

SELECT @@VERSION

If you are not sure what version of SQL Server you have, use the second query. It still works as it isn’t deprecated yet. The new version is handy if you need to get the details programmatically.

Filed Under: SQL Server
« Older Posts
Newer Posts »