0
votes
0answers
36 views

Is it possible to learn (and make a career out of) SQL without official training? [on hold]

I'm really interested in learning SQL (SQL server or Oracle) as I already have a bit of experience with the databases at work (mostly select statements). I don't have a technical background since I ...
0
votes
0answers
11 views

How to join 2 tables and traverse up hierarchy at the same time

Any help that can be given on a current problem I'm having in an Oracle environment would be greatly appreciated. I have 2 tables. One contains jobs to be performed on pieces of equipment, the other ...
1
vote
0answers
16 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
0
votes
1answer
25 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 ...
0
votes
0answers
32 views

Logging and Error MS SQL Server 2008 R2 [closed]

Currently there are multiple SSIS packages, batch jobs etc. I'm looking for best practices that could be followed for normal Logging and Error Logging for SSIS packages . I understand it is a ...
0
votes
1answer
21 views

Add range of values under a column using rowid

How do i add range of values in a column based on row id? For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5. Additionally is it possible to divide the then ...
-3
votes
1answer
66 views

Selecting a row and row above [closed]

I need to select a row from a table if it has "PART_NO=2" and the row just above it. What should be the query. I'm using Toad for Oracle. Thanks. **Data** *Title------Part_seq_no------*End_time ...
-2
votes
0answers
31 views

Midnight Datetime difference - SQL Oracle

We have an application which reads information from SQL and Oracle (testing purposes). A datetime value is placed inside the table: Example: 05/09/2014 00:00:00 When we have this date in SQL: The ...
0
votes
2answers
49 views

Oracle GROUP BY question

I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use. ...
0
votes
2answers
30 views

inconsistent type after querying search_condition column in all_constraints table Oracle

I'm trying to get the not null constraints in my tables, so I'm using the following sql request : select * from all_constraints where owner='userx' and constraint_type='C' and ...
0
votes
1answer
29 views

Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

I have run into a problem where some IDs have multiple names associated with them. That is, if I have a location ID from one table and need to pull the location's name from another table based on the ...
0
votes
0answers
30 views

PL/SQL Error bad bind variable

Where is the problem with my code. It should work, but Compiler says: Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' ...
2
votes
1answer
70 views

Select the max from two max values

I need to find the airport(departure or arrival) that had the most passengers in the last year. The tables are: PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) ...
0
votes
1answer
40 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...
2
votes
1answer
58 views

Fastest way to change column type from varchar2 to number on large table in Oracle

I have a table which contains a VARCHAR2 column that must me changed into NUMBER. The table contains more than 30 millions rows. The table definition : CREATE TABLE EFF ( MOD ...
-3
votes
0answers
79 views

SQL Query Consuming lot of CPU Usage:

Following SQL Query is Consuming lot of CPU Usage: SELECT * FROM (SELECT REQ.* FROM provisioningrequest REQ, unique_id_mapping UN WHERE ( instance_name IS NULL ...
0
votes
1answer
33 views

Max from 2 columns with priority date col then the seq col Oracle

I have a column xdate, column seqnumber, personID I want 1 record per personID, where it is the most recent xdate max(xdate) if there is multiple same max xdates i want it to look then for the max ...
0
votes
1answer
32 views

PL/SQL group orders by date and sum the total

I need to group orders by date and sum the total, My table looks like this : TEST_DATE TEST_ID - NUMBER SUCC_ID - NUMBER I need to group by date and sum the total, this is my query: SELECT ...
0
votes
1answer
42 views

procedure to select the first name of the passed id

I have a table person_name DESC PERSON_NAME Name Null Type ------------- -------- ------------ ID NOT NULL NUMBER(9) FIRSTNAME VARCHAR2(20) ...
0
votes
1answer
46 views

Update table from different table

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = ...
0
votes
1answer
50 views

How to check if current SQL session has uncommited changes?

Say you are in SQL Developer and want to check if the current user session (i.e. your connection) has uncommitted changes. If you quit SQL Developer, it does such a check and displays a dialog box ...
0
votes
1answer
24 views

How to find the last error codes and messages of failed statements executed by a user?

Hopefully, a program that connects to a Oracle DB does sufficient logging of errors on its own, e.g. when doing a bulk insert the error reporting of a program could produce something like this: ...
1
vote
1answer
29 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
1answer
30 views

Sub query with not equal

Sub query with not equal table_1 col1 SV0362 RU0177CSPG NO0124CSPG VN3582 AT9923CSPG BZ0324 PE0309 AS4293EXPT ML0331OMKT table 2 colA OO56128665 OO56128665 OO56128665 OO56128665 US0170 US0170 ...
0
votes
2answers
55 views

SQL insert into … values (…, select(…))

I am trying to insert 25 users, user1...25 into Members with the same GR_ID = 2 with command: insert into MEMBERS (GR_id, username) values (2, (select username from USERS where username like ...
1
vote
0answers
74 views

sql query takes too long to execute [closed]

I am having trouble with this query. It takes 10 hours to execute. WITH CONSUM AS ( SELECT C.ID_MEASURER,C.ID_MEASURING_POINT,C.CONSUM_TYPE FROM GCGT_RE_CONSUM_H_E C WHERE C.DAY ...
0
votes
1answer
41 views

Compare dates in Oracle SQL [closed]

I have a table LoanDeadline that has a column called deadline, it's simple text and not Date and it's in the format of July 25, 2014 How do I compare this with sysdate ? So far I have SELECT ...
0
votes
1answer
57 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
2
votes
1answer
96 views

Table name with special character in the end

I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab. SQL> SELECT ...
0
votes
1answer
45 views

Distributed transaction coordinator between SQL Server and Oracle

I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can ...
0
votes
2answers
308 views

How to create an oracle dump(.dmp) file using Toad?

We can export the entire database schema(skeleton) along with its data in one .dmp file using oracles "exp" tool. But how do I do it using Toad only. I can't use this C:\Users\kaunds_oliver>exp ...
1
vote
2answers
76 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
1
vote
2answers
49 views

Optimize rows to columns conversion

I have a big Oracle table (around 40 million rows) that looks like this: ID Name Question Answer Reason 3 Name1 1 Yes blah blah 3 Name1 2 No NA 3 Name1 3 ...
0
votes
2answers
34 views

Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

I am working on data in oracle 11g as below : subscriber_id | count_s | count_t | min_count | diff | negative_diff | positive_diff 137606401 3 3 3 0 ...
1
vote
2answers
68 views

Aggregate function / query in Oracle 11g

I am working in Oracle 11g. I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert) I ...
3
votes
3answers
180 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
0
votes
2answers
82 views

Difference Between SELECT and select in oracle sql

The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. Means The SELECT instead of select statement will be parsed again ...
-1
votes
3answers
64 views

Trouble quering a join in oracle sql

Theres a question on an exercise which asks me to write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. I have to use a ...
0
votes
3answers
1k views

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I am using Oracle 11g and want to export a table using below statement C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER ...
0
votes
1answer
31 views

Output another word instead of the word in the table in a query

Suppose I have a table where I have a field which can have to values: "enabled" and "disabled". Suppose want my query to output "unblocked" instead of "enabled" and "blocked" instead of "disabled". ...
1
vote
1answer
111 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
0
votes
0answers
32 views

Oracle right outer join SELECT statement with a table

I am trying to find out the tables that are in MONITORING.USAGE_TRACKING table in first place, along with the other tables that need to be analyzed. SELECT A.JOB_NAME, A.START_DATE from ...
0
votes
1answer
35 views

Oracle: Replace corrupted first datafile

A datafile associated with a tablespace has been corrupted (got truncated to size 0). I don't mind losing the data, because I have a full DB dump which I can import. Now I want to replace this ...
0
votes
2answers
47 views

Oracle conditional statements

I am making a report, and I am trying to do following: If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. ...
0
votes
1answer
25 views

What is the “other miscellaneous information” in the Shared Global Area (oracle database)?

I'm studying Oracle database ,and it says here that the SGA contains these data structures : Database buffer cache Redo log buffer Shared pool Java pool Large pool (optional) Data dictionary cache ...
0
votes
1answer
80 views

The best way to query a partitioned table in Oracle

I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better: making ...
0
votes
1answer
132 views

ORA-00907: missing right parenthesis on query with subqueries

I am getting ORA-00907: missing right parenthesis for the following query: SELECT PEOPLE.NAME FROM CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L WHERE NOT EXISTS ( (SELECT DISTINCT ARTIST.ID_U ...
1
vote
2answers
50 views

Time elapsed Calculation

I need to calculated time elapsed for each user to perform his part in a transaction. The sequence is: 0 -1 - B2 or 0-B3 The table looks like the following: ...
2
votes
1answer
137 views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
0
votes
1answer
72 views

SQL query for all combinations without repetition

I need a function for calculating combinations without repetition from a list. Example: I have a list of names e.g. Tom Bob Paul Tina Now I need a list of 3 names out of the 4 without having a ...