Data dodania Pytanie
2017-02-12 18:02 How to save date in ORACLE in Y-m-d H:i:s format? »
In PHP, date('Y-m-d H:i:s') gives result like '2017-02-17 23:10:00' format. Now, I want to save the result in ORACLE 10g. I am using Oracle 10g Xpres...
(3) odpowiedzi
2017-02-12 15:02 sql - Unpivot a join between two tables with the same column names »
I am having trouble with unpivoting the results of a join between two tables. The two tables are defined as: create table scores_jan ( id number ...
(5) odpowiedzi
2017-02-12 02:02 Getting command not properly ended while testing for empty relations »
I am reading database systems concepts book by Henry Korth.I tried the following exactly from the book for empty relations test. select course_id fro...
(1) odpowiedzi
2017-02-11 23:02 SQL - Change value on Row 3 when Case condition is met on Row 1 »
Am using SQL Oracle and trying to find out, based on below data set, how can I update row 3 when condition is met on row 1? Rule is: If Check = Y the...
(1) odpowiedzi
2017-02-11 10:02 Dropping all objects of a schema »
The query below will drop all the tables which are present in the current user A's schema (normal scenario). select 'drop '||object_type||' '|| objec...
(2) odpowiedzi
2017-02-11 05:02 how to select rows with multiple where conditions »
Not sure if the title made sense. But this what I need, I am bad at sql: I have table with the following data ID| Value ------ | ------ 1| A 1| B 1...
(4) odpowiedzi
2017-02-10 17:02 How to get rid of files with names bin$ »
Using Jooq generator, by Gradle plugin, I am getting now with POJOs and tables not only classes with normal names, bu also heaps of files whose names...
(2) odpowiedzi
2017-02-10 10:02 ORA-00922: missing or invalid option when execute "set long 100000" statement in oracle »
I am developing a java sample in which I execute query one after other to get ddl of indexces.My java code is - Statement stmt2 = con.createStatement...
(1) odpowiedzi
2017-02-10 09:02 SqlDeveloper - checking for NULL values without IS NULL clause - in a scripted query »
for circuit in allCircuit: # a cvs of 4 columns part = circuit.split(",") res = cur.execute("""SELECT col4 from ATable WHE...
(1) odpowiedzi
2017-02-10 04:02 SQL ORDER BY DECODE is sorting number as a string? »
I have a table of flight records created as follows CREATE TABLE FLIGHT_DETAILS ( FLIGHT_ID NUMBER(10) PRIMARY KEY, FLIGHT_NO VAR...
(2) odpowiedzi
2017-02-09 19:02 Latest records using sql »
I need to build weekly aggregated data for sales. Where TRNST_QTYis the latest available quantity of the closing week and PRCHS_QTY is the sum of purc...
(1) odpowiedzi
2017-02-09 11:02 Optimizer uses wrong index »
We have a quite simple select statement accessing data with unique index fields. Nevertheless, the optimizer decides to use a bad index and the select...
(1) odpowiedzi
2017-02-09 10:02 Oracle extract alpha characters from string »
I want to extract only alpha characters separately. For example Output for "NIC132DA.1" should be "NIC" and "DA" separately without any numbers. I ...
(1) odpowiedzi
2017-02-08 11:02 Copy tables containing BLOB columns between Oracle Databases »
On adhoc basis, we want to copy contents from 4 of our Oracle production tables to QA/UAT environments. This is not a direct copy and we need to copy ...
(2) odpowiedzi
2017-02-08 09:02 What's the correct way to escape the ? character in a JDBC PreparedStatement when using Oracle 12c MATCH_RECOGNIZE? »
The following query is correct in Oracle 12c: SELECT * FROM dual MATCH_RECOGNIZE ( MEASURES a.dummy AS dummy PATTERN (a?) DEFINE a AS (1 = 1) )...
(1) odpowiedzi
2017-02-08 04:02 SQL select lapsed customers with 30 day frequency by day »
The goal is to select the count of distinct customer_id's who have not made a purchase in the rolling 30 day period prior to every day in the calendar...
(3) odpowiedzi
2017-02-07 18:02 Oracle sql: Order by with GROUP BY ROLLUP »
I'm looking everywhere for an answer but nothing seems to compare with my problem. So, using rollup with query: select year, month, count (sale_id) f...
(3) odpowiedzi
2017-02-07 14:02 How to generate a list periods of each every 30 days from start to today »
I works for a company which request me generate a list of period for each 30 days since the item start until now. Sample: Item 'A' has begin date is 0...
(3) odpowiedzi
2017-02-07 14:02 What's the correct way to fetch all (possibly implicit) results from an Oracle query with JDBC? »
Since Oracle 12c, we can fetch implicit cursors from clients. For instance, it is possible to run the following PL/SQL anonymous block in SQL Develope...
(1) odpowiedzi
2017-02-06 15:02 Alternative to multiple concats in WHERE clause? »
I have a huge query like this: SELECT 'Tipification', COUNT(*) TOTAL, to_char(INITDATE,'YYYY-MM-DD') FROM ( SELECT (TYPE || ' \ ' || SUBTYPE || '...
(1) odpowiedzi
2017-02-06 12:02 how to check if a record is null »
I have a procedure which receives a record as an IN parameter (the procedure also has an OUT parameter.) CREATE OR REPLACE PACKAGE p PROCEDURE p_se...
(3) odpowiedzi
2017-02-06 08:02 function based index vs column index oracle performance »
I already have an index named idx_MyTableColumn on table named MyTable. I ran a query select * from MyTable where MyTableColumn = 'AAA'; Then, I ...
(2) odpowiedzi
2017-02-05 17:02 opening and closing balance query »
I'm trying to figure out the opening and closing balance based on the customer and their daily wise cr/dr transaction. closing= opening+db -cr and ...
(2) odpowiedzi
2017-02-05 12:02 Is there a way to execute an SQL merge atomically? »
I am looking for a way to connect a tuple of values with a random UUID in a non-locking manner and without the potential to fail a transaction due to ...
(2) odpowiedzi
2017-02-05 01:02 Validate UK postcode using regular expression in oracle »
Below is the list of valid postcodes: A1 1AA A11 1AA AA1 1AA AA11 1AA A1A 1AA BFPO 1 BFPO 11 BFPO 111 I tried with (([A-Z]{1,2}[0-9]{1,2})\ ([0-9][...
(1) odpowiedzi
2017-02-04 22:02 Is value on the list of values? (Oracle SQL) »
Below works for SQL Server and MySQL, but how to modify it to work as proper Oracle query: SELECT city FROM station WHERE LEFT(city, 1) IN ('a', 'e',...
(2) odpowiedzi
2017-02-04 22:02 Case in Select Statement issue? »
I would normally use the popular if-else statement in java, but in sqlplus I'm using the case in select statement to query the conditional statement t...
(3) odpowiedzi
2017-02-04 08:02 Split a string based on hyphen »
I have a string like this XX0099-X01 I would like to split the string into two based on the hyphen, i.e. XX0099 and X01 I have tried as SELECT 'X...
(2) odpowiedzi
2017-02-03 19:02 Oracle SQL grouping elements »
I know this question is kind of trivial but I have difficulties writing the SQL for the example shown below. As shown in the first table, is the resul...
(4) odpowiedzi
2017-02-03 16:02 ORA-06553 character set name is not recognized »
I have changed oracle database's NLS_CHARACTERSET from WE8MSWIN1252 toAL32UTF8 as described here Now when I run database commands, I get the followin...
(1) odpowiedzi
2017-02-03 06:02 How to query "LastName, FirstName" from "FirstName LastName" when there may be middle initials? »
Field contains first and last names. PERSON: Bob Franklin Gene Smith Roy G. Biv I am trying to create a query that puts the last name first, then ...
(6) odpowiedzi
2017-02-02 21:02 How to differentiate PrimaryKey and uniquekey Constraint in java »
In both cases of PrimaryKey violation and uniqueKey violation, I see the same message, error code and sql state values. When I run the insert query ...
(3) odpowiedzi
2017-02-02 19:02 Oracle jdbc "createArray" throws "Unsupported feature" exception while trying to pass arrray to prepared statement »
I am trying to pass an array to my prepared statement by doing createArrayOf val prep: PreparedStatement = con.prepareStatement("select * from SOA_WE...
(3) odpowiedzi
2017-02-02 17:02 coldfusion combine results from two queries »
I have two coldfusion queries, like below which return different columns along with a date column. <cfquery name="qry1" datasource="test"> sele...
(2) odpowiedzi
2017-02-02 17:02 A better way to swap two primary key values and circumvent a ORA-00001: unique constraint violated? »
Goal: Swap the primary keys of two records without encountering an ORA-00001: unique constraint violated. The solution that 'works' (further below) i...
(1) odpowiedzi