ORA-00904: Invalid sysdate identifier error

I am trying to run below query in Oracle but getting this error. Any idea how to resolve? SELECT DISTINCT t1.p_id "Id", (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate",

Oracle Date - Get January 1 of the given year

I have year as a parameter in proc which can be 2017,2018,etc. I want get 1 Jan of that year in Date Format in oracle server. Is there any way to do that ?You may need something like: create or replace procedure checkDate ( pYear IN number) is vNum n

Oracle. Invalid ASCII value of the regex result

I'd like to edit a string. Get from 2 standing nearby digits digit and letter (00 -> 0a, 01 - 0b, 23-> 2c etc.) 111324 -> 1b1d2e. Then my code: set serveroutput on size unlimited declare str varchar2(128); function convr(num varchar2) return varc

insert the identification number only in SQL

I have a SQL Server table like this +----+-----------+------------+ | id | acoount | date | +----+-----------+------------+ | | John | 2/6/2016 | | | John | 2/6/2016 | | | John | 4/6/2016 | | | John | 4/6/2016 | | | Andi | 5/6/2016 | | | Steve | 4/6/

The composite primary key is displayed as null

I am implementing a composite primary key made up of 3 other values. I have created the table, and inserted data in all other fields. Am I correct to assume that I don't need to provide values for the composite pk as its using other values to create

select sections that have no student enrolled

I want to select all sections which do not have any students enrolled These are the three tables: ENROLLMENTS student_id, section_id SECTIONS course_id, section_id COURSES course_id, description The output table should look like this: course_id | des

Find the best-selling item and the lowest item in a table

I have two tables as follows-- ORDERS create table orders ( ono number(5) not null primary key, cno number(5) references customers, eno number(4) references employees, received date, shipped date); ODETAILS create table odetails ( ono number(5) not n

Oracle case / replacing the result of grouping pivots

I have following query to display pivoted table: select deptno, clerk, salesman, manager, analyst, president from (select deptno, job, sal from emp ) pivot( sum(sal) for job in ( 'CLERK' as clerk, 'SALESMAN' as salesman, 'MANAGER' as manager, 'ANALYS

Panel Grid Layout or Expandable Panel?

I'm creating a page based on a template to present a query with results table and when i select a result the same data as to be presented on the side as a form. I have tryed to do it inserting a panel grid layout with some cells, so each cell will ha

Multiple fields in the control statement

I like to be able to enter the following values into my table: 0,0 ; 0,1 ; 1,0 But i want to disable the following value: 1,1 I have made the following query: CREATE TABLE mytab (a CHAR(1), b CHAR(1), CHECK(a != 1 AND b != 1)); I am able to enter 0,0

Delete from two tables by using an SQL query

I'm using these SQL queries to delete rows from two tables: DELETE FROM AGENT WHERE ID = ? DELETE FROM AGENT WHERE AGENT_GROUP_ID = (SELECT ID FROM AGENT_GROUP WHERE NAME = ?) I'm interested can I somehow combine the two SQL queries into one SQL quer

SQL gets ROW_NUMBER and COUNT on every SELECT query

I´m building a grid mechanism where I need to retrieve data from Database the total or records found, retrieving just a range of these records with a row_number in it. I´m using SqlServer for testing, but I need to support that on Oracle and MySql as

Compare a group of rows in Oracle

I have a table having coloumns, ID | BRANCH | START_NUM | END_NUM 123 S 25 95 234 S 45 105 445 S 46 90 556 M 56 129 78 M 76 199 87 M 80 110 987 M 89 128 777 M 100 1500 I want to first group my result on basis of BRANCH(M,S Here).Then i want only thos

Insert an empty line

This is so simple it has probably already been asked, but I couldn't find it (if that's the case I'm sorry for asking). I would like to insert an empty row on a table so I can pick up its ID (primary key, generated by an insert trigger) through an Ex

Parenthesis error in SQL query

I have used following query in my Oracle DB: UPDATE test5_rdf_memoization SET object ='galib' WHERE ( predicate='platform' AND object IS NULL AND subject IN ( SELECT subject FROM test5_rdf_memoization t1, test_parameters_new_5 t2, test_parameter_deta

When should I validate using FOR UPDATE in a procedure?

If I use a FOR UPDATE clause in a stored procedure when should I "commit"? After closing the opened cursor or before closing the opened cursor? Below is the procedure im using, am i doing it in correct way? CREATE OR REPLACE PROCEDURE Proc_Updat

LISTAGG equivalent with windowing clause

In oracle, the LISTAGG function allows me to use it analytically with a OVER (PARTITION BY column..) clause. However, it does not support use of windowing with the ROWS or RANGE keywords. I have a data set from a store register (simplified for the qu

Is there a way to cancel a WHERE clause in SQL?

This is my base query select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding&

how to update multiple tables in Oracle DB?

i am using two tables in my oracle 10g. the first table having the keyword,count,id(primary key) and my second table having id, timestamp.. but i am doing any chages in the first table(keyword,count) it will reflect on the my second table timestamp..