SQL TUTORIALS

SQL, or Structured Query Language, is a programming language designed to help you manage data held in a relational database management system (RDBMS).

SQL TUTORIALS

SQL, or Structured Query Language, is a programming language designed to help you manage data held in a relational database management system (RDBMS).

SQL Tutorials

SQL, or Structured Query Language, is a programming language designed to help you manage data held in a...

Understanding SQL

You probably noticed that many databases management systems include the acronym SQL...

Case Statement

See an example demonstrating the use of the 'Case' statement

Text / String Manipulation

SQL can be used to manipulate text, also called strings. Following is a list of functions that can...

Select Statement

The Select Statement is used to extract or query data from a database and can be broken down into the following...

Embedded Select Statement

SQL is a very flexible language that allows you different ways of extracting data.

Where Clause

The where clause is the part of the select statement that is used to filter the result set.

Joining Tables

The information in a database is typically structured in a way that prevents duplication of information.

Union

You can use a union to add constant values into a result set or to combine different, un-linked tables...

Aggregate Functions

Aggregate functions operate on groups of records, rather than on individual records or variables.

Nested Queries

You can prevent the repetition of lengthy subqueries by using nesting to select values on one level and then...

Numeric Calculations

SQL can be used as a numeric calculator.

Date Calculations

SQL can be used as a date calculator.

NULL Values

When a field is empty, it has a NULL value - a nothing value. This is very important to understand when...

User-Defined Fields

All the User-defined field values are stored in one table in QuickEasy, namely FIELDVALUES. Each record...

Deleting Expired and Orphanages Pages

Here are the scripts you need to...

SQL Examples

Click here to view examples...

SQL Tutorials

SQL, or Structured Query Language, is a programming language designed to help you manage data held in a relational database management system (RDBMS). SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987. Since then, the standard has been revised to include a larger set of features. Despite the existence of such standards, though, most SQL code is not completely portable among different database systems.

QuickEasy uses the Firebird relational database, so naturally, this guide will adhere to the standards that work in Firebird and will focus on Data Manipulation Language (DML), the scope of the SQL language which is used to insert, update, delete and query a database.

For more advanced study, please follow this link to the online Firebird Reference Manuals.

FlameRobin is a free Data Administration tool that can be used to access the Firebird database directly using SQL.

How tables are linked

A Database is a collection of tables and each table contains specific Data. eg. you might have a table for Customers, Suppliers, Transaction Headers, Items, etc.

The Tables in the database are linked using Primary Keys and Foreign Keys. Most of the time a one-to-many relationship will exist between linked tables. This is also referred to as a Master-Detail relationship or a Parent-Child relationship. In this case, the Foreign Key field in the child/detail table will link to the Primary Key field in the Master/Parent table. Below is an explanation of how Primary Keys and Foreign Keys work.

Primary Keys - Every table must have at least one field that uniquely identifies each record in the table. This is called the Primary Key (PK) and in QuickEasy an integer field is used to serve as the Primary Key in each table.
Foreign Keys - Foreign Keys are used in the Child/Detail tables to link to the Primary Key in the Parent/Master tables and they need to be of the same data type as the Primary Keys to which they are linked.

Primary Key / Foreign Key relationships are only used where a relationship is mandatory. For instance, a Transaction Item can only exist if it is linked to a Transaction Header and if the Transaction Header is deleted, the Items on the Transaction must also be deleted.

Data Types

Different Data Types are used to store different types of information, eg, Text, Numbers, Dates, Images, etc. Each field in a table is therefore linked to a Data Type. Following is an overview of the data types that are most commonly used in QuickEasy.

Text Data Types

VARCHAR(XX) - Used to store text, the 'xx' determines the maximum number of characters that may be stored in the field. Typically used for Name and Description fields
BLOB SUB_TYPE TEXT - Used to store text, but there is no limit to how many characters may be stored in the field - typically used for 'Note' fields.

Numerical Data Types

INTEGER - Used to store numeric values that don't have decimal values. In QuickEasy they are mostly used for Primary and Foreign Keys, Settings and True/False fields.
FLOAT - Used to store numerical values with decimals. Also called a floating-point data type. Typically used to store quantities and other numerical values that might be used in calculations, eg. an Estimate quantity or a Stock quantity.
NUMERIC(18,X) - Used to store currency values. The 'X' determines the number of decimals, usually 2 or 4.

Date and Time Data Types

DATE - Only stores a date. Used for transaction dates.
TIME - Only stores a time. This would typically be used in settings, eg. The Start and End times of a working day or shift.
TIMESTAMP - Stores a date and a time value. Typically used for due dates, timekeeping, audits, etc. where the time of the day is as important as the day itself.

Images and Files

BLOB SUB_TYPE 0 - This datatype can typically store any file that you can save on your hard-drive, including image files.

Understanding SQL

NOTE: Although very powerful, SQL can damage a database if used incorrectly, so please feel free to practice these SQL scripts but ON THE DEMO DATABASE.

You probably noticed that many databases management systems include the acronym SQL (often pronounced “sequel”). This is a term that arises very often when discussing databases. It stands for Structured Query Language and is a language that all relational databases understand. A “database query” is something that a user asks the database, and SQL is the language that the query is written in.

This is how you would create a “clients” table in a database:

CREATE TABLE MYCLIENTS ( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);

This creates a table called MYCLIENTS with five columns. The IDNUMBER column is the table’s PRIMARY KEY, which means that it is used to uniquely identify the row and thus has to be unique. In other words, this database will not let you assign two clients the same ID number which makes sense if you think about it. Two different people can't have the same ID number in real life. INT means that the IDNMUBER is an integer (a number without a decimal point) and TEXT means that the name column contains text (you’d probably use something else in a real situation as TEXT is relatively inefficient). SQL keywords have been written in bold. You can't use these words to name things in a database since the SQL interpreter (the program that talks to the database) uses them to issue commands.

Note the semi-colon (;) at the end of the SQL statement. This is like a full stop at the end of an English sentence. It lets the interpreter know that we're finished with the statement. The formatting of the text in the statement isn't important and can be written in any fashion that seems most clear to you. For example, the same statement could be written like this:

CREATE TABLE MYCLIENTS 

( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);

Or even:

CREATE TABLE MYCLIENTS 

( IDNUMBER INT PRIMARY KEY, 

NAME TEXT, 

CITY TEXT, 

TELEPHONE TEXT,

BUDGET INT );

Just remember that you may not be the only person reading the statement, so the neater you keep it, the less the confusion in the long run.

You can then use an SQL command like this to insert data into your new table:

INSERT INTO MYCLIENTS VALUES (100, 'Smiths Records', 'Cape Town', '020 8888 2222', 50000);

This adds a client with a client ID number of 100, some contact details and a budget. Notice that we always use single quotes to indicate a text (also called a string in database lingo). Once you have added some data to your table using similar statements, you can run searches using SQL commands like this:

SELECT IDNUMBER, NAME FROM MYCLIENTS WHERE CITY='Cape Town';

This will retrieve all clients' ID numbers and names located in Cape Town. If you want to get all the fields instead of just the ID number and name you can use an Asterix (*) instead of typing all the field names like so:

SELECT * FROM MYCLIENTS WHERE CITY='Cape Town';

Adapted from Introduction to Databases

Further resources:

sqlcourse.com

www.sql-tutorial.net

www.w3schools.com

wikipedia.org

Case Statement

Below is an example demonstrating the use of the 'Case' statement

select
C.CCENTREID,
C.NAME as Cost_Centre,
sum(SC.ESTTIME) as "CM_Hrs",
sum(SC.SUBTOTALCOST) as "CM_Amount",

case
when (C.NAME = 'Parva') then (sum(SC.QTY)*2)
when (C.NAME = 'GTO 46') then (sum(SC.QTY)/2)
else sum(SC.QTY)
end

from SOCOSTS SC
left join CCENTRE C on SC.CCID = C.CCENTREID
left join SOMAIN S on SC.SOID = S.SOID
where S.INVDATE between :DATESTART and :DATEEND
and SC.CCID > 0
and C.COSTTYPE = 'Printing'

group by C.CCENTREID, C.NAME, C.COSTTYPE
order by C.NAME

Text / String Manipulation

SQL can be used to manipulate text, also called strings. Following is a list of functions that can be used to manipulate text. (Click on the function to open the Firebird Reference Manual)

ascii_char() - Returns the ASCII character corresponding to the number passed in the argument.
coalesce() - The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument. If all the arguments evaluate to NULL, the result is NULL.
iif() - IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.
left() - Returns the leftmost part of the argument string. The number of characters is given in the second argument.
lower() - Returns the lower-case equivalent of the input string. The exact result depends on the character set.
lpad() - Left-pads a string with spaces or with a user-supplied string until a given length is reached.
position() - Returns the (1-based) position of the first occurrence of a substring in a host string.
replace() - Replaces all occurrences of a substring in a string.
right() - Returns the rightmost part of the argument string. The number of characters is given in the second argument.
rpad() - Right-pads a string with spaces or with a user-supplied string until a given length is reached.
substring() - Returns a string's substring starting at the given position, either to the end of the string or with a given length.
trim() - Removes leading and/or trailing spaces (or optionally other strings) from the input string.
upper() - Returns the upper-case equivalent of the input string. The exact result depends on the character set.

Using Firebird as a Calculator

You do not need to use tables and fields in order to perform calculations in Firebird. You can run a calculation using the select [CALCULATION] from RDB$DATABASE format as in the example below.

select                     
                            
upper('i love quickeasy')  
                            
from RDB$DATABASE

Notes

In Firebird SQL, text is delimited using single quotes - eg. 'this is text'
Strings can be joined (concatenated) using ||. eg. 'firstname'||', '||'surname'
A line break (carriage return) can be inserted into a text string using: ascii_char(13)||ascii_char(10)
If there is a null value in the calculation/concatenation, the result will be null.

Example - Creating an error message based on Transaction values

select
case
  when ((ERROR1 is null) and (ERROR2 is null)) then 'Everything OK'
  when ((ERROR1 is not null) and (ERROR2 is null)) then ERROR1
  when ((ERROR1 is null) and (ERROR2 is not null)) then ERROR2
  when ((ERROR1 is not null) and (ERROR2 is not null)) then ERROR1||ascii_char(13)||ascii_char(10)||ERROR2
end as ERRORMESSAGE
from
    (select
    cast(iif(COMPANYID is null, 'Customer not selected', null) as blob) as ERROR1,
    cast(iif(CONTACTID is null, 'Contact not selected', null) as blob) as ERROR2
    from
        (select
        L.TRANSID,
        L.DOCID,
        L.TRANSDATE,
        L.COMPANYID,
        L.CONTACTID
        from LEDGER L
        where L.TRANSID = 99)
    )

Select Statement

The Select Statement is used to extract or query data from a database and can be broken down into the following core parts:

select [FIELDNAME1, FIELDNAME1, ETC.] or [*= All Fields]
from [TABLENAME1]
join [TABLENAME] on [CONDITIONS]
where [CONDITIONS]
order by [FIELDNAME1, FIELDNAME2, etc.]

Eg. The select statement to return all the Customers in the QuickEasy database, starting with 'A' and sorted by company name, could look like this:

select
CUSID,
COMPANY,
TEL,
EMAIL
from CUSMAIN
where COMPANY starting with 'A'
order by COMPANY

Formatting

Spaces and line breaks are ignored, so leaving blank lines between the different parts of your select statement can make your SQL much more readable.

Another way to make your SQL more readable is the use of UPPER and lowercase. In this tutorial, we will be using UPPERCASE for all the field and table names and lowercase for the rest of the statement.

Comments

Comments can be used to explain how your queries work and to highlight where variables might need to be changed. This is especially useful when porting queries across different databases and when other persons need to support your clients. There are two types of comments that can be used:

Line comments -- comment

-- Comments the rest of the line

Block comments /* comment */

/* Block comments can stretch over multiple lines. They are ideal for creating a detailed description of how a query works. 
They are also useful for commenting out blocks of a query that might be returning errors*/

Embedded Select Statement

SQL is a very flexible language that allows you different ways of extracting data. The key is to start small and systematically build your query up. Have a look at the following example...

If you want to create a report grouped by say Cost Centres, always start with a basic Cost Centre select - this example will select all Production Cost Centres - always use Aliases, ‘C’ for CCENTRE in this example :

select 
C.CCENTREID,
C.NAME
from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Now you can add any embedded select statement inside this statement - in the example below I’ve inserted a sub-select that will select all the Costs for each Cost Centre:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC
where SC.CCID = C.CCENTREID
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Let’s take it a step further and set a Date Parameter for the Cost using the :DATESTART and :DATEEND parameters which will enable the Date filter controls - the Date sits in the SOMAIN (Sales Order Header) table so we have to join this table:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Next, we can filter out Cancelled Sales Orders:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Once the Sub-select is working to your satisfaction, you can easily copy it to say select the Estimated Time for each Cost Centre using the same Date parameters, etc:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST,

(select sum(SC.ESTTIME) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as TIME

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

From here it’s a simple procedure to add different Date parameters to select say the Cost from 12 months ago:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART1 and :DATEEND1
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST_THISMONTH,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART13 and :DATEEND13
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST_THISMONTHLASTYEAR,

(select sum(SC.ESTTIME) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as TIME

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Where Clause

The where clause is the part of the select statement that is used to filter the result set. The following conditional operators can be used to compare values in the where clause.

= Equal to

<> Not equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

between ... and ... >= and <= the values before and after and respectively

in (1,2,3) Equal to one of the values in the brackets

not in (1,2,3) Not equal to one of the values in the brackets

is null field is empty

is not null field is not empty

A where clause can contain multiple conditions that are grouped using and and or. Care must be taken to group the conditions correctly using round brackets ().

Joining Tables

The information in a database is typically structured in a way that prevents duplication of information.

For example, customer information is only stored in one table and then linked to transactions on the Primary Key. So if you want to create a query that displays transaction detail, you would need to join the Customer and Transaction table, because the transaction table does not contain any customer information, it only contains a link to the Customer table.

There are many options for joining tables in SQL, but you need only consider two options which generally provide the functionality to extract just about any information from QuickEasy:

join - This is used when there is a guaranteed link to the table that is being joined. The Child record must have a corresponding Master record. eg. joining a Transaction Header to a Transaction Item - Items can't exist without a Header. A left join would also work in this scenario, but it would be a lot slower in a large database.
left join - This is used when there MAY be a link to the table that is being joined. eg. when joining Rep details to a Transaction - some Transactions may not be linked to a Rep. If a join was used, the Transactions which are not linked to Reps will not be displayed.

When joining tables, we need to consider the order in which they are joined. By limiting ourselves to the join and left join options, we need to always start with the detail table when joining tables that have a Master-Detail relationship. For instance, if we wanted to show data from the Customer, Transaction Header as well as the Transaction Items, we would start with the Transaction Items, join the Transaction Header and then join the Customer table.

Aliases

When multiple tables are joined into a query, you need to identify the table to which each field in the query is linked. eg.

select
VATITEMS.*,
LEDGER.*,
CUSMAIN.*
from VATITEMS
join LEDGER on VATITEMS.TRANSID = LEDGER.TRANSID
join CUSMAIN on LEDGER.COMPANYID = CUSMAIN.CUSID

You can also do this by assigning an alias to each table which can in turn be used to identify which tables the fields are linked to. The above example would then look like this:

select
V.*,
L.*,
C.*
from VATITEMS V
join LEDGER L on V.TRANSID = L.TRANSID
join CUSMAIN C on L.COMPANYID = C.CUSID

Conditions

When joining tables in a Query, you also need to specify the joining conditions. ie. Which fields in each table will create the link. In most cases, you simply specify the fields that create the join. In certain cases, there may be additional conditions that must be added. eg. The query to join the selected Estimate quantity to an Estimate item.

select
*
from VATITEMS V
left join QUOTEQTY Q on V.VATITEMID = Q.VATITEMID and Q.STATUSID = 1

Union

You can use a union to add constant values into a result set or to combine different, un-linked tables into one result set.

Notes

The fields in each union must be identical - field names are taken from the first query in the union.

A Query with unions can be ordered by adding an 'order by' clause at the end of the query using the format: order by 1(where 1 = column number)

The following example illustrates how a union is used to add an 'All' option to a list of Item Categories.

select 
-1 as ID,
'All' as CATEGORY
from RDB$DATABASE

union

select 
MATCATID,
NAME 
from MATCAT

order by 2

The following example illustrates how a Transaction's Materials and Services (Costs) can be selected into one result set.

--Materials
select 
M.QUOTEOUTID as ID,
'Material' as COSTTYPE,
M.DESCRIPTION,
M.QTY,
M.TOTAL 

from QUOTEOUTSOURCE M
join VATITEMS I on M.VATITEMID = I.VATITEMID
join LEDGER L on I.TRANSID = L.TRANSID 

where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations
and L.DOCID = 18 -- Transaction No.

union

--Services
select 
S.QUOTEFINID,
'Service',
S.DESCRIPTION,
S.QTY,
S.TOTAL

from QUOTEFIN S
join VATITEMS I on S.VATITEMID = I.VATITEMID 
join LEDGER L on I.TRANSID = L.TRANSID 

where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations
and L.DOCID = 18 -- Transaction No.

order by 1,2

Aggregate Functions

Aggregate functions operate on groups of records, rather than on individual records or variables. They are often used in combination with a GROUP BY clause.

The aggregate functions don't handle NULL in the same way as ordinary functions and operators. Instead of returning NULL as soon as a NULL operand is encountered, they only take non-NULL fields into consideration while computing the outcome.

Firebird aggregate functions

list() - returns a string consisting of the non-NULL argument values in the group
max() - returns the maximum argument value in the group. If the argument is a string, this is the value that comes last when the active collation is applied.
min() - returns the minimum argument value in the group. If the argument is a string, this is the value that comes first when the active collation is applied.
sum() - returns the sum of the argument value in the group.
ave() - returns the average of the argument value in the group
count() - returns the count of records in the group

Nested Queries

You can prevent the repetition of lengthy subqueries by using nesting to select values on one level and then calculate them in the next level.

The following illustrates how a query is used to select the Start and End days of the current month, and then these values can in turn be used by another select statement.

select 

(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 1 --ENTRYTYPEID for QUOTES
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as QUOTES,
    
(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 2 --ENTRYTYPEID for SALES ORDERS
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as ORDERS,
    
(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 6 --ENTRYTYPEID for INVOICES
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as INVOICES

from 
    (
    select 

    (current_date - extract(day from current_date) + 1) as STARTOFTHEMONTH,

    current_date - extract(day from current_date) + 32 - extract(day from 
        (current_date - extract(day from current_date) + 32)) as ENDOFTHEMONTH 

    from RDB$DATABASE
    )

The following example illustrates how to first extract the user-defined field values so that you can calculate them in the next level.

select 
MATUNITID,
DESCRIPTION,
CODE,
DEPTH,
WIDTH,
GSM,
(DEPTH/1000*WIDTH/1000) as AREA,
(cast(DEPTH as float)/1000*cast(WIDTH as float)/1000*cast(GSM as float)/1000) as UNITKG,
maxvalue(DEPTH,WIDTH) as LONGSIDE

from
    (
    select 
    MU.MATUNITID,
    MU.DESCRIPTION,
    MU.CODE,

    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer))
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'DEPTH') as DEPTH,
      
    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) 
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'WIDTH') as WIDTH,
      
    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) 
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'GSM') as GSM
      
    from MATUNIT MU 
    join MATCAT MC on MU.MATCATID = MC.MATCATID 
    where MU.MATCATID = 10 --Paper Category
    )

The following example illustrates how to select a distinct list of Paper names, excluding the sheet size.

select
distinct(NAME||', '||COLOUR||', '||GSM||'gsm') as DESCRIPTION,
min(MATUNITID) as ID
from
    (
    select 
    MU.MATUNITID,
    (select F.FIELDVALUE from FIELDVALUES F
     where F.RECORDID = MU.MATUNITID
     and F.MODULEID = 3004
     and F.NAME = 'NAME') as NAME,

    (select F.FIELDVALUE from FIELDVALUES F
    where F.RECORDID = MU.MATUNITID
    and F.MODULEID = 3004
    and F.NAME = 'COLOUR') as COLOUR,

    (select F.FIELDVALUE from FIELDVALUES F
    where F.RECORDID = MU.MATUNITID
    and F.MODULEID = 3004
    and F.NAME = 'GSM') as GSM
 
    from MATUNIT MU
    where MU.MATCATID = 10
    )
group by DESCRIPTION
order by DESCRIPTION

Numeric Calculations

SQL can be used as a numeric calculator.

Following is a list of commonly used functions that can be used in numeric calculations (Click on a function to open the Firebird Reference Manual):

cast() - CAST converts an expression to the desired datatype or domain. If the conversion is not possible, an error is raised.
ceiling() - Returns the smallest whole number greater than or equal to the argument.
floor() - Returns the largest whole number smaller than or equal to the argument.
iif() - Takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.
maxvalue() - Returns the maximum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.
minvalue() - Returns the minimum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.
pi() - Returns an approximation of the value of pi.
round() - Rounds a number to the nearest integer. If the fractional part is exactly 0.5, rounding is upward for positive numbers and downward for negative numbers. With the optional scale argument, the number can be rounded to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.

Using Firebird as a Calculator

You do not need to use tables and fields in order to perform calculations in Firebird. You can run a calculation using the select [CALCULATION] from RDB$DATABASE format as in the example below.

select                                     
(2 + 3) * 6
from RDB$DATABASE

Notes

If a null value is passed into a calculation, the result will always be null.

The result of a calculation will be of the same data type as the values that were passed into the calculation - eg. 6/4 = 1 and cast(6 as float) / cast(4 as float) = 1.5

Date Calculations

You can use SQL to calculate on dates.

Following is a list of functions that can be used to manipulate dates in Firebird. (Click on the functions to open the Firebird reference manual)

dateadd() - Adds the specified number of years, months, days, hours, minutes, seconds or milliseconds to a date/time value
datediff() - Returns the number of years, months, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.
extract() - Extracts and returns an element from a DATE, TIME or TIMESTAMP expression

Notes

Dates and times can be extracted from the server using

Date - select CURRENT_DATE from RDB$DATABASE
Time - select CURRENT_TIME from RDB$DATABASE
Timestamp - select CURRENT_TIMESTAMP from RDB$DATABASE

A constant date can be added to SQL in the following formats:

'm/d/yyyy' - eg. '9/30/2015'
'm.d.yyyy' - eg. '9.30.2015'
'd mmm yyyy' - eg. '30 Sep 2015'

Calculating the Start and End date of the current month.

select
(CURRENT_DATE - extract(day from CURRENT_DATE) + 1) as STARTOFTHEMONTH,
CURRENT_DATE - extract(day from CURRENT_DATE) + 32 - extract(day from
  (CURRENT_DATE - extract(day from CURRENT_DATE) + 32)) as ENDOFTHEMONTH
from RDB$DATABASE

Extracting a three-month summary based on the current date.

select
MON,
YEA,
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 1
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as QUOTES,
  
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 2
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as ORDERS,
  
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 6
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as INVOICES
  
from
 
    (select
    extract(month from CURRENT_DATE) as MON,
    extract(year from CURRENT_DATE) as YEA
    from RDB$DATABASE
    union
    select
    extract(month from dateadd(-1 month to CURRENT_DATE)) as MON,
    extract(year from dateadd(-1 month to CURRENT_DATE)) as YEA
    from RDB$DATABASE
    union
    select
    extract(month from dateadd(-2 month to CURRENT_DATE)) as MON,
    extract(year from dateadd(-2 month to CURRENT_DATE)) as YEA
    from RDB$DATABASE
    )

NULL Values

When a field is empty, it has a NULL value - a nothing value. This is very important to understand when working with SQL, because any calculation or concatenation that contains a NULL value, will inevitably return a NULL answer - no matter which data type you are working with. Following are some functions and examples that can be used to avoid NULL values.

Functions that can be used to convert NULL values to usable values

iif() - IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned. eg.
coalesce() - The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument. If all the arguments evaluate to NULL, the result is NULL.

Numerical data types

When working with numerical types, you would mostly convert NULL values to 0. You need to be mindful that dividing by 0 will result in an error. Examples follow:

iif(FIELDNAME is null, 0, FIELDNAME)

coalesce(FIELDNAME, 0)

Text / String Data Types

When working with text strings, you would mostly convert NULL values to ''. In a concatenation, however, you could add or remove complete strings based on whether or not the field has a NULL value. Examples follow:

coalesce('Printed in '||COLOURS||' colours', 'Not Printed')

iif(COLOURS is null, 'Not Printed', 'Printed in '||COLOURS||' colours')

User-Defined Fields

All the User-defined field values are stored in one table in QuickEasy, namely FIELDVALUES. Each record in this table is linked to a Module (eg. Customers, Suppliers, etc.), a record in that Module as well as to the User-defined field that defines it.

The link to the Module is created using the MODULEID field. Following is a list of the Module keys used in QuickEasy:

0:Customers
1:Customer Contacts
2:Suppliers
3:Supplier Contacts
4:Staff

The link to the primary key of a Record in the Module is created using the RECORDID field.

The link to the primary key of the user-defined field that defines its properties is created using the FIELDSPECID field.

The value of the user-defined field is stored in either FIELDVALUE, FIELDVALUE_DATE or FIELDVALUE_BLOB depending on the type of field.

Example - User-defined field in Customers

Start with a basic Customer select statement:

select 
C.CUSID, 
C.COMPANY
from CUSMAIN C
order by C.COMPANY

Now add an embedded select statement to extract the value of the user-defined field:

select 
C.CUSID, 
C.COMPANY,
(select F.FIELDVALUE
from FIELDVALUES F
where F.MODULEID = 0
and F.RECORDID = C.CUSID
and F.FIELDSPECID = 1) as BEE_Rating
from CUSMAIN C
order by C.COMPANY

Notes

where F.MODULEID = 0 (0 = Fixed value for Customer Module)

and F.RECORDID = C.CUSID (Will link the user-field to a record in the Customer Module)

and F.FIELDSPECID = 1 (This value identifies the User-defined field and can be viewed in the ID field of the User-defined Fields grid - Edit>Settings & Defaults>User-defined Fields)

Deleting Expired and Orphaned Pages

Here are the scripts you need to:

Delete Paper Prices that have expired. (Delete Expired Paper Prices.sql)
Delete Papers that no longer have prices. (Delete Orphan Units.sql)

How to use these scripts

Before running the scripts, please ensure that you first make a backup of the database and update all the prices from the internet. Paper prices that are not updated (ie. Papers that are discontinued like Dukuza and Avalon) can then be deleted using the above scripts.

The first script requires you to enter the ‘MATCATID’ of the Paper Category - otherwise Ink and Plate prices will also be deleted. This would in most cases be ‘1’, but to be sure you can run the following script:

select MATCATID, NAME from MATCAT;

1. Delete Expired Paper Prices.sql

delete from MATPRICE
where MATPRICEID in
(select
MP.MATPRICEID
from MATPRICE MP
left join MATUNIT MU on MP.MATUNITID = MU.MATUNITID
where MU.MATCATID = 1
and MP.UPDATED < (select (CURRENT_DATE - 30) from RDB$DATABASE));
commit work;

2. Delete Orphan Units.sql

Delete from MATUNIT MU
where (select count(*) from MATPRICE MP where MP.MATUNITID = MU.MATUNITID) = 0;
commit work;

SQL Examples

Get a current valuation of stock in hand

select
STOCKID,
MATUNITID,
DESCRIPTION,
CODE,
UNIT,
INSTOCK,
COSTPRICE,
INSTOCK * COSTPRICE as STOCKVALUE
from 
    (
    select
    S.STOCKID,
    M.MATUNITID,
    M.DESCRIPTION,
    M.CODE,
    U.DESCRIPTION as UNIT,
    
    iif(S.QTYINSTOCK = 0, NULL, S.QTYINSTOCK) as INSTOCK,

    (select 
      max(MC.COSTPRICE / MC.PRICEQTY)
      from MATCOST MC 
      where MC.MATUNITID = S.MATUNITID
      and MC.UNITID = S.UNITID 
      and MC.STATUSID = 1) as COSTPRICE

    from STOCK S
    join MATUNIT M on S.MATUNITID = M.MATUNITID
    left join UNITS U on S.UNITID = U.UNITID
    )
order by 2,3,4

Calculate the Qty Out when working with Sheets

select
maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED) as QTYOUT,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYDOWN_ROTATE, QTYDOWN_STRAIGHT) as QTYDOWN,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, 'Yes', 'No') as ROTATED,
(AREA_PARENT-(maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED)*AREA_CHILD))/AREA_PARENT*100 as WASTE
from
    (
    select 
      floor((DEPTH_PARENT / DEPTH_CHILD)) * floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYOUT_STRAIGHT,
      floor((DEPTH_PARENT / WIDTH_CHILD)) * floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYOUT_ROTATED,
      
      floor((DEPTH_PARENT / DEPTH_CHILD)) as QTYDOWN_STRAIGHT,
      floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT,
      floor((DEPTH_PARENT / WIDTH_CHILD)) as QTYDOWN_ROTATE,
      floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYACCROSS_ROTATE,
      
      DEPTH_PARENT * WIDTH_PARENT as AREA_PARENT,
      DEPTH_CHILD * WIDTH_CHILD as AREA_CHILD
    from 
        (
        select 

        cast(1200 as float) as DEPTH_PARENT,
        cast(2400 as float) as WIDTH_PARENT,

        cast(600 as float) as DEPTH_CHILD,
        cast(900 as float) as WIDTH_CHILD

        from RDB$DATABASE
        )
    )

Calculate the Qty Out when working with Reels

select
iif(WASTE_ROTATE < WASTE_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS,
iif(WASTE_ROTATE < WASTE_STRAIGHT, 'Yes', 'No') as ROTATED,
minvalue(WASTE_ROTATE, WASTE_STRAIGHT) as WASTE
from
    (
    select 
    QTYACCROSS_STRAIGHT,
    QTYACCROSS_ROTATE,
    ((WIDTH_REEL - (QTYACCROSS_STRAIGHT * WIDTH_CHILD))/WIDTH_REEL * 100) as WASTE_STRAIGHT,
    ((WIDTH_REEL - (QTYACCROSS_ROTATE * DEPTH_CHILD))/WIDTH_REEL * 100) as WASTE_ROTATE
    from
        (
        select 
        WIDTH_REEL,
        DEPTH_CHILD,
        WIDTH_CHILD,        
        floor((WIDTH_REEL / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT,
        floor((WIDTH_REEL / DEPTH_CHILD)) as QTYACCROSS_ROTATE
        from 
            (
            select 

            cast(2000 as float) as WIDTH_REEL,
            cast(600 as float) as DEPTH_CHILD,
            cast(1200 as float) as WIDTH_CHILD

            from RDB$DATABASE
            )
        )
    )

Generate a list of Users and Access levels to a Transaction Type

select
USERNAME,

case 
  when ((ACCESS is null) or (ACCESS = 0)) then 'Access Denied'
  when ACCESS = 1 then 'View - User only'
  when ACCESS = 2 then 'View - Rep only'
  when ACCESS = 3 then 'View - All'
  when ACCESS = 4 then 'Edit - User only'
  when ACCESS = 5 then 'Edit - Rep only'
  when ACCESS = 6 then 'Edit - All'
  when ACCESS = 7 then 'Edit & Delete'
  when ACCESS = 8 then 'Full Access'
  end as ACCESS
  
from 
    (
    select 
    S.SHORTNAME as USERNAME,

    (select 
    max(A.ACCESSID)
    from MODULESSETTINGS A
    where A.ENTRYTYPEID = 1 --ENTRYTYPEID
    and ((A.USERID = S.STAFFID)
      or (A.ROLEID in 
      (select R.ROLEID
      from MODULESUSERROLES R 
      where R.USERID = S.STAFFID)))) as ACCESS

    from CPYSTAFF S 
    )

Concatenate Strings, that may or may not exist, into one Comma Delimited String

select

ERROR1 
||trim(leading ' ' from iif(((ERROR1 > '') and (ERROR2 > '')), ', ', ''))
||ERROR2
||trim(leading ' ' from iif(((ERROR1||ERROR2 > '') and (ERROR3 > '')), ', ', ''))
||ERROR3
||trim(leading ' ' from iif(((ERROR1||ERROR2||ERROR3 > '') and (ERROR4 > '')), ', ', ''))
||ERROR4
    
FROM
    (
    select 
    --'' as ERROR1,
    'This is Error 1' as ERROR1,
    '' as ERROR2,
    --'This is Error 2' as ERROR2,
    --'' as ERROR3,
    'This is Error 3' as ERROR3,
    --'' as ERROR4
    'This is Error 4' as ERROR4
    from RDB$DATABASE
    )

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.