Overview

SQL Tool can display the results of SELECT SQL queries and executes other query types such as UPDATE, INSERT and DELETE.

This Tool is potentially dangerous, be very careful when using it and make a backup for your database prior to running an update, delete or insert statement.

An explanation of the SQL language is beyond the scope of this manual. Please refer to the following web site for information on SQL: https://www.w3schools.com/sql/default.asp

Using the Tool

Select INPUT | Add-Ins > Datgel Administrator Tools > SQL Tool to launch the Datgel SQL Tool. The procedure is fairly straightforward and referring to the following screen shot:

Choose the SQL statement type you want to write by clicking on the appropriate button. Note the lowercase syntax e.g. <fieldname> is included as a guide only and should be overwritten or deleted before execution of a command.
Choose the database to run the query against – Project or Library
Paste the desired table and field names from the list. Note, by only using table and field names taken from the drop down list, you drastically reduce the chance of getting SQL read errors.
Optional. Use the In Library button to bring up the full path name for the current library file.
Choose between Current Project Only, All Projects, and Selected Project(s).The Current Project Only option checks the current project, All Projects checks every project, and Selected Project(s) allows you to select multiple projects, by clicking Browse and then selecting the projects you wish to use.
This option only works for gINT Enterprise databases, and the following error will appear if using an access-based gINT database.
Click on Execute.
Click on Reset Query to clear the SQL statement box, and Click on Reset Result to clear the results box.

Note that Execute displays the results in the white box below the query, whereas gINT Query will bring up a gINT window to display the results.

Exporting Data using the SQL Tool

If you want to export the result of an SQL statement to an Excel File, you can use the gINT Query option. If the command is properly executed, then this will bring up a window as below.

The window will contain the result of your query and give you the option to either send the result to the printer or to Export your result. Choose Export and then in the second window be careful to choose the type as Excel 2007 (*.xlsx).

Due to current program limitations, you are currently unable to Append or Overwrite existing files. Always create a new file when using the SQL Tool export option.

Example SQLs for CPT Tool

Average Penetration Rate

select [CPT_DATA].[PointID],[CPT_DATA].[ItemKey], round(avg([CPT_DATA].[Penetration_Rate]),1) as [Penetration_Rate]
from [CPT_GENERAL] inner join [CPT_DATA] 
on [CPT_GENERAL].[PointID] = [CPT_DATA].[PointID] 
and [CPT_GENERAL].[ItemKey] = [CPT_DATA].[ItemKey]
group by [CPT_DATA].[PointID],[CPT_DATA].[ItemKey]

Update CPT General

update cpt_general
set Remark = 'Application Class = 1', Contractor='PDE', Test_Type='PC', 
Termination='End of test reached',Test_Method='ISO 22476-1:2012',Status = 'Draft'

Stroke Min Max

select [A].PointID, [A].Itemkey, [A].[Min Depth], [A].[Max Depth], [CPT_GENERAL].[Remark], [CPT_GENERAL].Termination from 
(select Pointid, Itemkey, Max(depth) as [Max Depth], Min(depth) as [Min Depth],format(itemkey,'0000') as [Formated ItemKey] 
from CPT_DATA
group by Pointid,Itemkey) as [A] inner join [CPT_GENERAL] on 
[A].[PointID] = [CPT_GENERAL].[PointID] and [A].[Itemkey] = [CPT_GENERAL].[Itemkey]
order by [A].[PointID],[Formated ItemKey]

Min Depth Plus 300mm

select CPT_DATA.PointID ,M.PointID,CPT_DATA.ItemKey, M.ItemKey , CPT_DATA.Depth
from CPT_DATA left join
(select PointID, ItemKey, min(Depth) as [MinDepth], min(Depth) + 0.3 as [MinDepthPlus300mm] 
from CPT_DATA group by PointID, ItemKey) as [M]
on CPT_DATA.PointID = M.PointID and CPT_DATA.ItemKey= M.ItemKey
where CPT_DATA.Depth <= [M].[MinDepthPlus300mm] and CPT_DATA.PointID='1'

Min Depth 300 mm 2

select [CPT_DATA].*
from [CPT_DATA] left join
(select PointID, ItemKey, min(Depth) as [MinDepth], min(Depth) + 0.3 as [MinDepthPlus300mm] 
from CPT_DATA group by PointID, ItemKey) as [M]
on [CPT_DATA].PointID = M.PointID and [CPT_DATA].ItemKey= M.ItemKey
where [CPT_DATA].Depth <= [M].[MinDepthPlus300mm] and [CPT_DATA].PointID='1'

TempTable attempts

CREATE TABLE TempTable (
    PointID Text  ,
    ItemKey Text ,
    MinDepthPlus300mm Double); 

INSERT into TempTable (PointID,Itemkey, MinDepthPlus300mm)
select PointID, ItemKey, min(Depth) + 0.3 as [MinDepthPlus300mm] from CPT_DATA group by PointID, ItemKey;

CREATE TABLE TempTable As
select PointID, ItemKey, min(Depth) + 0.3 as [MinDepthPlus300mm] from CPT_DATA group by PointID, ItemKey;

select [CPT_DATA].*
from [CPT_DATA] left join TempTable
on [CPT_DATA].PointID = TempTable.PointID and [CPT_DATA].ItemKey= TempTable.ItemKey
where [CPT_DATA].Depth <= TempTable.[MinDepthPlus300mm] and [CPT_DATA].PointID='1';

Top 300m rows to delete

select [CPT_DATA].PointID,[CPT_DATA].ItemKey,[CPT_DATA].Depth
from [CPT_DATA] left join
(select PointID, ItemKey, min(Depth) as [MinDepth], min(Depth) + 0.3 as [MinDepthPlus300mm] 
from CPT_DATA group by PointID, ItemKey) as [M]
on [CPT_DATA].PointID = M.PointID and [CPT_DATA].ItemKey= M.ItemKey
where [CPT_DATA].Depth <= [M].[MinDepthPlus300mm]

Move remark from Strata Main to Depth Remarks

insert into [DEPTH_REMARKS]
(PointID, Depth, Bottom, Remark)
SELECT PointID, Depth, Bottom, Remark
FROM Strata_Main
WHERE Len(Remark) > 0

Randoms

update cpt_general
set ItemKey = cstr(cint(ItemKey)-100+7)
where PointID = 'PointID1'

update cpt_general
set PointID = 'PointID2'
where PointID = 'PointID1'

update CPT_DATA
set Depth = Depth - 1000 -0.32
where PointID = 'PointID1' and cint(ItemKey) >= 18

select PointID, ItemKey, max(Depth) as [End_Depth] from CPT_DATA
group by PointID, ItemKey


Update CPT_GENERAL
inner join (
select PointID, ItemKey, max(Depth) as [End_Depth] from CPT_DATA
group by PointID, ItemKey) as [ED] on ED.PointID = CPT_GENERAL.PointID and 
ED.ItemKey= CPT_GENERAL.ItemKey
Set [End_Depth] = [ED].End_Depth]

select POINT.PointID, first(POINT.HoleDepth) as [HoleDepth], Max(Strata_Main.Bottom) as [Strata Max] 
from POINT left join STRATA_MAIN on POINT.PointID = STRATA_MAIN.PointID group by POINT.PointID 
order by POINT.PointID

select POINT.PointID, first(POINT.HoleDepth) as [HoleDepth], Max(CPT_DATA.Depth) as [CPT Max] 
from POINT left join CPT_DATA on POINT.PointID = CPT_DATA.PointID group by POINT.PointID 
order by POINT.PointID

On this page