Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 12 c)
SQL Query [message #668332] |
Mon, 19 February 2018 01:13 |
|
Deep_80
Messages: 5 Registered: February 2018
|
Junior Member |
|
|
Hello Guru's
I am bit new to SQL queries, I need to create a report for a user, Who provided me a spreadsheet which has over 500 rows from a large table ( over 1 million records)
The spreadsheet contains Trans_no, Date, Currency, Zone ( 500 Rows) example is given below
Trans_no , Date, Currency, Zone
1011 , 12-03-1998, EURO , EUR
1011 , 12-03-1998 , DOLLOAR, NA
1033 , 10-12-2016 , POUND, EUR
XXXX , XXXXXXXXXX , XXXX, XXX
XXXX , XXXXXXXXXX , XXXX , XXXX
Like I mention , The spreadsheet has 500 rows , I need to write a sql to fetch the records from the table ( Status, Client name, address, Fees , date of submition, etc which satisfy the conditions such as TRANS_NO = AND DATE ='12-03-1998 ' , CURRENCY ='EURO ' AND ZONE ='EUR' ( for all the 500 rows)
We can easily write for couple of rows, how to write a sql which satisfy all the conditions of the 500 rows.
Please give some ideas.. Thanks in advance.
|
|
|
|
|
Re: SQL Query [message #668339 is a reply to message #668332] |
Mon, 19 February 2018 02:54 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you are new to SQL, one thing you must realize straight way is that it is a strongly typed language. You have this:
Quote: DATE ='12-03-1998 '
What data type is DATE? You are comparing it to a string. Oracle is pretty good at doing implicit type conversions when developers code bugs like comparing dates with strings. But you should never rely on this. At the most basic level, is that date the third of December or the twelfth of MArch?
--Update: and of course DATE is a reserved word that you cannot use as a column name.
[Updated on: Mon, 19 February 2018 02:55] Report message to a moderator
|
|
|
Re: SQL Query [message #668349 is a reply to message #668335] |
Mon, 19 February 2018 04:25 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
So maybe something like:
SQL> create table z (
2 trans_no integer,
3 trans_date date,
4 trans_currency varchar2(20),
5 trans_zone varchar2(5)
6 )
7 organization external (
8 type oracle_loader
9 default directory my_dir
10 access parameters (
11 records delimited by newline skip 1
12 badfile 'z.bad'
13 logfile 'z.log'
14 nodiscardfile
15 fields terminated by ',' lrtrim missing field values are null (
16 trans_no,
17 trans_date date "DD-MM-YYYY",
18 trans_currency,
19 trans_zone
20 )
21 )
22 location ('z.txt')
23 )
24 reject limit unlimited
25 /
Table created.
SQL> host type c:\z.txt
Trans_no , Date, Currency, Zone
1011 , 12-03-1998, EURO , EUR
1011 , 12-03-1998 , DOLLAR, NA
1033 , 10-12-2016 , POUND, EUR
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered.
SQL> select * from z;
TRANS_NO TRANS_DATE TRANS_CURRENCY TRANS
---------- ---------- -------------------- -----
1011 12/03/1998 EURO EUR
1011 12/03/1998 DOLLAR NA
1033 10/12/2016 POUND EUR
3 rows selected.
SQL> select * from z
2 where TRANS_NO = 1011
3 AND trans_DATE = date '1998-03-12'
4 and trans_CURRENCY = 'EURO'
5 AND trans_ZONE ='EUR'
6 /
TRANS_NO TRANS_DATE TRANS_CURRENCY TRANS
---------- ---------- -------------------- -----
1011 12/03/1998 EURO EUR
1 row selected.
|
|
|
|
|
|
|
|
Re: SQL Query [message #668374 is a reply to message #668373] |
Mon, 19 February 2018 23:21 |
|
Deep_80
Messages: 5 Registered: February 2018
|
Junior Member |
|
|
Sorry if I confused you with word spreadsheet ,SPREADSHEET contains the example data which goes in where CLAUSE
I have written below sql . please let me know if it is correct way .
SELECT SETTLEMENT_STATUS, SETTLEMENT_NAME,BOOKING_AGENT,BOOKING_NO
FROM TRANS_TABLE
WHERE
TRANS_NO IN ('912828R85','912810QD3','831594AF3','44841BAA4' LIKEWISE I HAVE 500 VALUES)
AND TYPE IN ('BONDS','FUTURES')
QUANTITY('1059000','100000','1425000','175000' LIKEWISE I HAVE 500 VALUES)
PRICE_PRICE('98.384,','123','105.78', LIKE WISE I HAVE 500 VALUES)
SETTLEMENT_DATE('03-FEB-17','6-MAY-18', LIKEWISE I HAVE 500 VALUES)
So is this correct approach? I hope I have explained the requirement correctly. ( I have attached the VALUES of all trans_no,quantity, price_price , settlement_date in the attachment, please check.)
-
Attachment: book.png
(Size: 53.25KB, Downloaded 1247 times)
|
|
|
|
|
|
Re: SQL Query [message #668379 is a reply to message #668374] |
Tue, 20 February 2018 05:28 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
SELECT SETTLEMENT_STATUS, SETTLEMENT_NAME,BOOKING_AGENT,BOOKING_NO
FROM TRANS_TABLE
WHERE
TRANS_NO IN ('912828R85','912810QD3','831594AF3','44841BAA4' LIKEWISE I HAVE 500 VALUES)
AND TYPE IN ('BONDS','FUTURES')
QUANTITY('1059000','100000','1425000','175000' LIKEWISE I HAVE 500 VALUES)
PRICE_PRICE('98.384,','123','105.78', LIKE WISE I HAVE 500 VALUES)
SETTLEMENT_DATE('03-FEB-17','6-MAY-18', LIKEWISE I HAVE 500 VALUES) An IN list predicate is expanded into multiple OR predicates, so your query above may retrieve many more rows than you are expecting. Are you sure that your predicate should not be more likewhere
(transno='912828R85' and type='BONDS' and <the other columns of the first row=..>)
OR
(transno='912810QD3' and type='BONDS' and <the other columns of the second row=..>)
OR
<and so on 500 times>
[Updated on: Tue, 20 February 2018 05:30] Report message to a moderator
|
|
|
Re: SQL Query [message #668381 is a reply to message #668379] |
Tue, 20 February 2018 07:38 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
since the conditions are in an existing table why not something like
SELECT SETTLEMENT_STATUS, SETTLEMENT_NAME,BOOKING_AGENT,BOOKING_NO
FROM TRANS_TABLE
WHERE
TRANS_NO IN (select trans_no from condition_table)
AND TYPE IN ('BONDS','FUTURES')
QUANTITY in (select quantity from condition_table)
PRICE_PRICE in (select price_price from condition_table)
SETTLEMENT_DATE in(select settlement_date from condition_table)
make sure that the columns in the condition_table are indexed
[Updated on: Tue, 20 February 2018 07:39] Report message to a moderator
|
|
|
|
Re: SQL Query [message #668384 is a reply to message #668375] |
Tue, 20 February 2018 11:18 |
|
Deep_80
Messages: 5 Registered: February 2018
|
Junior Member |
|
|
Hello Quirks,
Quirks,
In a large table Z ,The Transaction has multiple trans_no , and type can be bond or futures, it has Multiple quantity ,and different price_price and different settlement_date so their requirement is to find the status of transaction for analysis purpose.
Requirement :Get me the "transaction status" from table Z for below trans_no.
In simple words : What is the STATUS of the transaction no (912828R85 ) which is "bond" type and it has "quantity" 1059000, "currency" usd , "price_price" =98.49921875, "settlement_dat"e='3-Feb-17. likewise i have 500 transaction_no with bond type, quantity, currenty,price_price and settlement_date..( i can write query using select IN ( Trans_no) but how to get the corresponding filter such as Quantity, currency,price_price and settlement_date for all the trans_no? ( please see the uploaded file for corresponding entries for below trans_No's)
A long list of trans_no values
Trans_no:
912828R85
912810QD3
831594AF3
44841BAA4
74442AAC2
USQ8809VAH26
05543EAA0
912828WJ5
912828P46
912828Q52
912828R44
718286BG1
USY3815NAU55
40963MAH5
718286BG1
XS0766460157
44891AAC1
UXU6
493738AD0
XS1377511511
71854QBV3
XS0864259717
015398AC4
903724AM4
718286AY3
912810RQ3
XS1405781854
71567PAE0
05477LAA4
XS1405781854
UXU6
74727PAT8
XS1405781854
87218RAA3
88556LAB2
18952UAA5
71854QBQ4
71854QBQ4
74727PAT8
988895AF5
706451BR1
71854QBQ4
706451BR1
912810RQ3
151290BF9
XS0114288789
71854QAZ5
715638AP7
445545AF3
G3925DAA8
706451BR1
715638AP7
XS1435072464
91911TAG8
XS0648477593
08861JAA7
06120TAA6
706451BR1
84265VAE5
817477AC6
74727PAU5
40963LAD6
Y68851AK3
74727PAU5
05533UAC2
79604YAB5
92890HAA0
912810RQ3
466112AR0
XS1075430741
606822AA2
912810RS9
912810RS9
912810RS9
912810RS9
912810RS9
912810RS9
912828Q29
912828S35
912828P79
912828S35
Y68856AB2
035198AA8
912810RM2
912810FF0
Y68856AB2
421790AA4
980236AM5
912828P53
912810RN0
RXU6
USP3579EBT30
84265VAE5
40049JAZ0
879403AV5
900123CB4
912810FP8
912810RJ9
912810RJ9
68620YAA0
USY20721AJ83
40049JAX5
600814AL1
65334HAG7
USG27649AE55
40049JBC0
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
9128282A7
9128282F6
912828S76
912828S76
912828S76
912828S76
912828S76
912828S76
912828G95
912810RT7
US25215BAN38
912828M56
XS1366918305
XS0974126186
836205AP9
XS0880134258
900123BH2
836205AP9
XS0607904264
77586TAE6
836205AN4
4651387N9
900123BZ2
900123CF5
900123BD1
900123BA7
USG2353WAB75
XS1206972348
XS0971721963
191241AG3
XS0880134258
XS0807677496
836205AU8
44891CAK9
40049JBA4
760942AS1
912810RS9
191241AF5
XS0607904264
XS1120709826
XS0925015074
445545AF3
XS0971721963
XS1303929894
195325BL8
836205AN4
912810RN0
XS0556885753
63254AAS7
03512TAC5
698299AK0
3130A86P3
65334HAG7
912810RS9
912828R85
912828R85
912828R85
912828UE8
912810RM2
40052VAD6
912828R44
912828R44
912828R44
40049JAZ0
912828MP2
912828J27
912828RC6
912828VP2
912828T67
912828T67
912828T67
912828T67
912828T67
912828T67
912828T67
912828T67
912828T67
912828T67
02364WAP0
40049JAZ0
912828T67
912810RT7
9128282A7
912810RT7
912828T67
912828T67
912828T67
912810RT7
912810RT7
912828T67
02364WBE4
912810RS9
91829KAA1
02364WAW5
1248EPBP7
91829KAA1
912810FT0
912810RM2
912828R85
912828K74
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
912828WZ9
195325BR5
055451AU2
912810RN0
912828U24
XS0903872603
FR0011391838
XS1405136364
912810RM2
912828N55
912828N55
912828N55
US912828U246
US31808LAA08
912828NW6
912828K74
319963BP8
RXH7
912828TC4
912828M64
912810RH3
09628TAA7
09628TAA7
OEH7
09628TAA7
09628TAA7
09628TAA7
09628TAA7
09628TAA7
09628TAA7
912828V72
055451AL2
92765YAE7
09628TAA7
09628TAA7
09628TAA7
USQ13095AA40
92765YAE7
92765YAE7
42806LAA9
RXM7
USQ0700QAA50
USQ0700QAA50
USQ94606AE22
912828W89
912810RV2
09628TAA7
89366QAA1
912828X21
92765YAE7
29085TAB9
067687AC7
09628TAA7
912828G79
09628TAA7
09628TAA7
912828WY2
912828WJ5
PL0000108510
67051XAA2
09628TAA7
30251GAW7
76720AAF3
USQ14048AA20
067687AC7
XS1366918305
912828U24
67051XAA2
912828M56
172967GD7
47030UAA6
30251GAW7
09628TAA7
912810RS9
912810RS9
XS0294364954
912828ND8
912828XG0
09628TAA7
09628TAA7
09628TAA7
92765YAG2
47030UAA6
09628TAA7
67051XAA2
09628TAA7
09628TAA7
67051XAA2
09628TAA7
92765YAG2
067687AC7
09628TAA7
912810QY7
47030UAA6
912828X88
067687AC7
05210QAA5
47030UAA6
912810QZ4
09628TAA7
09628TAA7
912828X88
912810QC5
067687AC7
USQ14048AA20
9128282P4
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
912828X88
9128282P4
09628TAA7
XS1303929894
USQ94606AE22
US040114HG87
XS1861056686
XS1433314314
912828Q78
USG0457FAC17
US040114HL72
9128282R0
US040114HL72
912828WZ9
912828X88
9128282R0
9128282R0
9128282R0
912828WE6
912828S76
912828F96
912828D72
94974BFQ8
US500769HJ69
US780097AU54
9128282W9
XS1433314314
XS1896445518
00206RBK7
BE0000337460
912828X88
912828KD1
USP989MJBE04
IT0005030504
US30251GAU13
912810FF0
IT0005175598
US961214DJ92
760942BA9
US30251GAU13
912810RP5
US30251GAW78
912828M80
912828M80
US30251GAW78
912828M80
912828M80
912828M80
912828M80
20030NBU4
912810RZ3
912828M80
US055451AX66
XS1822624242
XS1822624242
US780097AU54
US912828M805
XS1731823255
XS1731823255
01809WAW2
71854QBE1
US9128283F58
XS0268642181
XS0268642181
CH0118886735
XS0893103852
XS0268642181
XS0268642181
XS1502174581
XS1502174581
XS0506527851
XS0556885753
XS0431744282
GB00BHBFH458
XS1303921214
92936UAB5
GB0002404191
88579YAK7
03027XAN0
GB00B3KJDQ49
XS1263139856
GB00B3KJDQ49
191218CD0
US25156PAC77
US25156PAC77
XS1829047017
71854QBW1
718286AY3
XS1269724826
60687YAH2
US040114HL72
40049JAT4
741503AV8
459058DX8
05253JAG6
609207AJ4
XS1423722823
00287YBB4
084670BM9
40049JBB2
EC1727394
ED1594545
49456BAK7
GB0030880693
GB00B1VWPC84
GB00BN65R313
037833BL3
74340XBD2
92343VDJ0
494368BX0
494368BX0
G9328DAM2
741503BA3
741503BA3
GB00B058DQ55
01809WAW2
USY20721AJ83
900123CL2
47032FAB5
XS1584122920
XS1584122920
XS1584123142
XS1584123142
XS1584122920
XS1584122920
ED1594545
XS1382610183
XS1382610183
XS0253572050
XS1400342587
XS1400342587
XS1050840724
US912810RY64
XS1763181012
XS1706235097
USP22854AG14
2027A0HT9
46556MAJ1
71856MBQ1
USP989MJAY76
71854QCD2
71854QAW2
715638AU6
105756BV1
9128283U2
US26156FAA12
US26156FAA12
013817AW1
172967ET4
013817AW1
US25152RVS92
US25152RVS92
CH0331455318
9128282A7
912828U32
9128282A7
912810RS9
912828U32
912828U32
912828W63
912828W63
912828W63
912828W63
912828W63
912828W63
912828B58
9128283F5
9128283F5
912810RY6
9128283F5
912810RY6
9128283J7
852061AS9
9128283F5
912828N30
9128283F5
9128283Q1
912810RY6
585055BR6
9128283F5
9128283Q1
00817YAV0
92343VCM4
9128283Q1
912810RY6
9128283F5
[EDITED by LF: applied [spoiler] tags]
-
Attachment: book.png
(Size: 53.25KB, Downloaded 1219 times)
[Updated on: Tue, 20 February 2018 13:07] by Moderator Report message to a moderator
|
|
|
|
|
Re: SQL Query [message #668400 is a reply to message #668384] |
Wed, 21 February 2018 07:48 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Deep_80 wrote on Tue, 20 February 2018 12:18Hello Quirks,
In simple words : What is the STATUS of the transaction no (912828R85 ) which is "bond" type and it has "quantity" 1059000, "currency" usd , "price_price" =98.49921875, "settlement_dat"e='3-Feb-17. likewise i have 500 transaction_no with bond type, quantity, currenty,price_price and settlement_date..( i can write query using select IN ( Trans_no) but how to get the corresponding filter such as Quantity, currency,price_price and settlement_date for all the trans_no? ( please see the uploaded file for corresponding entries for below trans_No's)
I'm still confused by the description of the problem, but now it sounds to me like you need a lot of OR code.
where (trans=:v1 and type=:v2 and quantity=:v3 and currency=:v4 and price=:v5 and date=:v6)
or (trans=:v7 and type=:v8 and quantity=:v9 and currency=:v10 and price=:v11 and date=:v12)
or (trans=:v13 and type=:v14 and quantity=:v15 and currency=:v16 and price=:v17 and date=:v18)
etc.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:11:25 CDT 2024
|