I have products table like this:
productID,producteName,category
now, I need to get last 4 products inserted into products table for every category.
I have to do a number of database exercises and it's my very first time using SQL. I did all of them, except for one query.It's fairly simple, I need to find for every combination of warehouses (there are 5) and items (25) how many of each items I can stock in said warehouse until until it goes over the capacity. So for example warehouse 1 has a max of 300 pounds and item 1 weighs 1 pound, so I can stock 300 in it. Warehouse 1 and product 2...etc.
Every warehouse has a capacity in pounds and every item has a weight also in pounds. There are 3 tables between warehouse and items (warehouse---shipment---order----order details----items).
I managed to find every instance of warehouse_ID/item_ID/(capacity/weight)...but thats for the existing warehouses/item. If they added a new warehouse or product, I'd be screwed, as they wouldnt show up in my query...here's my query:
select Warehouse_ID, item_ID, (select round(capacity/weight,1) as maximum_capacity
from order_detail
join item using (item_ID)
join order using (Order_ID)
join shipment using (shipment_number)
join warehouse using (warehouse_ID)
group by warehouse_id, item_id
order by warehouse_ID
I keep trying to find a way to include possible new warehouses or products in my query, but I'm at a complete loss. I tried to right and left outer joins with no luck
I'm trying to install additional Oracle products on my XP VM, but it is telling me to kill all running oracle products. It states that by running the Oracle utility: oradim80 or the Windows NT service Control Panel I can stop all of these services, but I cannot find either.
View 2 Replies View RelatedTrick is that I've got 4 tables
Sale , sale_details, sales_message, message_text
Table:Sales
Sales_id
Table: Sales_product
Sales_id, sales_product_id
Table: Sales_message
Sales_id, sales_message_id, message_text_id
Table:Message_text,
Message_text_id, message_text
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123
Product1 ID 1234 for sale ID 123
Product2 ID 4321 for sale ID 123
Sales_message ID 098 for product ID 1234 'blabla'
Sales_message ID 876 for product ID 1234 'albalb'
AND NO MESSAGES FOR PRODUCT 4321
This is what I want to get from DB.
I'm trying to create a query which will show sales of products by month, something like this: -
JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015
The data is held on 3 tables: -
TB_ARTICLES
ID
CODE
DESCRIPTION
TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE
[code]....
Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
Is there a way to prevent Apex from combining labels into an "other" category in the charts?
View 4 Replies View Relatedi am trying to insert a table through select statement. if i execute the select stmnt alone its working fine. but the table is not getting inserted?
View 10 Replies View RelatedI have a table where there is no sequence maintained and there is no time stamp column to track the inserted record.How do I find the latest inserted record in the table.
View 5 Replies View RelatedWe have a table, to which daily we insert 100 records. if we want to know recently or today inserted records from that table.
View 3 Replies View RelatedI am using an Oracle Forms application. When you click a check-box in the form and save it,a record is inserted to a log table, they say. The DB schema has over 1000 tables and over50 log tables. Is there way using the data dictionaly to find out which table was inserted tomost recently? I don't have access to the Forms code. My goal is to find out the table that the Form inserted the log entry to.
View 7 Replies View RelatedI have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table
transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)
I want to write SQL which should not inset duplicate record.
e.g.
I have written one as bellow:
insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;
Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)
Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.
I have a table which is getting populated from a source. I dont have a time-stamp column in the table. Is there anyway to find the time when a record was inserted into the table?
View 3 Replies View Relatedthe query to copy the data in last 24 hours from one to another table
Database Version :- Oracle 11g
how to resolve the following issue ...
insert into GTT (select query) inserting less no. of rows than that returned by the query.
The global temporary table has ON COMMIT PRESERVE ROWS.
I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.
View 12 Replies View RelatedI know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.
1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.
2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.
However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.
From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.
I am using oracle developer 10g. I want to know the status of the printer where i want to print. If the running report is printing or in queue then a record is to be inserted into a table as audit-trail of printing. Idon't want to do it manually.
View 1 Replies View RelatedI have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.
i have attached the trigger script. when i execute the table but i shows error like
"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.
I have two tables in which one is partitioned table with the following details.
CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),
[code].....
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198
[code].....
I also tried to upload the same data using sqlldr.
sqlldr log file contents is as follows:
Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37
[code].....
So some sqlldr bad file contents is as follows.
100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,
[code].....
My code format is:
Insert into table1
select val1,val2
from table2,table3
where table2.col2=table3.col2
and....(some more conditions)
I am working with Oracle 10g.This insert statement is called from a procedure for nearly 3000 to 4000 time.Now my problem is even though the select statement selects correct no. of rows but still it insert only 2 or 3 rows.Apart from it ,it's working fine for some cases i.e it insert the actual no. of rows that is being selected from the select statement.
My code format is:
Insert into table1
select val1,val2
from table2,table3
where table2.col2=table3.col2
and....(some more conditions)
I am working with Oracle 10g.This insert statement is called from a procedure for nearly 3000 to 4000 time.Now my problem is even though the select statement selects correct no. of rows but still it insert only 2 or 3 rows.Apart from it ,it's working fine for some cases i.e it insert the actual no. of rows that is being selected from the select statement.
I keep getting the "ORA-01401:inserted value too large for column". No biggie - I've dealt with this multiple times before (but obviously not enough in this instance).
The data being entered is a SINGLE digit number - a number like 1, 2 or 3 - nothing fancy, just a plain straight everyday single digit number. The field in question is / was set as field type "Integer". Now, there is no set field size for integers! - not in Oracle anyway. Since it wasn't happy, I decided I'll try field types of 'Number' and also "Varchar2" set to 10 bytes. I have deleted the column from the table and re-created it as well.
Here's the even more puzzling bit: I can INSERT data into this field, BUT I can not UPDATE the field with the exact same data. The data is being inserted from a csv file. The same exact csv file used to insert works, but the same data in the same file will not update only that particular column.
If I delete the specific column data from the csv file, all goes through fine. If I hard code the update for the field (eg SET field2 = '1' or even SET field2 = ' ') it still doesn't work. So I know it is not the csv file that is causing problems. I deleted all data from the csv file except the field in question - still no luck.
So after eliminating:
1. The field type
2. The field length
3. The data being inserted
4. The external source of the data
What else could possibly be the problem?
i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?
View 4 Replies View RelatedI need select last added/inserted records where value = 'test1' and = 'test2' and = 'test3' in column 'NAME' should be one record for each 'test1','test2' ...
aslo..table have column 'DATE_USR' with data_type = timestamp(6)
We are using Release 11.2.0.3.0 of Oracle. I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.
We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database?
Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.
String - ‡Mw‹O--ggÑ ÆÔéÓÞ³ µmT¤O ˆÓ`ôiyïÎ!Ž
ASCII character is : ‡ ASCII Value Is : 14844065
ASCII character is : ‹ ASCII Value Is : 14844089
ASCII character is : -- ASCII Value Is : 14844052
ASCII character is : ASCII Value Is : 49793
ASCII character is : Ñ ASCII Value Is : 50065
[code]....
I have a table with lot of records.In this table, how do I find, which record is inserted just before a record in my question.
consider this example..
SQL> desc test1
Name Null? Type
------------------------------- -------- ----
SEQ_ID NUMBER
NAME VARCHAR2(1)
[code]....
in the above data, if I want to know what record is inserted before "1018", how can I find it.
Can we find out based on ROWID.?
I have the following tables:
CREATE TABLE test_abc(id NUMBER PRIMARY KEY, col1 NUMBER(10), col2 NUMBER(10), col3 NUMBER(10));
INSERT ALL
INTO test_abc VALUES (1,12345,34567,87654)
INTO test_abc VALUES (2,17345,37567,87754)
INTO test_abc VALUES (3,12745,34767,87674)
INTO test_abc VALUES (4,17045,30567,80754)
INTO test_abc VALUES (5,12740,34067,87604)
SELECT * FROM dual;
CREATE TABLE test_b (id NUMBER, col VARCHAR2(10), coltype_id NUMBER);
What I need to do is to convert the columns col1, clo2, col3 in test_abs into rows in test_b. but I need to do this so that the column name is what determines the value of colytype_id.So in test_b values will look like:
id col coltype
1 12345 1
1 34567 2
1 87654 3
and so on.