spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Assigning value to variable from a select count


You need to use SELECT INTO in PL/SQL to set values returning from SELECT queries. Can you change it as follows and see what error you get after that?

Also IF EXISTS is not valid in Oracle, see here.

I guess, you cannot get the values of the deleted row with CONTRATO.DATA_FIM_CONTRATO, instead, you need to write :old.DATA_FIM_CONTRATO. This time, here.

create or replace TRIGGER
ANULAR_CONTRATO BEFORE DELETE ON CONTRATO
FOR EACH ROW
DECLARE
valor NUMBER;
juro NUMBER;
cond1 NUMBER;
cond2 NUMBER;
BEGIN
SELECT COUNT(N_FATURA) INTO juro FROM FATURA WHERE
EXTRACT(month FROM FATURA.DATA_FATURA) =
EXTRACT(month FROM SYSDATE);
valor := 50 + juro;
IF DELETING THEN
    IF (MONTHS_BETWEEN(:OLD.DATA_CONTRATO,
SYSDATE)) <= 8 THEN
        SELECT COUNT(FATURA.CODIGO_CLIENTE) INTO
cond1 FROM FATURA, CONTRATO WHERE
(FATURA.CODIGO_CLIENTE = CONTRATO.CODIGO_CLIENTE)
AND (FATURA_ESTADO = 'Nao Pago');
        IF cond1 > 0 THEN
            INSERT INTO MENSALIDADE
(N_MENSALIDADE, CODIGO_CLIENTE, N_CONTRATO, VALOR,
DATA_FIM) VALUES (SEQ_MENSALIDADE.NEXTVAL,
:old.CODIGO_CLIENTE, :old.N_CONTRATO, valor
,:old.DATA_FIM_CONTRATO);
        END IF;
    ELSE
        SELECT COUNT(FATURA.ESTADO) INTO cond2
FROM FATURA, CONTRATO WHERE (FATURA.CODIGO_CLIENTE
= CONTRATO.CODIGO_CLIENTE) AND (FATURA_ESTADO =
'Nao Pago');
        IF cond2 > 0 THEN
            INSERT INTO MENSALIDADE
(N_MENSALIDADE, CODIGO_CLIENTE, N_CONTRATO, VALOR,
DATA_FIM) VALUES (SEQ_MENSALIDADE.NEXTVAL,
:old.CODIGO_CLIENTE, :old.N_CONTRATO, valor
,:old.DATA_FIM_CONTRATO);
        END IF;
    END IF;
END IF;
END ANULAR_CONTRATO;

Categories : SQL

Related to : Assigning value to variable from a select count
order of evaluation in C with assigning a variable with a function that changes a variable in the same assignment
In both these expressions sum1 = (i / 2) + fun(&i); sum2 = fun(&j) + (j / 2); you have unspecified behavior, because the order of evaluation of the subexpressions of + is unspecified. The compiler is free to evaluate (i/2) and (j/2) before or after the call, as it sees fit. Unlike && and || operators that force evaluation of their left side before their right side, +, -, *, /,

Categories : C
Assigning a string variable in a dos FOR loop
@echo off >Nul setlocal EnableDelayedExpansion for /R %%v in (*.pdf) do ( set pathname=%%~pv >Nul echo !pathname! ) endlocal Here mind EnableDelayedExpansion keyword of setlocal command = Expand variables at execution time rather than at parse time, if used !pathname! syntax instead of %pathname% one.

Categories : Batch File
Django JOIN. select data + select count
Get all the categories: categories = KeywordCategory.objects.all() Count the entries in them: for category in categories: category.keyword_set.count()

Categories : Python
Assigning a variable from nested generics in generic class in Java?
First is important to know, that the compiler replace ? operator with Object class. So when you write this: DummyClass<?> dummy your class definition will look like this: public class DummyClass<Object> and methods like this: public List<Object> getList() { return new ArrayList<Object>(); } public Set<List<Object>> getListSet() { return new HashSe

Categories : Java
SELECT with a COUNT of another SELECT
Try a dependent subquery: SELECT t1.*, ( SELECT count(*) FROM sometable t2 WHERE t2.id = t1.id AND t2.status = 3 ) As somecolumnname FROM sometable t1 WHERE t1.status=0

Categories : Mysql
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.