SQL & PL/SQL :: Pulling All Records From The Customer Table?
Nov 21, 2012
I am writing a query to give me all CustomerID's in the report. I will give you the tables that I am trying to get the query from.
1. Customer table:
Custid (pk)
memberid
fname
lname
bname
2. Cust_cokeid table:
Cokeid (pk, fk)
Custid (pk, fk)
3. Cokeid table:
Cokeid (pk)
[code]....
Here is the query I have wrote:
SELECT customers.custid AS CUSTID,
customers.memberid AS MEMBERID,
customers.bname AS BNAME,
drpepper_rebate.drpepid AS DRPEPID,
drpepper_rebate.totalcarb AS TOTALCARB,
drpepper_rebate.totalncb AS TOTALNCB,
[code]....
I have more then 700 customer records in the Customers table, but so far I can only pull 500 records.
Reason I am trying to pull all the records from the customer table is because I want to find out who is missing cokeid and Drpepid.
View 7 Replies
ADVERTISEMENT
Nov 5, 2013
We are running Oracle 10g. I need to pull the DDL information from our Oracle tables. The following SQL statement returns the result as a "HUGECLOB". Is there a way to convert the result to text in varchar2 data type? Since tables being processed have numerous partitions, the DDL information for them is quite large, therefore, using substring would not be a viable alternative.
[code]select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE') from DUAL; [/code]
View 6 Replies
View Related
Dec 20, 2010
I am trying to insert data from tables that I build from a stage table. I have pasted my package body code below and error message.
create or replace
PACKAGE BODY etl_contact_sales
IS
PROCEDURE insert_contact_sales
IS
start_dt DATE := sysdate;
v_error_message varchar2(150);
v_error_block varchar2(30);
v_exception exception;
v_process_name varchar2(30);
[code]...
View 11 Replies
View Related
Dec 7, 2011
I'm trying to write a procedure that displays customerID, customer name, product name, and the total quantity of products the customer purchased, and the total amount the customer paid.Here's the relevant Schema tables:
CREATE TABLE Product (
Product_ID NUMBER(6) NOT NULL,
Description VARCHAR2(30),
Product_Code CHAR(4));
[code]....
Now I'm trying to wrap the above query in procedure code. I believe that I need a cursor, but I don't know what kind of cursor variable to store the result of the SELECT statement in because the query selects columns from several different tables, and I'm not sure how to terminate the FOR loop (but I think probably I can use the EXIT WHEN cursor%NOTFOUND;Here's the procedure code I have written thus far:
CREATE OR REPLACE PROCEDURE find_customer_statistics IS
DECLARE
TYPE cust_stats IS REF CURSOR; weak ref cursor declaration
SELECT sales_order.customer_id, customer.name, product.description, SUM(line_item.quantity),
SUM(line_item.subtotal)
FROM sales_order, customer, product, line_item
WHERE customer.customer_id = sales_order.customer_id
AND line_item.order_id = sales_order.order_id
[code]....
View 7 Replies
View Related
Aug 14, 2013
There are two oracle inventory tables, I found that used to pull up data before but not anymore . It is
�INVFV_INV_BY_PERIOD_AND_ITEM�
and �MTL_BIS_INV_BY_PERIOD� .
I need to use all the columns/data that are in these two tables. I am not finding any equivalent table. where can I retrieve these information from?
Select * from INVFV_INV_BY_PERIOD_AND_ITEM;
Select * from MTL_BIS_INV_BY_PERIOD;
View 3 Replies
View Related
Apr 23, 2012
We want to provide data dump from 3 tables Like A,B,C.
In C we have more than 3 million records.
What is the best way to move this data from our DB to customer DB.
Customer is running on MySql
View 12 Replies
View Related
Nov 19, 2010
i WANTED TO SIMPLIFY THIS CODE SO THAT I NEED NOT DO ALL THESE manipulations but still get the result of
UNI_CUG_SITE,
Create Table CUG_SITE compress nologging As
Select C_Key, S_Key From A
group by C_Key, S_Key
/
commit
/
--Creating a table for all Single Sited Customer ---
[code]........
View 5 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Dec 1, 2009
I have an alerts system for a web site built in an Oracle DB. There are multiple alerts that can be assigned per page, but I want to display only the most recent. I have 5 pages but each page can have different alerts.
I have a parent table of all the alerts and a child table with the page assignments. I want to pull one record (the newest) for each page assignment. The pages can increase so I can't hardcode this.
I keep thinking I need to do something like:
SELECTalertID, headline
FROMalerts_V
WHEREalertID IN (
SELECTdeptID
FROMso_departments
)
But it's obviously not quite what I need and not the correct syntax.
View 5 Replies
View Related
Jun 1, 2010
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
View 9 Replies
View Related
Mar 28, 2013
I'm doing online business of selling my manufacturing product to my customer through internet. It is so much easy way but with this there are some unwanted threads that might be disturb business working area. I want to keep my customer online data from hacker but have no strong software who minimize or protect them hackers.
View 1 Replies
View Related
Oct 29, 2008
wish to do a database on dvd rental in oracle following: for the ER diagram i have indetified the entities as : branch , emp, customer,dvd, rental...
1) set the one - many relationship in the above data
2) identify the attributes for customer,dvd and rental tables and thus normalise them as well.
View 2 Replies
View Related
Nov 25, 2012
the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... )
This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.
Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.
To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme
View 6 Replies
View Related
Nov 3, 2010
i'm working on procedure to send invoices to customer thru mails.
i'm using UTL_Mail package to send html content in a mail , it's working if i'm using my company mail exchange BUT if i put "xxxx@hotmail.com" or "xxxx@yahoo.com" it doesn't work and raise
error :ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for xxxxx@hotmail.com
View 10 Replies
View Related
Dec 20, 2012
have a column cstomer_ts from table customer, which contain records like:
"cstomer account"||"PRIVATE"||"foundation"
"cstomer account"||"PRIVATE"||"foundation of money"
"cstomer account"||"PRIVATE"||"Moneycost"now i want to fetch the third record from the column cstomer_ts which starts with 'Foundation'
i mean i want to fetch only 3rd postion frm the column cstomer_ts and tht should starts with 'Foundation'
View 9 Replies
View Related
May 26, 2010
How to Print Customer Copy / Office Copy in rports 6i for example i want to print two copies of any report i want that on first copy of report shows CUSTOMER COPY and second copy of shows OFFICE COPY and report may be on multiple pages.
View 1 Replies
View Related
Feb 16, 2012
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
View 6 Replies
View Related
Sep 13, 2013
how to write this in sql command lineDisplay CUSTOMER number, name, rep, balance, and credit limit for all CUSTOMERs of Rep 20.
In addition, display this information for CUSTOMERs of rep 65 who have a $10,000 credit limit. Arrange the output by credit limit descending within rep ascending. no more than two decimal digits
View 3 Replies
View Related
Feb 7, 2012
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
View 7 Replies
View Related
Jul 17, 2013
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
View 2 Replies
View Related
Mar 25, 2013
following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
View 1 Replies
View Related
Mar 9, 2004
Trying to auto insert the newest records from one table into another Table. I have a vendor provided table that is part of my database (running Oracle 9i) so I can't change the underlying structure to it or their process stops fluxing. However, I can add a trigger to it. What I want to do is this:
When the vendor's software inserts a new row (through their own automated process) I want to insert data from that same new record into another table of my own. (where of course I can re-format it, etc., and make the data my own)
The original vendor table does not have a insertion timestamp field to work off of.What is the best way to trigger an insert off the latest inserted record? It works to replace all the records in the entire vendor table but I only want to insert one record at a time.
View 2 Replies
View Related
May 14, 2011
I am trying to create a procedure that will send in the customer id, password and new password. The procedure will say if it has been updated or not. this is the code i have.
--Patty Carson
--Assignment 3 Question 3
--Description: Allows the user to change a password for a customer
CREATE or REPLACE procedure ChgPwd
(custid OUT number, password OUT VARCHAR2, newpassword IN VARCHAR2)
AS
[code]...
View 15 Replies
View Related
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
View 5 Replies
View Related
Apr 21, 2011
I have requirement like we need to send records from one table to another table. for example if i have 4 records in Table A , first i need to send only 2 records to Table B then again rest 2 records to the Table B.
View 10 Replies
View Related
Aug 10, 2011
I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".
which is the best option to make most efficient search --
1) rownum < 201
2) row_num()
3) rank/denserank etc
View 3 Replies
View Related
Jun 23, 2010
I am dealing with a table containing millions of records. I have table loans_list table and he data looks similar to this..
LOAN_IDSEQUENCE_NUMCOMPLETE_DATE
1237000
1237005
1237010
5237010 6/23/2010 10:07:02 AM
5237000 6/23/2010 10:07:02 AM
12237000
I am trying to select only those loan_id from this table which contain all these 3 sequence_num = 7000,7005,7010 and containing null compelete date. I tried different way to write the query but can't think of efficient way of writing this query yet.
Since this table contain million of records, i dont prefer to call this table more than once in a query. I am just trying to avoid the longer time delay for the execution of this query..
View 7 Replies
View Related
Mar 16, 2012
We have a table which gets updated on a daily basis. We need to manage the records with the retention period of 30 days and move the remaining records to another table.
I understand that we can use "select insert" statement and then the "delete" statement to achieve this. Is there any sql to directly move the records to another table rather than inserting and then deleting?
View 1 Replies
View Related
Jul 18, 2012
I want to insert 10 records from table a to table b. If i m using statement level trigger how many record insert?In row level trigger how many record inserted?
View 2 Replies
View Related
Oct 3, 2013
how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
View 3 Replies
View Related
