Rows not populating [message #657687] |
Fri, 18 November 2016 12:12 |
|
krishna.orcl97
Messages: 4 Registered: November 2016
|
Junior Member |
|
|
created a table RAW_Table, which has two primary keys SEARCHID and SERVICE_INVOKED.
When the program runs, my database updates SEARCHID. This is unique and for every SEARCHID generated, there can be multiple SERVICE_INVOKED.
The table should have an individual row for every SERVICE_INVOKED. At the moment, only one row is ever populated, even if there are multiple services invoked. What should I do? Here is the SQL query for creating the RAW_TABLE.....
CREATE TABLE "SAT_RAW_TABLE"
(
"SEARCHID" VARCHAR2(20 BYTE) ,
"USER_NAME" VARCHAR2(100 BYTE),
"SAT_NAME" VARCHAR2(100 BYTE),
"ADDRESS" VARCHAR2(100 BYTE),
"CITY" VARCHAR2(50 BYTE),
"STATE" VARCHAR2(10 BYTE),
"ZIPCODE" VARCHAR2(20 BYTE),
"TERM" VARCHAR2(20 BYTE),
"BANDWIDTH" VARCHAR2(20 BYTE),
"SERVICE_INVOKED" VARCHAR2(50 BYTE) ,
"SERVICE_REQUEST" CLOB,
"SERVICE_RESPONSE" CLOB,
"INVOKED_DATETIME" date,
PRIMARY KEY (SEARCHID,SERVICE_INVOKED)
);
To fix this, we ran this script.
ALTER TABLE SAT_RAW_TABLE DROP CONSTRAINT SYS_C001274759;
ALTER TABLE SAT_RAW_TABLE ADD CONSTRAINT SYS_C001274759
PRIMARY KEY ( SEARCHID , SERVICE_INVOKED )
ENABLE;
The result should look something like this,
SearchID Username SAT-NAME SERVICE INVOKED
100 ABCD SAT1 Service1
100 ABCD SAT1 Service2
100 ABCD SAT1 Service3
200 PQRS SAT2 SERVICE1
200 PQRS SAT2 SERVICE2
But I can see only the last Service in the table, but all the services from service_invoked should have multiple rows for each SearchID
Thanks
|
|
|
|
Re: Rows not populating [message #657689 is a reply to message #657687] |
Fri, 18 November 2016 12:22 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Align the columns in result.
Quote:created a table RAW_Table, which has two primary keys SEARCHID and SERVICE_INVOKED
No you have one primary key with two columns. A table can't have more than one primary key.
The result you showed is from which query?
If you have only the last service maybe this is because there is only the last service in the table.
Quote:At the moment, only one row is ever populated, even if there are multiple services invoked.
So instead of UPDATE use INSERT.
Are you sure that
"USER_NAME" VARCHAR2(100 BYTE),
"SAT_NAME" VARCHAR2(100 BYTE),
"ADDRESS" VARCHAR2(100 BYTE),
"CITY" VARCHAR2(50 BYTE),
"STATE" VARCHAR2(10 BYTE),
"ZIPCODE" VARCHAR2(20 BYTE),
"TERM" VARCHAR2(20 BYTE),
"BANDWIDTH" VARCHAR2(20 BYTE),
depends on SERVICE_INVOKED?
I think the table should be split in 2.
Read Normalization.
|
|
|
|
|
|
|
|
|
|