Oracle - create public database link to oracle db

 1. Change sqlnet.ora file from SQLNET.AUTHENTICATION_SERVICES= (NTS) to SQLNET.AUTHENTICATION_SERVICES= (NONE)

2. Create public database link with command:

CREATE PUBLIC DATABASE LINK DBLINK1

    CONNECT TO REMOTE_USER_NAME IDENTIFIED BY password

    USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.2 )(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )';

COMMIT;

3. Test link with command:

select * from table_name@DBLINK1;

4. Drop link with command:

DROP  DATABASE LINK DBLINK1;

COMMIT;

Oracle - calculate time difference with time_zone adjustment

Task: Calculate difference between CREATEDON  column (with UTC time_zone) and CURRENT_DATE (with local time zone from database) 

Table contents:

select CREATEDON, CURRENT_DATE from TABLE_NAME;

Query:

with time_difference as

(

select

cast(FROM_TZ(CAST(CREATEDON AS TIMESTAMP), 'UTC') at time zone (SELECT DBTIMEZONE FROM DUAL)  as date) AS Created,

cast(FROM_TZ(CAST(CURRENT_DATE AS TIMESTAMP), (SELECT DBTIMEZONE FROM DUAL)) AS DATE) AS CurrentDate

from TABLE_NAME

)

select 

to_char(Created,'YYYY-mm-dd HH24:MI') as Created,

to_char(CurrentDate ,'YYYY-mm-dd HH24:MI') as CurrentDate,

round((CurrentDate - Created) * 24 * 60) as DifferenceInMinutes

from time_difference;

Result:

Google BigQuery - simple function to read XML tags with Regex

 1. Simple SQL function to read XML tags from string using regex

CREATE OR REPLACE FUNCTION `PROJECT.TEST.readXML`(temp1 STRING, tag STRING) RETURNS STRING AS (

REGEXP_SUBSTR(temp1,CONCAT("<",tag,">(.*?)<\\/",tag,">"))

);

 

2. Usage of function

DECLARE example STRING DEFAULT "'<TELEGRAM><Equipment>Welding machine</Equipment><State>SENT</State></TELEGRAM>'";

SELECT 

TEST.readXML(example,"Equipment") as Equipment,

TEST.readXML(example,"State") as State,

TEST.readXML(example,"TELEGRAM") as TELEGRAM

Google BigQuery SQL - calculate production by each hour of the shift

1. Change the date of 3rd shift after midnight to day -1

SELECT  

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date

...

2. Calculate the shift of production

SELECT  

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift

...

3. Calculate the time difference between the beginning of the shift and production time

SELECT

 -- Calculate the time difference between the begining of the shift and production date

IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

as HOUR_OF_PROD

...

4. Create the pivot table

WITH Production as

(

  SELECT  

    WorkCenter, SerialNumber, 

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date,

    

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift,

    -- Calculate the time difference between the begining of the shift and production date

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

      TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

    as HOUR_OF_PROD

FROM `PROJECT.TEST.TABLE`

)

select * from Production

PIVOT(COUNT(SerialNumber) FOR HOUR_OF_PROD IN (1,2,3,4,5,6,7,8))

5. Results

Power shell - save sql query result to file

 1. Simple script to run query and save it to file

$fileName = 'FILE_PATH'

$SQLServer = "SERVER_NAME"  

$SQLDBName = "DATABASE_NAME"  

$userid ="USER_NAME"  

$password = "PASSWORD"   

$delimiter = ";"

$SqlQuery = "SELECT * FROM TABLE_NAME";

#SQL Query 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $userid; Password = $password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  

$SqlCmd.CommandText = $SqlQuery  

$SqlCmd.Connection = $SqlConnection  

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  

$SqlAdapter.SelectCommand = $SqlCmd   

#Dataset  and save to file

$DataSet = New-Object System.Data.DataSet  

$SqlAdapter.Fill($DataSet)  

$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $fileName -NoTypeInformation

Transact-SQL: insert into two tables, use newly created values with output clause

Transact-SQL: insert into two tables, use newly created values with output clause

Microsoft SQL

Tables:

Table1 Columns: Id, Name

Table2 Columns: Id2, Surname, Table1Id

Data:

Name: Adam 

Surname: Sandler

Query:

INSERT INTO Table1(Name) 

OUTPUT inserted.Id, 'Sandler' INTO Table2 (Table1Id, Surname)  

VALUES('Adam');

Query will not work with foreign keys defined. In order to use it with foreign keys you need to switch off constrain check.

f.e.

ALTER TABLE Table2 NOCHECK CONSTRAINT ALL

GO

Query

ALTER TABLE Table2 CHECK CONSTRAINT ALL

GO

SQL: group by number of rows (divide result into sections with particular number of rows)

SQL: group by number of rows

Oracle

Example shows how to divide "group by clause"result into sections with particular number of rows.

Table:
select column1 from table1;

Query:

Group capacity: 4

with query1 as
    (
    select 
    column1,
    row_number()  OVER (PARTITION BY column1  ORDER BY column1)-1 as c1
    from table1
    )
    select 
    column1,
    count(column1)
    from query1
    group by column1, floor(c1/4)
    order by column1, count(column1) desc

Result: