SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?
Mar 6, 2012
To get output from a below mentioned table? can we combine a row like that?
Table: Test_Fruits
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------
View 6 Replies
ADVERTISEMENT
Aug 13, 2010
If I have below two sql statment.
select count(*) from table_a;
select count(*) from table_b;
How can I output it in one row together like below
Table_a | Table_b|
10 | 20|
give me detailed example ....
View 2 Replies
View Related
Jun 8, 2011
how to combine 2 queries as attached in notepad file in order to get full output?
View 3 Replies
View Related
May 11, 2012
Is it possible to combine both these selects into one.?
SELECT a.grouping_set_member_id,
a.service_agreement_id,
a.enrolment_group_id,
a.enrolment_group_class_id,
a.effective_date,
[code]...
Where l_service_agreement_id,l_enrolment_group_id,l_ENROLMENT_GROUP_CLASS_ID,l_effective_date
are vaues from the First SELECT.
View 2 Replies
View Related
Jun 5, 2012
SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........
View 5 Replies
View Related
Mar 4, 2011
I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:
ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37
if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC
[Code]....
View 7 Replies
View Related
Jun 21, 2010
I have 4 select queries.
Query1
Query2
Query3
Query4
First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.
Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.
Third Step: Is to now combine both the data set from First Step and Second Step.
In order to achieve my First and Second Steps I used WITH clause.
With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID
With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID
1. Is that approach right for achieving my First and Second step requirements ?
2. How to achieve Third Step ?
View 3 Replies
View Related
Nov 24, 2009
I have a query more or less like this:
SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;
For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.
So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3
I want results like this:
ABC | 15
XYZ1 | 3
I've tried using some case statements like
SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;
but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3
How can I combine record 1 and 2 from the last record set example above?
View 4 Replies
View Related
Jul 23, 2012
i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).
now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!
here i have some part from the original
declare
procedure add(
i_id pls_integer,
[Code].....
View 3 Replies
View Related
Dec 10, 2010
ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.
1)
SELECT
COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL,
COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,
[Code]....
2)
SELECT
SUM (CHEQUE_TOTAL) CHEQUE_TOTAL
FROM RI_CHEQUE_VOUCHER_REFUND refund
INNER JOIN CH_CASE case ON (case.id = refund.id)
INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED')
WHERE
case.creation_time<= SYSDATE
AND
case.creation_time>= SYSDATE-7
Again I need to combine the resultset.So the result would look like
Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total
View 3 Replies
View Related
Sep 13, 2007
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
[code]....
View 1 Replies
View Related
Mar 5, 2013
I have the following table
PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
0220unknown
011200unknown
021240unknown
025300unknown
031340unknown
[code]...
If code value is same i would like to combine prefix,sfirst suffix1 and last suffix2. So from above table i want something like the following table.
PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
022410unknown
02421426UKred
View 11 Replies
View Related
Jul 1, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
View 2 Replies
View Related
Sep 20, 2012
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....
View 12 Replies
View Related
Dec 15, 2010
I am exploring the possibilities of getting table output or query output to an XML file.
View 3 Replies
View Related
Oct 23, 2012
declare Input VARCHAR(10) :='011000141'
declare rt StringOutput%rowtype;
begin
SELECT SUBSTR(Input , SumOfLength - ColLength + 1, ColLength) FinalOutput INTO rt FROM mytable ;
end;
/
I need to store the result to a temp table rt.
View 10 Replies
View Related
Jan 25, 2013
i have three tables and all of these tables have around 30L records.
Using join i am retrieving records from these tables but it is taking much more time to get output.
Partition can improve performance?
View 7 Replies
View Related
May 19, 2011
I have a case expression as follows:
(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa
I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.
View 1 Replies
View Related
May 28, 2013
Does trcsess combine various trace files in the order in which the queries were executed?
View 3 Replies
View Related
Dec 27, 2012
I've created a type as
CODETYPE TEST_DWH_PROD AS OBJECT
(WO_RELEASE_DATE DATE,
SITE_CODE__MFG CHAR(5),
SITE_CODE__PARENT CHAR(5),
ORDER_TYPE CHAR(3),
MFG_TYPE VARCHAR2(10),
[code]....
the above query is giving the value of current QTD(on the basis of V_REPORT_DATE)..I need to integrate the same as user want the output as table type and he can also give some filter conditions.
View 1 Replies
View Related
Dec 9, 2011
I am stuck with this query.
I have a table "xyz" as -->>
SQL> select * from xyz;
A B
---------- ----------------------------------------
1 Hello
2 Hello
3 No Hello
4 No Hello
5 Hello
6 Hello
7 Hello
I want to print the output of this table as -->>
A B
---------- ----------------------------------------
1
2 Hello
3
4 No Hello
5
6
7 Hello
To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.
Either of SQL or PLSQL would d for me.
View 11 Replies
View Related
May 18, 2011
how can i get the output format from given input table
View 2 Replies
View Related
Aug 15, 2012
I am searching for a decent method / example code to subdivide a large table (into a global temp table (GTT) for further processing) based on a list of numeric/alphanumeric which is the resultset from a view.
I am groping with the following strategy in PL/SQL:
1 -- set up cursor, execute the view (so I have the list of identifiers)
2 -- create a second cursor (or loop?) which: accepts each of the identifiers in turn executes a query (EXECUTE IMMEDIATE?) on the larger table INSERTs (or appends?) each resultset into the GTT
3 -- Then the GTT contains just the requires subset of the larger table for further processing and eventual import into iReport for reporting.
GTT is defined and ready to go, the larger table contains approx 40,000 rows and I need to extract a dozen subsets or so which add up to approx 1000 rows.
View 10 Replies
View Related
Jan 19, 2011
I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.
//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT
[Code]....
View 8 Replies
View Related
Jun 26, 2013
I have a report which comes from a table-function. This produces hundreds of lines of output which the user would like to be able to select and then export just the subset of lines.Looking at the Forum's (this article and this one) this seems possible if you are working with a table where you can re-query the original dataset with just a specific rowset required however as the table-function is generating output on the fly it is hard to rerun the query to reselect the same output for redisplay. Also most of the reports are working with interactive reports - whilst this is just static output. Is there a way to redisplay the information which has already been shown in an filtered way with check boxes?Is there then a way to have hidden fields which are not shown when the check boxes are selected but which can be shown in the "filtered" view? I am using Apex 4.0 at the moment on an Oracle 10g instance.
View 0 Replies
View Related
Sep 25, 2013
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
View 6 Replies
View Related
Aug 3, 2011
How can I write a query to combine amount1 and amount1 into single column amount?
create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);
[code]....
View 7 Replies
View Related
Mar 11, 2013
Following query is giving me the required output but i want to use reg_exp from same output.
SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH
[code]...
Required Output using Reg_Exp
SQL>/
Bill No
----------
21
9
View 6 Replies
View Related
Feb 2, 2010
One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
[code]....
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
View 9 Replies
View Related
Jan 12, 2012
Here i have one PL/SQL block which will returns the age of an employee using his id.
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER;
v_days NUMBER;
[code]...
It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer
DECLARE
v_num NUMBER;
v_days NUMBER;
BEGIN
SELECT to_date(sysdate)-to_date(dob)
INTO v_num
FROM customer_details
WHERE application_id=:block3.day;
v_days :=floor(v_num/366);
MESSAGE(v_days);
MESSAGE(v_days);
END;
It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.
View 10 Replies
View Related
