[go: up one dir, main page]
Scribd
Upload
41 views3 pages

Sms SQL

Uploaded by

laviniab
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
41 views3 pages

Sms SQL

Uploaded by

laviniab
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

Create Table:

CREATE TABLE "PRODUCT"


( "PRODUCT_ID" NUMBER,
"PRODUCT_NAME" VARCHAR2(200),
"PRICE" NUMBER (12,2)

Create sequence:
CREATE SEQUENCE DEPARTMENT_S START WITH 1 INCREMENT BY 1;

Create Trigger:
CREATE OR REPLACE TRIGGER "DEPARTMENT_SEQ"
before insert on "DEPARTMENT"
for each row
begin
if :NEW."ID" IS NULL THEN
SELECT "DEPRTMENT_S".nextval into :NEW."ID" from sys.dual;
end if;
end;
/

CREATE TABLE "MECHANIC"


( "ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(45) NOT NULL ENABLE,
"PHONENUMBER" VARCHAR2(45),
CONSTRAINT "MECHANIC_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
)
/

CREATE SEQUENCE MECHANIC_S START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER "MECHANIC_SEQ"


before insert on "MECHANIC"
for each row
begin
if :NEW."ID" IS NULL THEN
SELECT "MECHANIC_S".nextval into :NEW."ID" from sys.dual;
end if;
end;
/

select "ID",
"NAME",
"PHONENUMBER"
from "#OWNER#"."MECHANIC"

Send sms:

INSERT INTO MAINTENANCE


(TREATMENT_DATE,"TIMESTAMP",EQUIPMENT_ID,TEXT,OLD_STATE,NEW_STATE, TAGNUMBER)
VALUES (TIMESTAMP '2017-07-10 11:20:23.021000',TIMESTAMP '2017-07-13
11:20:23.021000','ZC_ED_UNIT_A','Unit B failure level','Unknown','WCU_OK',
'SUB01');
- insert this statement into the sms db
(jdbc:oracle:thin:@192.168.0.243:1521:fdmbsql, user `siemens` pass `APeatqlu64qa`),
SUB01 will send it to Christophe.
SUB02 will send it to Henri.
You can of course create your own Subsystem and appoint your phonenumber to it

phone number christophe: +32485829881

select substr(TAGNUMBER,5,3) as subsystem_id


---WHERE 5 is position where it starts, and 3 how many characters to display: 1B2-
CTV-CAM-002 - RESULT: CTV

SELECT SOURCEALARM, TAGNUMBER,


CASE SOURCEALARM
WHEN 'PSCADA' THEN 'REL'
WHEN 'SIGATS' THEN 'ATS'
WHEN 'FSCADA' THEN SUBSTR(TAGNUMBER,5,3)
else 'NO_VALUE' END AS Result
FROM NORMALARMS
===WHERE SOURCEALARM='SIGATS' (FOR TESTING)
=== else clause can be omitted

https://dba.stackexchange.com/questions/75473/insert-missing-dates-from-a-query
https://stackoverflow.com/questions/10395459/comparing-results-with-todays-date

"SQL1"."ISEQ$$_73540".nextval
NEXT VALUE FOR PRIMARY KEY

case when examples - oracle:


The CASE statement is like a series of IF statements, only using the key word WHEN.
A CASE statement is evaluated from top to bottom. If a condition is true, then
corresponding THEN clause is executed and execution jumps to the END CASE (short
circuit evaluation) clause.

============================================================
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN 'Low'
WHEN 0.15 THEN 'Average'
WHEN 0.2 THEN 'High'
ELSE 'N/A' END ) Commission
FROM
employees
ORDER BY
last_name;

==================================================================
SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%' or %% added in the
end.
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;

=============================================================================

Use a CASE Statement in Oracle SQL

* CASE 1: Books with total sales greater than $100,000, display "Best Seller"

* CASE 2: Books with total sales between $10,000 and $99,999 display "Average
Seller"

* CASE 3: Books with sales less than $10,000 display "Poor Seller"

ANSWER:

col store_name format a25


col book_title format a25
col total_sales format $999,999
col sales format a15

break on sales skip 2

select
(case
when sum(quantity)*book_retail_price > 100000 then 'Best Seller'
when sum(quantity)*book_retail_price < 10000 then 'Poor Seller'
else 'Average Seller'
end ) sales,
store_name,
book_title,
sum(quantity)*book_retail_price total_sales
from
store,
sales,
book
where
store.store_key = sales.store_key
and
sales.book_key = book.book_key
group by
store_name,
book_title,
book_retail_price
order by
total_sales desc
;

================================================================

You might also like