The Experts below are selected from a list of 135 Experts worldwide ranked by ideXlab platform
Joe Celko - One of the best experts on this subject based on the ideXlab platform.
-
Case Expression Family
Joe Celko's SQL for Smarties, 2015Co-Authors: Joe CelkoAbstract:SQL is declarative, so we do not have the concept of flow control in procedural languages. Instead, we use Expressions that come from primitive recursive function theory to select a result from among possible values.
-
18 – Case Expressions
Joe Celko's SQL for Smarties, 2011Co-Authors: Joe CelkoAbstract:Publisher Summary Case Expressions are regarded as the most useful addition in SQL-92. The Case Expression allows the programmer to pick a value based on a logical Expression in his code. The searched Case Expression is probably the most used version of the Expression. Writing an explicit ELSE clause is always recommended, so that one can change it later when he finds a value to return. The is defined as a searched Case Expression in which all the WHEN clauses are made into equality comparisons against the . A Case Expression is very useful with a GROUP BY query. As aggregate functions remove NULLs before they perform their operations, the order of execution might be different in the ELSE 0 and the ELSE NULL versions. Subquery Expressions are SELECT statements inside of parentheses and the four types of subquery Expressions include tabular, columnar, row, and scalar subquery Expressions. The tabular or table subquery returns a table as a result, whereas the columnar subquery returns a table with a single column in it. The row subquery returns a single row that provides the basis for the singleton SELECT statement used in the embedded SQL.
-
LIKE and SIMILAR TO Predicates
Joe Celko's SQL for Smarties, 2011Co-Authors: Joe CelkoAbstract:This chapter focuses on LIKE and SIMILAR TO predicates. The “_” character represents a single arbitrary character; the “%” character represents an arbitrary substring, possibly of length zero. Any other character in the represents that character itself. The is used in the to specify that the character that follows it is to be interpreted as a literal rather than a wildcard. The “_” character tests much faster than the “%” character as the parser that compares a string to the pattern needs only one operation to match an underscore before it can move to the next character, but has to do some look-ahead parsing to resolve a percentage sign. The NULL can be the escape character, pattern, or match value and a NULL in the predicate returns an UNKNOWN result. The Case Expression in Standard SQL lets the programmer use the LIKE predicate in some interesting ways. A Case Expression executes the WHEN clauses in order of their appearance and adjusts the pattern within the LIKE predicate. If the SQL has the ability to define a function in an index, one can reverse or rearrange the string to give faster access, where the query must explicitly use the same function as the index. An alternative to this is to store the rearranged value in the base table and show the actual value in a view. When the view is invoked, the rearranged value is used for the query without the users knowing it.
-
puzzle 45 pepperoni pizza
Joe Celko's SQL Puzzles and Answers (Second Edition), 2006Co-Authors: Joe CelkoAbstract:Publisher Summary This chapter describes how structured query language (SQL) can be used to create report of old billings. It illustrates the example of a pizza joint, which makes use of SQL to get a sum of amounts paid by each member within an age range. The ranges are 0 to 30 days old, 31 to 60 days old, 61 to 90 days old, and everything over 90 days old. This is called an aging report on account receivables. In this report, an age range can be obtained with UNION ALL operators or by using Case Expression.
-
Puzzle 45 – Pepperoni pizza
Joe Celko's SQL Puzzles and Answers, 2006Co-Authors: Joe CelkoAbstract:Publisher Summary This chapter describes how structured query language (SQL) can be used to create report of old billings. It illustrates the example of a pizza joint, which makes use of SQL to get a sum of amounts paid by each member within an age range. The ranges are 0 to 30 days old, 31 to 60 days old, 61 to 90 days old, and everything over 90 days old. This is called an aging report on account receivables. In this report, an age range can be obtained with UNION ALL operators or by using Case Expression.
James M Slauch - One of the best experts on this subject based on the ideXlab platform.
-
Construction of targeted single copy lac fusions using lambda Red and FLP-mediated site-specific recombination in bacteria.
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the lambda Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of beta-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.
-
construction of targeted single copy lac fusions using λ red and flp mediated site specific recombination in bacteria
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the λ Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of β-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.
Craig D Ellermeier - One of the best experts on this subject based on the ideXlab platform.
-
Construction of targeted single copy lac fusions using lambda Red and FLP-mediated site-specific recombination in bacteria.
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the lambda Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of beta-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.
-
construction of targeted single copy lac fusions using λ red and flp mediated site specific recombination in bacteria
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the λ Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of β-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.
Calisto Zuzarte - One of the best experts on this subject based on the ideXlab platform.
-
Queries with Case Expressions
Journal of Intelligent Information Systems, 2010Co-Authors: Jarek Gryz, Qiong Wang, Xiaoyan Qian, Calisto ZuzarteAbstract:In recent years more and more queries are generated automatically by query managers/builders with end-users providing only specific parameters through GUIs. Queries generated automatically can be quite different from queries written by humans. In particular, they contain non-declarative features, most notorious of which is the Case Expression. Current query optimizers are often ill-prepared for the new types of queries as they do not deal well with procedural ‘insertions’. In this paper, we discuss the inefficiencies of Case Expressions and present several new optimization techniques to address them. We also describe experimental evaluation of the prototype implemented in DB2 UDB V8.2.
-
ISMIS - SQL queries with Case Expressions
Lecture Notes in Computer Science, 1Co-Authors: Jarek Gryz, Qiong Wang, Xiaoyan Qian, Calisto ZuzarteAbstract:In recent years more and more queries are generated automatically by query managers/builders with end-users providing only specific parameters through GUIs.Queries generated automatically can be quite different fromqueries written by humans. In particular, they contain non-declarative features, most notorious of which is the Case Expression. Current query optimizers are often ill-prepared for the new types of queries as they do not deal well with procedural 'insertions'. In this paper, we discuss the inefficiencies of Case Expressions and present several new optimization techniques to address them. We also describe experimental evaluation of the prototype implemented in DB2 UDB V8.2.
Anuradha Janakiraman - One of the best experts on this subject based on the ideXlab platform.
-
Construction of targeted single copy lac fusions using lambda Red and FLP-mediated site-specific recombination in bacteria.
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the lambda Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of beta-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.
-
construction of targeted single copy lac fusions using λ red and flp mediated site specific recombination in bacteria
Gene, 2002Co-Authors: Craig D Ellermeier, Anuradha Janakiraman, James M SlauchAbstract:A simple method for the construction of targeted transcriptional and translational fusions to the lac operon using FLP mediated site-specific recombination is described. Conditional plasmids containing promoterless lacZY genes and the FLP recognition target (FRT) site in both orientations were constructed for generating transcriptional fusions. Similarly, a plasmid used to create translational fusions was constructed in which the endogenous translational start of lacZ has been removed. These plasmids can be transformed into strains containing a single FRT site, which was previously integrated downstream of the promoter of interest using the λ Red recombination method. The FLP protein produced from a helper plasmid that contains a conditional origin of replication promotes site-specific recombination between the FRT sites, resulting in an integrated lac fusion to the gene of interest. Transcriptional fusions to the Salmonella typhimurium genes sodCII and sitA were constructed using this method and shown to respond appropriately to mutations in the respective regulatory genes, rpoS and fur. Translational fusions were also constructed using this method. In this Case, Expression of β-galactosidase was dependent on translation of the target protein. Given that the FLP recombinase does not require host factors for function and that this method requires no molecular cloning, this method should be applicable for the analysis of gene Expression in a variety of organisms.