New Defragmentation Solution for SQL Server

The product is said to be the industry?s only SQL server defragmentation solution that simplifies the process of pinpointing fragmentation of hotspots through automation.  It gives users the power to defragment indexes automatically or when needed, thus helping to improve SQL Server?s overall performance.

Index fragmentation is one of the major causes of lethargic SQL Server performance.  By keeping indexes defragmented, SQL defrag manager v3.0 keeps SQL Server applications running at optimal performance levels.  This frees database administrators from having to waste precious time on manual defragmentation, allowing them to increase efficiency and giving them the opportunity to concentrate on other tasks.  Heather Sullivan, Idera?s Director of SQL Server Products, reiterated this notion in the official press release: "When SQL Server indexes become fragmented, database performance can quickly degrade and manually defragmenting can be extremely time consuming and tedious.  SQL defrag manager 3.0 provides DBAs with a defragmentation autopilot for the entire SQL Server enterprise."

The latest version of SQL defrag manager comes equipped with a host of features, some of which are new to the software.  One of the newest additions is index fill factor management, which helps to reduce the splitting and shifting of pages.  Darlene Stephens, a database administrator with WaterOne, commented on its useful nature: "The new index fill factor feature in SQL defrag manager has saved me time because I can quickly modify those parameters and visualize the history.  Also, the ability to view and independently reorganize or rebuild partitioned indexes in SQL defrag manager 3.0 has made it even easier to improve the performance of my SQL Servers."

Other features besides the newly added index fill factor management begin with complete, customized control over defragmentation.  SQL defrag manager?s management console provides users with a solid overview of defragmentation activity across multiple servers and databases.  Processes can be activated by and prioritized according to a certain fragmentation percentage, scan density, or index size.  Policy-based management is another highlight of the software?s feature set.     Detailed metrics, email notifications for DBAs, and extensive reporting are just a few more examples of what is included in the SQL defrag manager v3.0 package.

SQL defrag manager v3.0 is currently available through Idera?s website, www.idera.com.  The software costs $1,195 per instance and is free to Microsoft MVPs.  Idera offers a fully functional 14-day trial so users can test all of the features that SQL defrag manager v3.0 has to offer.

For more on this topic, visit http://www.marketwatch.com/story/idera-announces-sql-defrag-manager-30-2011-08-30

Novosoft?s Handy Backup Now Supports 64-bit Version of SQL Server

Novosoft, a global software development company and provider product solutions for businesses, recently announced that its Handy Backup software has been updated to offer backup, sync, and restore for 64-bit versions of Microsoft SQL Server and Microsoft Exchange.  Handy Backup is known across the industry for its power and efficiency when it comes to data backup in both home and business environments.  With the addition of 64-bit support, the software allows Novosoft to provide one of the most comprehensive and cost-friendly backup solutions around.

Handy Backup was originally developed to backup and restore 32-bit platforms.  The addition of 64-bit support comes via Handy Backup Workstation x64.  Those looking for 64-bit SQL Server support will need to have Workstation x64 installed in conjunction with the Handy Backup utility itself.  Workstation x64 comes equipped with multiple 64-bit backup plugins.  The MS SQL x64 plugin offers backup for SQL Server 32-bit and 64-bit versions of its 2000, 2005, and 2008 editions.  Other plugins that come with Workstation x64 include MS Exchange x64 and File System x64.

Novosoft pairs the workstation with local and network backup options for maximum flexibility.  The local backup of 64-bit applications requires the same-computer installation of Workstation x64 on top of Handy Backup.  Remote backup requires the installation of Workstation x64 on the remote computer.  In addition, the program must be configured to comply with the Backup Network Workstation policy.  There is no cap as to the amount of additional workstations that can be installed, and Novosoft offers premium and free support for those seeking help with the configuration process.

Alexander Prichalov, head of the Novosoft Development Department, commented on the latest Handy Backup improvements, saying: ?Bet you won?t guess five in a row hardcore up-to-date-actual data backup features which Handy Backup doesn?t have. Concerning those features which are being currently developed and will be released soon, I bet you won?t name three. That?s our key priority here at Novosoft: we make cool backup stuff and make it quickly. Thanks to that little alchemy formula, Handy Backup is now officially one of a few fully-functional software solutions to manage backup and restore of 64-bit server platforms. You have never seen a handier 64-bit MS SQL Server backup software solution.?

Novosoft?s suite of Handy Backup products is available through the official Handy Backup website, www.handybackup.net. 

For more on this topic, visit http://www.thehostingnews.com/handy-backup-enhances-ms-sql-backup-with-new-support-20149.html

blog comments powered by

Gazebo shed on saleDownload here

Read More ...

Clauses and Logical Operators for Retrieving Table Data

How to code the WHERE clause

The WHERE clause in a SELECT statement filters the rows in the base table so that only the rows you need are retrieved. In the topics that follow, you?ll learn a variety of ways to code this clause.

How to use the comparison operators

Figure 3-11 shows you how to use the comparison operators in the search condition of a WHERE clause. As you can see in the syntax summary at the top of this figure, you use a comparison operator to compare two expressions. If the result of the comparison is True, the row being tested is included in the query results.

The examples in this figure show how to use some of the comparison operators. The first WHERE clause, for example, uses the equal operator (=) to retrieve only those rows whose vendor_state column have a value of IA. Since the state code is a string literal, it must be enclosed in single quotes.

In contrast, a numeric literal like the one in the second WHERE clause isn?t enclosed in quotes. This clause uses the greater than (>) operator to retrieve only those rows that have a balance due greater than zero.

The third WHERE clause illustrates another way to retrieve all the invoices with a balance due. Like the second clause, it uses the greater than operator. Instead of comparing the balance due to a value of zero, however, it compares the invoice total to the total of the payments and credits that have been applied to the invoice.

The fourth WHERE clause illustrates how you can use comparison operators other than the equal operator with string data. In this example, the less than operator (<) is used to compare the value of the vendor_name column to a literal string that has the letter M in the first position. That will cause the query to return all vendors with names that begin with the letters A through L.

You can also use the comparison operators with date literals, as illustrated by the fifth and sixth WHERE clauses. The fifth clause will retrieve rows with invoice dates on or before May 31, 2008, and the sixth clause will retrieve rows with invoice dates on or after May 1, 2008. Like string literals, date literals must be enclosed in single quotes. In addition, you can use different formats to specify dates, as shown by the two date literals shown in this figure. You?ll learn more about the acceptable date formats and date comparisons in chapter 8.

The last WHERE clause shows how you can test for a not equal condition. To do that, you code a less than sign followed by a greater than sign. In this case, only rows with a credit total that?s not equal to zero will be retrieved.

Whenever possible, you should compare expressions that have similar data types. If you attempt to compare expressions that have different data types, Oracle may implicitly convert the data types for you. Although implicit conversions are often acceptable, they will occasionally yield unexpected results. In chapter 8, you?ll learn how to explicitly convert data types so your comparisons will always yield the results that you want.

The syntax of the WHERE clause with comparison operators

WHERE expression_1 operator expression_2
The comparison operators 

Examples of WHERE clauses that retrieve? 

Vendors located in Iowa 

[code]WHERE vendor_state = 'IA'

Invoices with a balance due (two variations)

WHERE invoice_total - payment_total - credit_total > 0
WHERE invoice_total > payment_total + credit_total

Vendors with names from A to L

WHERE vendor_name < 'M'

Invoices on or before a specified date

WHERE invoice_date <= '31-MAY-08'

Invoices on or after a specified date

WHERE invoice_date > = '01-MAY-08'

Invoices with credits that don?t equal zero

WHERE credit_total <> 0

Description

You can use a comparison operator to compare any two expressions that result in like data types. Although unlike data types may be converted to data types that can be compared, the comparison may produce unexpected results.
If the result of a comparison results in a True value, the row being tested is included in the result set. If it?s False or Unknown, the row isn?t included.
To use a string literal or a date literal in a comparison, enclose it in quotes. To use a numeric literal, enter the number without quotes.
Character comparisons are case-sensitive. ?CA? and ?Ca?, for example, are not equivalent. How to use the comparison operators

blog comments powered by

Gazebo shed on saleDownload here

Read More ...

Comparison of MyISAM and InnoDB MySQL Databases

It's not breaking news that MySQL is by far the most popular open-source RDBMS that exists today. The widespread adoption of the database server stems from a balance of robustness and solid performance; it doesn't hurt to that it also supports a variety of storage engines, which gives users the ability to pick the one that best suits their needs.

Unfortunately, not everyone using MySQL is an experienced DBA with a deep background on each storage engine supported by the server. This is especially evident in the terrain of web development, where (in many cases) the design of a project?s database schema is conducted by taking into account only the kind of data that will be persisted between HTTP requests.

The bright side is that in most use cases, selecting the appropriate storage engine for a particular project can be much simpler than one might think. If you?ve been working with MySQL on a frequent basis and have followed its evolution over time, you probably know that the whole selection process can be reduced to a few typical options: the older MyISAM system and the newer, flashier InnoDB.

That being said, in this tutorial I?ll be conducting a comparison between the main characteristics offered by MyISAM and InnoDB, in this way making it easier for you to spot their differences without having to get lost in the overwhelming contents of the MySQL manual.

MyISAM

Being the storage engine included by default with MySQL from its early days, MyISAM (short for My Indexed Sequential Access Method) is a relatively simple storage mechanism (especially when compared to more complex and sophisticated implementations, such as InnoDB and DB2) which has a fast performance, due to the fact that each record is organized first into  a sequential file, and then accessed via a set of small indexes or pointers annexed to the record in question.

It?s worth noting, however, that this simplicity and great performance comes with a hidden cost: first and foremost, MyISAM doesn?t provide support for transactional ACID operations, meaning that it?s not possible to run batches of operations in one go, or even to roll them back when something goes wrong. For obvious reasons, this can lead to potential inconsistencies in tables and affect the integrity of the stored data, thus making it necessary to tackle this and other related issues at application level.

In addition, when accessing a database, MyISAM uses an approach known as table-level blocking. Simply put, this implies that each time the server attempts to run an operation in the target table, it will be blocked during the execution process. While at first glance this operational mode seems to be quite efficient, it can quickly become a poor strategy, particularly when it?s necessary to attend to multiple clients trying to gain access to the same resource at the same time (aka concurrence issues).

Finally, there?s another big "NO" that MyISAM hides under the hood. Effectively, the mechanism doesn?t offer support for handling native foreign keys. Of course, I?m not saying that you won?t be able to create two or more tables and relate them manually at will through a few foreign keys. What I actually mean is that you can?t specify foreign keys in the corresponding table definitions and specify the constraints that they will behave in response to common cascade operations, such as updates and deletions.       

In summary: MyISAM will perform satisfactorily as long as you?re database requirements remain basic and limited to running traditional, non-transactional CRUD operations. If you need to take a step further into the realm of transactions (which goes hand in hand with the trends of modern application development), it should be discarded in favor of a more robust implementation, such as InnoDB.

InnoDB

Added to MySQL in 2005, InnoDB (whose manufacturer is the Finnish company Innobase Oy [http://www.innodb.com]) is a remarkable improvement of its predecessor MyISAM, which not only saves records following the same order of their primary keys (something that sometimes avoids an additional reordering process), but it also offers solid support for performing transactional CRUD operations (the details on how InnoDB achieves this are out of the scope of this article, but you can take a deeper look at them here [http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html]).

While the support for transactions is hard to beat when it comes to selecting the storage engine that fits your needs most efficiently, InndoDB provides a few other robust features that turn it into an even more appealing contender.

If you?re wondering what these extra features are, here?s a quick summary of them: first off, the engine implements a row-level blocking mechanism (unlike its older brother MyISAM, which blocks the entire table being accessed), meaning that the server will be able to perform concurrent operations in the same table, as long as they don?t target the same row. This is a really clever approach, which really shines on its own, especially if you have a lot of clients accessing the same target table.

Last but not least, InnoDB will let you easily specify (in the same table definition) which column(s) will be native foreign keys, as well as the behavior that they will have when updating and deleting rows in the parent table(s) (in other words, the foreign keys constraints mentioned in the preceding section).

Don?t worry - I?m not planning to cover how to define foreign keys constraints when using InnoDB; however, if you?re interested in learning more on this topic, just check out the tutorial that I wrote here (http://www.devshed.com/c/a/PHP/Updating-and-Deleting-Records-in-MySQL-Tables-with-Foreign-Key-Constraints/).

It goes without saying that MyISAM and InnoDB implement a host of additional low-level features, which are way to extensive to dicuss here. Nevertheless, if you only need a quick guide that will show you the most relevant facilities that each storage engine brings to the table, hopefully this tutorial will assist you in the analysis process.

Final Thoughts

Over the course of this article, I made a brief summary of the most relevant features offered by the popular MyISAM and InnoDB MySQL storage engines. As you saw through this quick review, the clear winner here is undoubtedly the latter, not only for being much newer than its competitor, but due to the fact that it provides a bunch of facilities that are hard to ignore when it comes to building database applications, including the support for transactions (a key one, in my humble opinion) and the implementation of a solid row-level blocking mechanism.

Naturally, if your database requirements are rather basic and you don?t need to use transactions, MyISAM is still a good option worth noting that will yield pretty satisfactory results as well. Regardless of this, InnoDB is rapidly becoming the choice of many users, because of the aforementioned benefits, which as I said before, are definitively along the lines of modern application development.    

See you in the next MySQL tutorial!

blog comments powered by

Gazebo shed on saleDownload here

Read More ...

Free Monitoring Tool for Java Apps on Heroku

The announcement came during the Salesforce.com Dreamforce ?11 conference, which brings hundreds of companies and thousands of customers, partners, and developers together to discuss cloud computing.  The timing of the announcement could not be better, as Java support on Heroku was recently made official.  New Relic?s application performance management tool offers a comprehensive solution for monitoring web applications and is used by a host of businesses, both large and small. 

The New Relic Standard edition provides 24/7 app and user monitoring and is ideal for smaller projects.  It supplies users with detailed information on database performance through various statistical tracking measures.  Customer data is retained for 1 week, and there can be unlimited users per account.  The tool also comes equipped with a JVM performance analyzer, incident and error alerting, plus much more to ensure that web application performance is optimized.

Heroku VP of Product Management Oren Teich commented on the importance of the latest move: "As more businesses look to Heroku to enable their cloud strategy, providing application performance monitoring is critical.  We are excited to announce the availability of New Relic's add-on for our tens of thousands of developers worldwide and now growing base of Java developers with New Relic's new production profiling and monitoring capabilities integrated seamlessly with Heroku."

Bill Lapcevic, New Relic?s VP of Business Development, noted the strength of the partnership between New Relic and Heroku, stating: "New Relic and Heroku have worked together to deliver the tools, resources, and skills organizations needed to rapidly and successfully deploy business-critical apps in the cloud.  The addition of New Relic's app production profiling and monitoring capabilities to Heroku's new Cedar platform underscores our continued joint commitment to providing Java developers with the same scalability, simplicity, and reliability that Ruby programmers have enjoyed for over the last two years." 

New Relic Standard edition is available for free to Heroku?s Java customers and continues to be free of charge to those running Ruby apps on the Heroku platform.  New Relic also offers its application performance management tool in a Pro edition that offers additional features beyond those found in the Standard version.  A 14-day trial can be used to experience the Pro edition?s capabilities.  A business package is available as well for clients with more than five servers.  More information on the different offerings can be found on New Relic?s website, www.newrelic.com.

Formore on this topic, visit http://www.marketwatch.com/story/new-relic-provides-developers-with-free-24x7-web-app-monitoring-and-analysis-for-java-apps-on-heroku-2011-08-30?reflink=MW_news_stmp

Extentech Releases Update for its Java Spreadsheet Solution

Extentech, an industry leader in web spreadsheet and document technology, recently upgraded its portfolio of Java components and development tools with the announcement of its release of ExtenXLS 10.1.  The release upgrades the already solid Java spreadsheet SDK to include several new and improved features.

The ExtenXLS Java spreadsheet toolkit offers a fast and reliable platform that is ideal for reading, writing, and generating Excel spreadsheets.  Beyond its excellent performance and reliability, ExtenXLS? main advantage over competitors is that it now gives users SVG charting that is compatible with web browsers such as Google Chrome, Firefox, Internet Explorer, and Safari.  SVG is an XML graphics format that eliminates the need for any browser plugins, as the most up-to-date browsers support its output.  Thanks to SVG charting, users can experience the benefits of data rich spreadsheet reporting in real time with vector graphics that are not only fast, but scalable as well.  ExtenXLS also offers VML Chart output to provide compatibility with legacy browsers. 

In addition to the SVG charting, ExtenXLS 10.1 is highlighted by several other appealing features, beginning with the introduction of the new ERF, NORMINV, NORMSINV, NORMDIST, and NORMSDIST formula functions.  Sheet protection and locking have been upgraded for increased compatibility with Excel, and charts containing spreadsheets can be exported in PDF format. 

ExtenXLS 10.1 also comes with various improvements aimed at increasing performance.  One of those improvements focuses on the realm of XLSX and OOXML fidelity, which has been increased when working with Excel 2010 and encrypted spreadsheet files.  A new workbook parser has been included as well to offer enhanced event-driven document review for sessions that are read-only.

Extentech CEO John McMahon commented on the latest ExtenXLS release, stating: ?We are continually updating ExtenXLS to provide the most functionality for developers and to ensure that it remains the top choice of enterprises that need reliable and fast spreadsheet capabilities without vendor or platform lock-in.  With our new SVG charting output, we give customers a better way to present spreadsheet data, with rich and up-to-date web-based charts. Our system renders charts very quickly, so busy users don't need to wait for their charts and spreadsheet data to display, allowing everyone to work more efficiently."

blog comments powered by
Gazebo shed on saleDownload here

Read More ...

Steve Jobs` Era at Apple Ends

The official resignation by Jobs does not come as a complete surprise, as he had recently been plagued by various health problems.  Jobs turned over Apple?s reigns to Cook in January of this year when he was forced to take an indefinite medical leave.  It was his third such leave in recent years, but he was determined to return to his position, stating, ?I love Apple so much and hope to be back as soon as I can.?

Jobs? unfortunate medical history began in August of 2004, when he had surgery to treat a rare form of pancreatic cancer.  The procedure was a success, and Jobs returned to work the next month.  He ran into problems once again in January of 2009, when he claimed that his body?s ability to absorb proteins was being hindered due to a hormonal imbalance.  Jobs had liver transplant surgery in April of that year which kept him sidelined until July.

The string of health problems appear to have been his final undoing, as Jobs proclaimed that he was no longer able to perform as CEO in a letter to the Apple?s  board members as well as the company?s vast community.  He said, ?I have always said if there ever came a day when I could no longer meet my duties and expectations as Apple's CEO, I would be the first to let you know. Unfortunately, that day has come.? 

While his CEO days may be over, Jobs expressed the desire to continue contributing to the company as chairman of the board, director, and even as an employee.  He noted that such involvement would be subject to the board?s approval.  Jobs finished his letter on a positive note, stating, ?Apple's brightest and most innovative days are ahead of it.?  Jobs? last major public appearance as CEO of Apple occurred a couple of months ago during the Worldwide Developers Conference in June.  The conference was used as a springboard for the company?s introduction of its iCloud service and iOS5. 

Tim Cook?s career with Apple began in 1998.  Cook had served as Jobs? right-hand man for a lengthy period, and his duties included figurehead appearances at product launches, as well as leading several earning calls with investors and shareholders meetings.  The 50 year old assumed the position of chief operating officer in 2004.  Cook?s new role as CEO kicks off a new era for Apple, but the company?s booming success combined with his solid experience should make it a prosperous one. 

Apple recently held the distinction of being the world?s most valuable company thanks to its $330 billion-plus market capitalization.  Apple?s line of highly-noticeable products, including the Mac, iPhone, iPad, and iPod, has created an army of loyal followers and helped it separate itself from competitors.

 

blog comments powered by
Gazebo shed on saleDownload here

Read More ...

Heroku Adds JCloud Platform Support, Java 7 News

Heroku first introduced its Celadon Cedar stack in May.  The company claimed that the stack would be able to run any language.  With the addition of Java to the mix, Heroku?s is solidified even further.  Adam Wiggins, co-founder of Heroku, described the latest move in a recent blog post, noting: ?Java is, by many measures, the world's most popular programming language.  In addition to its large and diverse developer base, it offers a huge ecosystem of libraries and tools, an extremely well-tuned VM for fast and reliable runtime performance, and an accessible C-like syntax.?

Bill Lapcevic, VP of business development at New Relic, claimed that Heroku?s support of Java would open new doors for the platform.  He said: ?New Relic and Heroku have been long-standing partners and it's great to see them expand their multi-language platform with support for Java.  This puts Heroku in a key position to serve Salesforce.com's developer audience as their platforms are largely Java- based. It also puts Heroku on the same playing field with existing solutions like Amazon Elastic Beanstalk. I expect there will be a significant uptake from enterprise developers looking for easy deployment and multi-language support for their critical apps."

Besides its popularity, Wiggins noted various other reasons for supporting Java.  He applauded the Java Virtual Machine (JVM) for its reliable memory footprint and impressive performance, calling it one of the best runtime VMs around.  Wiggins cited Java?s massive estimated population of six million developers as another reason to support the language, and said that it?s the industry?s best developed programming language for building server-side applications.  He cited the JVM runtime environment?s wide availability across platforms as an appealing factor as well.  ?Supporting Java is what's best for the large world of Java developers; it's what's best for developers who want to use other JVM languages; and it's even good for users of other languages, who will benefit indirectly from the learning their community may gain from contact with Java,? Wiggins said.

Wiggins also promised that Heroku would help to clean up certain problems associated with Java 2 Platform Enterprise Edition (J2EE), noting that the platform offers ?the capabilities promised by J2EE application containers for managing your app include deployment, restart, logging, service binding (config), and clustering (horizontal scaling.  Running your Java app on Heroku, you achieve these ends via the platform instead.?  He added, ?Using Heroku's platform to run Java apps finally solves the impedance mismatch between application containers designed for traditional software distribution, and the modern world of software-as-a-service.?

As for what to expect from Heroku in the future, Wiggins said, ?Future language packs will span the gamut from venerable (like Java) to cutting-edge (like Clojure and Node.js) to squarely in-between (like Ruby). Our desire is to be as inclusive as possible. Choice of language is up to the developer.?
For more on this topic, visit http://www.eweek.com/c/a/Application-Development/Heroku-to-Java-Welcome-to-Our-Cloud-832656/

A Glimpse into Java 7?s Improvements

The recent release of Java Platform Standard Edition 7 (Java SE 7) was the first major Java release in over five years.  It also marked the first Java introduction under the Oracle umbrella.  While Java SE 7 did have a bit of a rough start due to some serious bugs, it has been fixed, and it offers developers a host of new features that are worthy of praise.

Although Java SE 7 comes with various upgrades, some believe that its timing is its most important feature.  ?The main thing about Java SE is that it shipped. The inertia of five years without a release had to be overcome,? said Al Hilwa, an analyst at IDC.  Some features, such as language and VM support for modular programming and the ability to add closures for multi-core programming did not make the deadline for Java 7, meaning users will have to wait for Java 8?s release in 2012 to see them.

As for what?s available now, Java 7?s new and improved feature set begins with support for dynamic languages.  According to Hilwa, such support will promote the expansion of the Java ecosystem.  Thanks to a multi-core ready API, developers can decompose problems into tasks for parallel execution via multiple processor cores more efficiently.  Another Java 7 improvement serves to help increase developer productivity.  With Project Coin, coding is reduced and syntax is clarified through various language changes. 

Java founder James Gosling cited the new NIO2 file-system capabilities as one of his favorite features of Java 7.  NIO2 supplies a solid interface for working with file systems that offers detailed error information and broad access to file attributes.  Other Java 7 highlights of note include the new Gervill sound engine and the XRender pipeline for 2D graphics rendering.

blog comments powered by
Gazebo shed on saleDownload here

Read More ...

Using the IN and BETWEEN Operators on Tables

How to use the IN operator

Figure 3-13 shows how to code a WHERE clause that uses the IN operator. When you use this operator, the value of the test expression is compared with the list of expressions in the IN phrase. If the test expression is equal to one of the expressions in the list, the row is included in the query results. This is illustrated by the first example in this figure, which will return all rows whose terms_id column is equal to 1, 3, or 4.

You can also use the NOT operator with the IN phrase to test for a value that?s not in a list of expressions. This is illustrated by the second example in this figure. In this case, only those vendors who are not in California, Nevada, or Oregon are retrieved.

If you look at the syntax of the IN phrase shown at the top of this figure, you?ll see that you can code a subquery in place of a list of expressions. Subqueries are a powerful tool that you?ll learn about in chapter 6. For now, though, you should know that a subquery is simply a SELECT statement within another statement. In the third example in this figure, for instance, a subquery is used to return a list of vendor_id values for vendors who have invoices dated May 1, 2008. Then, the WHERE clause retrieves a vendor row only if the vendor is in that list. Note that for this to work, the subquery must return a single column, in this case, vendor_id.

The syntax of the WHERE clause with the IN operator

WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]...})

Examples of the IN operator

The IN operator with a list of numeric literals

WHERE terms_id IN (1, 3, 4)

The IN operator preceded by NOT

WHERE vendor_state NOT IN ('CA', 'NV', 'OR')

The IN operator with a subquery

WHERE vendor_id IN
(SELECT vendor_id
FROM invoices
WHERE invoice_date = '01-MAY-2008')

Description

You can use the IN operator to test whether an expression is equal to a value in a list of expressions. Each of the expressions in the list must evaluate to the same type of data as the test expression.
The list of expressions can be coded in any order without affecting the order of the rows in the result set.
You can use the NOT operator to test for an expression that?s not in the list of expressions.
You can also compare the test expression to the items in a list returned by a subquery as illustrated by the third example above. You?ll learn more about coding subqueries in chapter 6. --------------------------------------------Figure 3-13 How to use the IN operator

blog comments powered by

Gazebo shed on saleDownload here

Read More ...