SQL & PL/SQL :: Pivot And Similar - Return Zip-codes That Exist?
Dec 29, 2011
I have an question.
- Imagine I have an table with several ZIPCODE.
- Imagine I want to return from that table the existence of several zipcodes I need to get.
- Imagine that I need to return both zipcodes that exists and both zipcodes that not exist.
The solution I've found to do this, was using the Pivot method, like the sample below, if there is another way to return anything like that. Return the zipcodes that exist, and the zipcodes that does not exist also!
Table Creation:
CREATE TABLE "AAA_ADDRESSZIPCODES"
("ZIPCODE" NUMBER,
"NAME" VARCHAR2(50 BYTE),
"COD_PLACE" NUMBER
)
Data:
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1100,'Portugal',2);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1150,'Portugal',2);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1000,'Portugal',1);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1200,'Spain',2);
Select using Pivot:
SELECT *
FROM (SELECT distinct zipcode FROM aaa_addresszipcodes group by zipcode )
PIVOT ( count(zipcode) FOR zipcode IN (1000,1100,1200, 1150, 2000) )
View 3 Replies
ADVERTISEMENT
Sep 24, 2010
I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
View 2 Replies
View Related
May 7, 2011
I am using oracle 10g to create a user register application. what i want is to insert the user information (like Email ,username, password, etc.) into a table. i am able to insert the data into the table but what i want is to check before inserting that same email,and username doesnt exist. if it does it should return some error like the email or username already exist.
View 2 Replies
View Related
Mar 17, 2009
I am writing my first procedure and need to exclude two codes from a list of receipts. I can probaby use the following
and rc_receipt_code in (1, 2, 3, 4, 7, 8)
however can I use <> to eliminate 2 codes for instance can I say
select NVL(sum(rc_amt), 0)
into tot_cont
from trefrc
where rc_filer_seq = filer
and rc_receipt_code <> (5, 6);
View 1 Replies
View Related
Jun 10, 2010
I am using oracle 10g.
I have a query which is used to get the contact details based on the zip code
select * from contacts where
primary_address_postalcode like '65084%' or
primary_address_postalcode like '65011%' or
primary_address_postalcode like '65034%' or
primary_address_postalcode like '65078%' or
primary_address_postalcode like '65050%' or
primary_address_postalcode like '65037%' or
primary_address_postalcode like '65329%' or
primary_address_postalcode like '65072%' or
primary_address_postalcode like '65081%' or
primary_address_postalcode like '65038%'
Result
userid name primary_address_postalcode
5 abc 65072
6 def 65038 -12343
1 xyz 65050
24 pqr 65011
32 test 65011-23455
54 itsme 65329
i want to order the records just same as the zip codes we are using in like (65084,65011,65034, 65078,65050, 65037, 65329,65072, 65081, 65038)
How can i get the result like below
expected result
userid name primary_address_postalcode
24 pqr 65011
32 test 65011-23455
1 xyz 65050
54 itsme 65329
5 abc 65072
6 def 65038-12343
View 15 Replies
View Related
May 22, 2013
I am getting string from my tool like this.... .. ‘ ‘PH1234’,’Ph3456’,’PH5678’ ‘
I wanted to remove single codes and take the each value and I have to process. Need output like this....
PH1234,
Ph3456,
PH5678.
View 4 Replies
View Related
Feb 27, 2013
Is there a way we can wrap pl sql codes and other people will not be able to unwrap it? because I came across this site successfully unwrapped my wrapped pl sql codes. How to just unwrapped pl sql code then what is the use of wrapping?
View 1 Replies
View Related
Nov 26, 2010
I am trying to build a report.My query is working fine when i take out this report for a single area_code.But it is not showing proper result when report is take for all are_code's available in table.I have used two tables transactions and balance
create table transactions ( glcode varchar2(10), area_code varchar2(10), debit number,credit number );
insert into transactions values(2000,'ap',200,200);
insert into transactions values(3000,'ap',222,222);
insert into transactions values(4000,'ap',123,123);
insert into transactions values(2000,'dp',200,200);
insert into transactions values(3000,'dp',222,222);
insert into transactions values(4000,'dp',123,123);
insert into transactions values(2000,'pp',200,200);
insert into transactions values(3000,'pp',222,222);
insert into transactions values(4000,'pp',123,123);
[code]....
View 6 Replies
View Related
Sep 12, 2012
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PS:All the columns are same in all the 3 tables
table1_COLLECTIONS_a
table1_COLLECTIONS_b
table1_COLLECTIONS_c
View 7 Replies
View Related
Oct 17, 2012
In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
View 1 Replies
View Related
Dec 4, 2012
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
View 4 Replies
View Related
Jul 3, 2012
I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.
Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.
INSERT INTO TB_B
SELECT *, sysdate from TB_A;
View 9 Replies
View Related
Jul 5, 2012
I have this sql (generated by discoverer plus), and work fine:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
We need change the table stock_por_sucursal for a view like this, with similar indexes each table
select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new
and the result is not good:
SQL Statement from editor:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,
[code]...
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
View 2 Replies
View Related
Oct 17, 2013
am trying to add a validation in Oracle forms using Oracle DB.in a Oracle table consider there is a record named 'Netbook' then if I try to create another record with name 'Netbooks', then a Oracle warning message should be displayed stating similar name 'Netbook' is already available. same way, if NETBOOKS is already available in table and if user try to create another record NETBOOK then same warning message netbooks is already available should be shown.
View 17 Replies
View Related
Jul 14, 2011
Any good tool similar to Oracle reports builder 6i ?Which should be OPENSOURCE.
View 7 Replies
View Related
Nov 16, 2010
it's possible to retrieve the names of tables that contain similar information in an oracle database.
View 7 Replies
View Related
Aug 21, 2013
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as (
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all
[code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all
[code]...........
View 12 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Apr 16, 2013
How can you use pivot with a parameter? If you attempt to use a parameter then an ORA-500: bind variable not supported is given
Below is a simplified version of the sql to illustrate teh problem.
create table test_pivot
(
ord_no number,
qty number,
loc varchar2(10)
):
insert into test_pivot values (123, 3, 'LOC1');
insert into test_pivot values (123, 2, 'LOC1');
insert into test_pivot values (234, 4, 'LOC1');
insert into test_pivot values (234, 5, 'LOC1');
-- this one is fine
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In ('123', '234')
)
Pivot (Sum(Qty) For Ord_No In ('123', '234') )
-- how to use pivot with parameters
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (:Ord1, :Ord2) )
View 4 Replies
View Related
Aug 28, 2011
I have the following table structure:
PK1,PK2,PK3,A1,B1,C1,A2,B2,C2
Which I want represent as follows:
PK1,PK2,PK3,A1,A2
PK1,PK2,PK3,B1,B2
PK1,PK2,PK3,C1,C2
Is pivot the right command to use? If so, how do I do this? Most pivot examples I've looked at use an aggregate like SUM, which is not really want I am trying to accomplish here.
View 4 Replies
View Related
Sep 24, 2013
pivot the table. Table
dataNAMELINE_TYPE_REF_IDClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserskkondNode NamePB15CKYNum Contacts3ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num Contacts21ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num
[Code]....
View 4 Replies
View Related
Jan 28, 2013
I'm trying to use a PIVOT on the following data set:
ID STATUS_DESC PAY_STATUS PAID_DATE TRANSACTION_TYPE TRANSACTION_DESC DEBIT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 Card Payment Payment 2 349 349
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 Card Payment Payment 1 100 100
However I'm still getting two rows as per the below. Ideally all data should be on a single row.
ID STATUS_DESC PAY_STATUS PAYMENT_1_DATE PAYMENT_1_AMT PAYMENT_2_DATE PAYMENT_2_AMT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 100 100
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 349 349
I have constructed my pivot using the following on the outer select:
PIVOT (MAX (insert_timestamp) AS paid_date
,SUM (debit) AS amt
FOR transaction_desc IN ('Payment 1' AS payment_1,
'Payment 2' AS payment_2)) ;
I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.
View 7 Replies
View Related
Apr 18, 2013
i have a table which looks like this
TRANS_ID TRANS_TYPE CONNECTION_DATE CLOSED_DATE
LK490 4 01-01-2009 01-01-2011
LK490 3 01-01-2010 01-01-2012
SELECT TRANS_ID, TRANS_TYPE, CONNECTION_DATE , CLOSED_DATE FROM TABLE_A
and want to pivot it like this
TRANS_ID 4_CONNECTION_DATE 4_ CLOSED_DATE 3_CONNECTION_DATE 3_ CLOSED_DATE
LK490 01-01-2009 01-01-2011 01-01-2010 01-01-2012
View 3 Replies
View Related
Sep 5, 2011
I have this following data in a single table
Student
Semester
Subject
Marks
and I want to achieve the following.
I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.
new to this type of queries...This is somewhat like pivot..
Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?
View 5 Replies
View Related
Jan 24, 2011
I have a requirement to write a single sql query where i can generate the pivot report. Found some of the examples in Google search. But here we are hard coding the values if it is limited like month in this example.
i want to write similar query to represent the amount based on product type , i have around 200 types of products. I can't write case/ decode statement those many times.
query which will produce the output in pivot format , dynamically depending the number of values.
select Product,
sum(case when Month=�Jan� then Amount else 0 end) Jan,
sum(case when Month=�Feb� then Amount else 0 end) Feb,
sum(case when Month=�Mar� then Amount else 0 end) Mar
from Sales
group by Product
View 3 Replies
View Related
Apr 23, 2007
Is there anyway to pivot the results of a query?
so if i have:
SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP
And it give the following output:
A 10
B 50
c 24
Is there anyway to put into this format?
A B C
10 50 24
I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!
View 1 Replies
View Related
Feb 11, 2013
I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA
Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
View 2 Replies
View Related
Aug 18, 2010
I have a table in the following format:
CTitle1, CTitle2, CTitle3, CTitle 4, CTitle5
Row1Val1,Row1Val2,Row1Val3,Row1Val4,Row1Val5
Row2Val1,Row2Val2,Row2Val3,Row2Val4,Row2Val5
Row3Val1,Row3Val2,Row3Val3,Row3Val4,Row3Val5
Row4Val1,Row4Val2,Row4Val3,Row4Val4,Row4Val5
Row5Val1,Row5Val2,Row5Val3,Row5Val4,Row5Val5
I have an application that requires the data to be in the following format:
CTitle1, Row1Val1,Row2,Val1,Row3Val1,Row4Val1,Row5Val1
CTitle2, Row1Val2,Row2,Val2,Row3Val2,Row4Val2,Row5Val2
CTitle3, Row1Val3,Row2,Val3,Row3Val3,Row4Val3,Row5Val3
CTitle4, Row1Val4,Row2,Val4,Row3Val4,Row4Val4,Row5Val4
CTitle5, Row1Val5,Row2,Val5,Row3Val5,Row4Val5,Row5Val5
So I am truly trying to transpose the columns and rows and not doing a traditional pivot. How I can do this in Oracle 9i?
View 9 Replies
View Related
Jul 19, 2013
I have a table tab1 which contains columns Cust, ProdSeq, StartDat, EndDat, AttrId, AttrValue. I can have different attributes represented by attrId (say 1,2,3,4..etc.,) and its value by AttrValue respectively.
My question is for a given a customer, prodSeq and date range say 01-Jan-2013 to 31-Jan-2013, I want a report like below
Example input data
----------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT |ATTRID |ATTRVALUE |
----------------------------------------------------------------
|C1 | 1 | 1/1/2013 | 1/15/2013 | 1 | 1 |
|C1 | 1 | 1/1/2013 | 1/10/2013 | 2 | A |
|C1 | 1 | 1/1/2013 | | 3 | X |
|C1 | 1 | 1/1/2013 | | 4 | G |
|C1 | 1 | 1/11/2013 | 1/31/2013 | 2 | B |
|C1 | 1 | 1/16/2013 | 1/20/2013 | 1 | 2 |
|C1 | 1 | 1/21/2013 | 1/31/2013 | 1 | 3 |
----------------------------------------------------------------
Expected output
----------------------------------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT | ATTR1VAL | ATTR2VAL | ATTR3VAL | ATTR4VAL|
----------------------------------------------------------------------------------------
| 1 | 1 | 01-JAN-2013 | 10-01-2013 | 1 | A | X | G |
| 1 | 1 | 11-JAN-2013 | 15-01-2013 | 1 | B | X | G |
| 1 | 1 | 16-JAN-2013 | 20-01-2013 | 2 | B | X | G |
| 1 | 1 | 21-JAN-2013 | 31-01-2013 | 3 | B | X | G |
----------------------------------------------------------------------------------------
View 1 Replies
View Related
Dec 6, 2010
This is how data is in table.
SQL> SELECT order_num, jcds.change_num, jcds.object_status, jcds.time_changed
2 FROM tjcds_f jcds
3 WHERE change_num = '001'
4 AND object_status IN ('E0001', 'I0002', 'I0009', 'I0028')
5 and order_num='000001000001';
ORDER_NUM CHANGE_NU OBJECT_STAT TIME_CHANGED
------------ --------- ----------- --------------------------------------------
000001000001 001 I0002 01-NOV-10 02.52.56.000000 PM
000001000001 001 I0009 01-NOV-10 09.30.20.000000 AM
000001000001 001 I0028 01-NOV-10 10.36.32.000000 AM
Needed Output:
ORDER_NUM CHANGE_NU E0001 I0002 I0009 I0028
------------ --------- ----------- ------------------------------- ------------------------------ ---------------------------------
000001000001 001 01-NOV-10 02.52.56.000000 PM 01-NOV-10 09.30.20.000000 AM 01-NOV-10 10.36.32.000000 AM
My Query:
SELECT order_num, jcds.change_num,
decode(jcds.object_status,'E0001', jcds.time_changed) E0001 ,
decode(jcds.object_status,'I0002', jcds.time_changed) I0002,
decode(jcds.object_status,'I0009', jcds.time_changed) I0009,
[code]...
how do i get the above requirement in one row? is it possible.
View 5 Replies
View Related