I have zero sql query writing skill, in fact, I've never done it before, but for some reason, a request came across my desk to get information from one of our databases. I have about 200 ticket numbers that have no information attached, so instead of going through the GUI to input each number, I'm hoping to create a query to run against the (Oracle) DB and extract the information.
For instance, I have a ticket number 123456, I need to lookup the ticket number and pull user information (dept, email, phone, ect).
Help me get started with this please.
---------- Post updated at 07:19 AM ---------- Previous update was at 07:14 AM ----------
Perhaps something like this would work.
Code:
SELECT EmployeeID, FirstName, LastName, Email FROM Employees
WHERE Ticket = 'ticketnumber'
The only question I have from this is, how to get the query to cycle through a list of ticket numbers?
You need to tell us about the schema - tables involved mainly. You need to put together the logic of how the data relates so you perform a lookup.
Code:
describe tablename -> list the table information so you can do this
-- so this is all hooey below just 'pseudo SQL' I'm using the syntax table.columnname
-- for edification not because it is required
select ticket.ticketid, emp.dept, ticket.pullname, emp.phone, emp.emailaddr
from tickets, emp
where tickets.ticketid=123456
and emp.empname=ticket.pullname;
...
...
...
Perhaps something like this would work.
Code:
SELECT EmployeeID, FirstName, LastName, Email FROM Employees
WHERE Ticket = 'ticketnumber'
The only question I have from this is, how to get the query to cycle through a list of ticket numbers?
It's not clear from your post what your table looks like.
Does the "Employees" table have a column called "Ticket"?
If ticket information is stored in a separate table, then how are these two tables (Employees and the ticket table) related?
Is there a column in the ticket table that points to or references the "Employees" table? In other words, does the ticket table have a foreign key to the "Employees" table?
As Jim mentioned, the "describe" command will provide you information about the table structure. It should be run in SQL*Plus (GUI or command-line). Or you could use any of the ad-hoc query tools like TOAD, SQL Developer, SQL Navigator, PL/SQL Developer etc. that your company provides and view the table structures by clicking.
Assuming the query you posted runs successfully in your schema, you could use the following query to fetch information about multiple tickets:
Code:
SELECT Ticket, EmployeeID, FirstName, LastName, Email FROM Employees
WHERE Ticket IN ( 'ticketnumber_1', 'ticketnumber_2', 'ticketnumber_3', 'ticketnumber_4' );
Last edited by rbatte1; 11-12-2014 at 08:56 AM..
Reason: Added LIST=1 tags & LIST=a tags
Just got more information on the database layout. Each table has links to another table to get the information. For example:
There's a table called Employee.Id that lists all the tickets associated with that employee, but from there it links to another table called Contact.Id which contains the Email and Phone numbers of each employee and there's a third link to another table that has the Location of the employee....Arg....
Alright, the data is spread across multiple tables. Now it's time to find out if there is a column present in all three tables which can be used to "connect" those three tables (see durden_tyler's question 2a). It should be easy to find out with the command jim mcnamara has provided.
Assuming there is a column EmployeeID present in all three tables, the SQL query could be something like
Code:
SELECT e.EmployeeID, e.FirstName, e.LastName, e.Ticketnumber, e.Tickettext,
c.Email, c.<anotherColumnFromContactTable>,
l.<someColumnFromLocationTable>, l.<anotherColumnFromLocationTable>
FROM Employee e
INNER JOIN Contact c
ON e.EmployeeID = c.EmployeeID
INNER JOIN Location l
ON e.EmployeeID = l.EmployeeID
WHERE e.Ticketnumber IN (32432,32434,34524);
If you already have the ticket numbers you want to check in another table, the WHERE clause could be something like
Code:
WHERE e.Ticketnumber IN (SELECT numbers FROM anothertable);
Ok, walk me through this step by step. I'm trying to grasp the concept of a db structure.
I used the 'DESCRIBE' option as mentioned earlier and it returned a plethora fields. I assumed I could take one of these fields and pull the information? Or is this purely just a list of fields?
Ok, walk me through this step by step. I'm trying to grasp the concept of a db structure.
I used the 'DESCRIBE' option as mentioned earlier and it returned a plethora fields. I assumed I could take one of these fields and pull the information? Or is this purely just a list of fields?
Your Oracle database instance comprises of multiple "schemas" or "database users", each of which has its own password. A "schema" is a logical container of database objects - tables, views, indexes, sequences, packages etc.
Each Oracle client installation is shipped with a client command-line utility called "sqlplus" (or SQL*Plus - its GUI incarnation).
"DESCRIBE <table_name>" is actually a sqlplus *command* and not an *option*. What it does is - it lists the column definitions for the table <table_name>. You can use it to describe views or packages as well. The "help" command provides more information:
Code:
scott@PDBORA12C>
scott@PDBORA12C> help describe
DESCRIBE
--------
Lists the column definitions for a table, view, or synonym,
or the specifications for a function or procedure.
DESC[RIBE] {[schema.]object[@connect_identifier]}
scott@PDBORA12C>
Notice that "RIBE" is within brackets "[ ]" so that part is optional, which means you can use the short form "desc" for "describe".
Also, the commands and table names are case-insensitive in Oracle.
In the following example, I describe the "EMP" table:
Code:
scott@PDBORA12C>
scott@PDBORA12C> desc emp
Name Null? Type
----------------------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
scott@PDBORA12C>
It tells me that the "emp" table has 8 columns as listed under "Name", with the datatypes as listed under "Type". It also tells me that the "Empno" column cannot be "NULL" which means it must have a value in every row of this table. (Oracle will not allow you to insert the row otherwise.)
Now I describe the "Dept" (department) table:
Code:
scott@PDBORA12C>
scott@PDBORA12C> desc dept
Name Null? Type
----------------------------------------- -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@PDBORA12C>
Note that the "deptno" column is in both the tables. And logically, it makes sense as well.
=> The "Dept" table contains a list of departments, one per row with a Department Number in each row. And I can see that:
Code:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
=> The "Emp" table contains a list of employees, one per row.
Now, each employee should be in a department. That value, in each row, would be in the "Deptno" column of the "Emp" table.
And I can see that too:
Code:
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7499 ALLEN SALESMAN 1600 30 -- ALLEN is in SALES
7654 MARTIN SALESMAN 1250 30 -- MARTIN is in SALES
7782 CLARK MANAGER 2450 10 -- CLARK is in ACCOUNTING
7788 SCOTT ANALYST 3000 20 -- SCOTT is in RESEARCH
So *theoretically* you could join these two tables on the "deptno" column.
Which means your SQL query could be something like this:
Code:
select <column_list>
from emp e, dept d
where e.deptno = d.deptno;
The <column_list> is a comma separated list of columns that you want to fetch from any or both the tables.
I said *theoretically* because there is much more to question - "on which columns should I join two tables?" To answer that, you have to understand the concepts of keys, primary/foreign keys and ER-Diagrams. That's a whole new beast in itself and there's a lot of literature on the Internet for that.
In a well designed system, for the above example, the "deptno" column would be a primary key in the "dept" table and a foreign key in the "emp" table.
So, to answer your questions:
Quote:
... I assumed I could take one of these fields and pull the information?...
In order to join two tables, check for the common columns. And see if those columns make sense logically. If they do, then you could join the two tables on those columns.
If the common columns have a primary key - foreign key relationship, then those are the *only* columns you should use for joining the tables.
Quote:
...Or is this purely just a list of fields?
It's a list of columns in the table with their data types and nullability values. The "describe" command does not tell you if the column is a primary or foreign key.
Please advice to rectify below error
#!/bin/ksh
X=$(sqlplus -s user/pass << EOSQL
set serveroutput on;
set heading off feedback off serveroutput on trimout on pagesize 0
INSERT INTO TEST(df)
VALUES('a');
COMMIT;
EXIT;
EOSQL)
echo $X
echo $?
ERROR at line 2: ORA-00984: column not... (1 Reply)
Hi All ,
I have tried many times am getting syntax error on 'UNION'
can anybody tell me ...
INSERT INTO table1
(
Type
, num_items
, num_letters
, total_value
)
(select
type='1',
num_items,
num_letters=count(*),
total_value=sum(letter_value)
from
table2
where
num_items = 1 (1 Reply)
Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database.
I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :(
What should I do? I'm not sure that... (2 Replies)
Hi Everyone,
Can anyone pls help me out......with my requirement, i am struggling since 3 days. Please find the requirement below
my file contains below data
R1|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|SURFACE MOUNT|AND|8533.10.00.20|8533.10.00.20|
R1|Array/Network Resistor... (9 Replies)
I'm having problems with writing my sql results to a file:
sqlplus -S username/password@DB <<!!
set echo off
set verify off
set showmode off
set feedback off
set timing off
set linesize 250
set wrap off
set pagesize 0
set newpage none
set tab off
set trimspool on
set colsep... (1 Reply)
Hi ,
I used the below script to get the sql data into csv file using unix scripting.
I m getting the output into an output file but the output file is not displayed in a separe columns .
#!/bin/ksh
export FILE_PATH=/maav/home/xyz/abc/
rm $FILE_PATH/sample.csv
sqlplus -s... (2 Replies)
Hi All,
Please help me writing the below script.
I have two sql queries.
1. Select count(1),Client_id from TABLE_A group by Client_id;
2. Select count(1),Client_id from TABLE_B group by Client_id;
I need the output of above two sql queries in a single file. The output 2nd query should be... (4 Replies)
Hi I am new to using pl/sql on a unix platform and am having trouble writing to a file from within a block. Below is an example of the code that I have. I know that I need to use UTL_FILE to accomplish this; however, I keep getting errors. Can someone please help me?
I am trying to create a... (1 Reply)
I have a script that looks for all jobs that contain a particular calendar.
Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this?
I have tried:
select job_name from job where run_calendar='1&15dom'
select job_name from job... (3 Replies)
Hello again... I have a request from another department to list for them all the columns and tables we use in this certain database. I have spooled the oracle stored procedured into 1 file. I need a way to write out parts of that file. The criteria is to to start the block to be written when... (0 Replies)
11-07-2014
