PL/SQL :: Use Two Temporary Tables To Assign Color To Data Results?
Jul 16, 2012
Example Tables and Data:
I have 5 Courses, each course has 2 assignments.
with Courses as (
select 100 courseID from dual union all
select 200 from dual union all
select 300 from dual union all
select 400 from dual union all
select 500 from dual
), Assigns as (
[code]...
I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.
--Color table with 6 colors
with Colors as (
select 'red' color from dual union all
select 'blue' from dual union all
select 'yellow' from dual union all
select 'green' from dual union all
select 'purple' from dual union all
select 'teal' from dual
)
View 8 Replies
ADVERTISEMENT
Jan 17, 2011
I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.
DB1 uses some Global Temporary tables to write and store session level information.
I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.
DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.
Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.
View 1 Replies
View Related
Feb 2, 2011
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
View 3 Replies
View Related
Apr 18, 2013
There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.
I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!
These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';
[code]...
C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.
View 4 Replies
View Related
Nov 11, 2010
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
View 9 Replies
View Related
Aug 6, 2010
I'm trying to leave out results of a query that match certain conditions, basically I'm looking at two tables that each have a status field. If the status of both is equal to zero then I want the result to be left out of the search results. I'm struggling with the logic though. In the where clause I've tried:
(ACCO.STATUS != '0' AND CCO.STATUS != '0')
this, however, omits everything where the status combination isn't 1 and 1. I've also tried:
(
(ACCO.STATUS = '1' AND CCO.STATUS = '1') OR
(ACCO.STATUS = '1' AND CCO.STATUS = '0') OR
(ACCO.STATUS = '0' AND CCO.STATUS = '1')
)
but this does not work either.
View 6 Replies
View Related
Jan 4, 2012
I'm trying to change the attributes on a display where the info is gathered via a routine that fills the data block with info. I've searched and tried using 'set item instance property' in several places within the data block using different triggers but still doesn't work.
No errors are encountered or shown. Attributes aren't changed based on a small test condition.
View 2 Replies
View Related
Oct 16, 2013
I have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type
TYPE xx_delivery_detail_rectype IS RECORD ( p_delivery_id NUMBER, p_ultimate_dropoff_location_id NUMBER, p_creation_date DATE, p_last_update_date DATE, p_container_name VARCHAR2 (30), p_inventory_item_id NUMBER, p_shipped_quantity NUMBER, p_shipment_line_id NUMBER ); TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype INDEX BY BINARY_INTEGER;
Say i have variables to assign to all the values within the record type, How do i do it within the package
View 4 Replies
View Related
Apr 26, 2011
Is there a neat way other than having to reformat the data in a temporary table to do the following,I've got the following content in a table:
CODECustid Type Nb Amount
1 Deposit 2 10000
1 Withdrawal 1 4000
I'd like to show the data in this manner:
CustID Deposit DepositAmount Withdrawal WithdrawalAmount
1 2 10000 1 4000
View 3 Replies
View Related
Nov 15, 2011
Can we lock data in global temporary table?
View 4 Replies
View Related
Nov 7, 2012
how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.
CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE
[code]...
why insert into table temporary not successfully in job_submit.
View 3 Replies
View Related
Jun 25, 2012
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.
View 1 Replies
View Related
Feb 9, 2009
I want to confirm the order of the data in results of two sql queries.
For example, in my table 5 columns are there.c1 to c5.
c1 c2 c3 c4 c5
--------------------------------------------------
test100 1 A 1-Jan-2006 2-Jan-2006
test100 2 B 3-jan-2006 4-Jan-2006
test101 1 A 01-Jan-2006 2-Jan-2006
test101 2 B 03-Jan-2006 4-Jan-2006
test101 3 c 05-Jan-2006 06-Jan-2006
In this table if you order by c2,
select c3
from tab1
where c1='test101'
order by c2;
c3
---------
A
B
C
If you order by c4,
select c1,c2,c3
from tab1
where c1='test101'
order by c4;
c3
------
A
B
C
Sometimes order of these data may change like. the result of the 2nd query may change (bcos of c4 date column changes)
C3
--
A
C
B
So,@ run time i want to check the order of these 2 results should match....if not,i need to display the corresponding c1 column.
View 3 Replies
View Related
Jul 14, 2010
I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.
----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);
-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');
SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today
Attached File(s)
create_Function.zip ( 6.39K )
Number of downloads: 1
InsertData.txt ( 84.34K )
Number of downloads: 0
View 1 Replies
View Related
Apr 10, 2013
I am new to writing queries for an oracle database and I was giving a bit of challenge. Here's the situation. I have 3 tables I am using. 2 of the tables are being used to transpose people's names from rows to columns by account number (there are multiple people associated with each account). The last table is a pretty straight forward query. I can run each query by itself and I get the results I want. But when I try to compile the two together, I start getting a variety of errors. Below is the two queries:
Query 1 (returns about 1,500 rows):
SELECT DISTINCT CAST (EIS_DW.ACCTCOMMONLOAN.ACCOUNT as VARCHAR(20)) as ACCOUNT_NUM,
EIS_DW.ACCTCOMMONLOAN.ACCOUNT_STATUS,
EIS_DW.ACCTCOMMONLOAN.MINOR_DESCRIPTION,
EIS_DW.ACCTCOMMONLOAN.OWNER_NAME,
[code]......
Query 2 (returns about 570 rows):
SELECT ACCTNBR,
max(DECODE (rn , 1, FULLNAME)) GUARANTOR_1,
max(DECODE (rn , 2, FULLNAME)) GUARANTOR_2,
max(DECODE (rn , 3, FULLNAME)) GUARANTOR_3,
max(DECODE (rn , 4, FULLNAME)) GUARANTOR_4,
[code]....
Ideally, I want to join these two queries on ACCOUNT and ACCTNBR. I have tried working my first query into my second query, but the best I get with that, is the 570 or so accounts, not all the accounts.
View 4 Replies
View Related
May 28, 2011
how can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions
View 12 Replies
View Related
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
View Related
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
Oct 11, 2012
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies
View Related
May 30, 2011
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies
View Related
Sep 3, 2010
what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.
View 10 Replies
View Related
Dec 19, 2011
I had as source a set of actions where I have to assign the end date, the general rule is that each action has the same end date to start date of next action, if there is no next action it will be assigned the default('35001230').
I can do the assignment with the date of order:
NVL (LAG (DAT_INI) OVER (PARTITION BY ID_CLIENT DAT_INI ORDER BY DESC, DESCCOD_SEVERITY), TO_DATE ('35001230 ',' YYYYMMDD ')),
However, I have an exception to the rule, if the action have COD_SEVERITY = 3 or 4 and the next action have the COD_SEVERITY = 3 or 4, the end date will be for the previous day. If in the same day I have actions with COD_SEVERITY 3 and 4, the end date of actions with COD_SEVERITY = 3 will be DAT_INI -1 and the action with COD_SEVERITY = 4 will have the same date of the next action or DAT_INI -1 if the next action had COD_SEVERITY = 3 or 4
Input
ID_CLIENTDAT_INICOD_SEVERITYVALUE
1201112011100
1201112013100
1201112044101
1201112073100
[code].......
Output except
ID_CLIENTDAT_INICOD_SEVERITYVALUEDAT_END
120111201110020111201
120111201310020111203
120111204410120111206
120111207310020111213
[code]........
The source
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 1 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 3 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111204 as DAT_INI, 4 as COD_SEVERITY, 101 as VALUE FROM DUAL
UNION ALL
[code].......
View 1 Replies
View Related
Mar 20, 2013
I have written this code, its throwing error.
begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;
View 10 Replies
View Related
Aug 12, 2011
Can i set individual tab page bar color?
Like the attached screenshot..
View 3 Replies
View Related
Mar 13, 2012
i want to change the text background color as red ex: if desc is the field in the forms and when i type the word china in that field then that china backgroud color should change when i move to next item to red for that record alone..in the key_next_item trigger i wrote this
set_item_property('desc' backgroud_colour,'RED');
IS THIS RIGHT only that particular record should change
View 14 Replies
View Related
Aug 30, 2012
I have a problem while changing in a form canvas color
i make some modification in a form. The layout color of the canvas is blue. i want to change the layout color of it to default (<unspecified>) (which is the default color).
when i change in the property pallet the background color to <unspecified>. it give me that error FRM-12305 color by this name doesn't exist and i want it the default color.
how can i do it without recreating the form from the beginning.
View 2 Replies
View Related
Nov 24, 2011
How can i change my text box into a transparent background?Any color code for it? I'm using Form 6i
View 5 Replies
View Related
Mar 3, 2010
I want to display may drop down list on different colors. Let's say I have 3 choices on list,
I want choice 1 to have a blue background, red for choice 2 and green for choice 3.
View 3 Replies
View Related
Jun 25, 2011
My requirement is, when ever cursor entered into a field, the value of the field will highlight with Blue color and text color will be white, if field is not empty. It the feature of oracle application.
View 5 Replies
View Related
Apr 12, 2010
The Background colour of the canvas is "gray" i want to change it to default.
View 4 Replies
View Related