Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 12 c)
SQL Query [message #668332] Mon, 19 February 2018 01:13 Go to next message
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 #668333 is a reply to message #668332] Mon, 19 February 2018 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no difference to write a query for one or million of rows.
Post what you already did.

Before, read How to use [code] tags and make your code easier to read.
Re: SQL Query [message #668335 is a reply to message #668333] Mon, 19 February 2018 02:26 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
I think they are asking how to query the excel sheet. Which is a different kettle of fish altogether!
Re: SQL Query [message #668339 is a reply to message #668332] Mon, 19 February 2018 02:54 Go to previous messageGo to next message
John Watson
Messages: 7664
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
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 #668354 is a reply to message #668349] Mon, 19 February 2018 05:53 Go to previous messageGo to next message
browncat
Messages: 9
Registered: May 2015
Junior Member
The query can be...
select * from ltable
where Date = '12-03-1998'
and Currency = 'EURO'
and Zone ='EUR';
where ltable is the name of the table with 500 rows.
Re: SQL Query [message #668356 is a reply to message #668354] Mon, 19 February 2018 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 19 February 2018 13:02
Michel Cadot wrote on Tue, 05 May 2015 10:49
@browncat,

Welcome to the forum, it is nice to see you want to help but test your solutions before posting them.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Why all your answers are just silly?

[Updated on: Mon, 19 February 2018 06:03]

Report message to a moderator

Re: SQL Query [message #668371 is a reply to message #668356] Mon, 19 February 2018 21:58 Go to previous messageGo to next message
Deep_80
Messages: 5
Registered: February 2018
Junior Member
Hello Michel Cadot / Others
Thanks for quick help. That is pretty simple but the requirement is .. SELECT * FROM Z
"WHERE" clause is from the book ( Uploaded). it should satisfy all the columns in uploaded txt file

FYI: book.txt represent columns of the table Z

Thanks again for your help



Example :
select * from z
where TRANS_NO IN ( '912828R85','912810QD3',...............................................)
AND TYPE IN ('BONDS','FUTURES')
AND QUANTITY IN (' ........,)

all the conditions should be satisfied from spreadsheet.. Hope I am able to explain properly


  • Attachment: book.png
    (Size: 53.25KB, Downloaded 104 times)
Re: SQL Query [message #668372 is a reply to message #668354] Mon, 19 February 2018 22:06 Go to previous messageGo to next message
Deep_80
Messages: 5
Registered: February 2018
Junior Member
Brown cat,
"The query can be...
select * from ltable
where Date = '12-03-1998'
and Currency = 'EURO'
and Zone ='EUR';
where ltable is the name of the table with 500 rows. "


you mean to say I have to write Select statement 500 times? to fetch the records from a table which has over 1 Million rows?

FYI: the user provided a spreadsheet with 500 (CONDITIONS) , and he wants information from the table which satisfy the conditions such as DATE , CURRENCY , ZONE
Re: SQL Query [message #668373 is a reply to message #668372] Mon, 19 February 2018 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Why do you keep saying "spreadsheet" when Oracle DB does not contain or know anything about spreadsheet?

What is your input?
What are your requirements?
What is your expected/desired results?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: SQL Query [message #668374 is a reply to message #668373] Mon, 19 February 2018 23:21 Go to previous messageGo to next message
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 166 times)
Re: SQL Query [message #668375 is a reply to message #668374] Tue, 20 February 2018 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you'll have to load (import, whatever) your "book" / "spreadsheet" into yet another table.

Then, using a dynamic SQL, you'd compose a SQL statement based on conditions stored in that book and, finally, execute a query.
Re: SQL Query [message #668376 is a reply to message #668374] Tue, 20 February 2018 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
So is this correct approach?

Correct approach for what?

Quote:
I hope I have explained the requirement correctly.

Nope.
Answer these simple questions:

Quote:
What is your input?
What are your requirements?
What is your expected/desired results?

No other comment, just answer to these questions.

Re: SQL Query [message #668378 is a reply to message #668376] Tue, 20 February 2018 01:48 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
Dear Deep_80,

just a few questions. Please don't feel offended, but to me it sounds as if you have no clue (probably I'm terribly wrong). But to help you it is necessary for us to know what you exactly want and from where we must start.

1st Question:
Have you ever written a working SQL (your last post let me assume that you haven't)?

2nd Question:
In which format is the "spreadsheet" delivered to you (e.g. excel file, database table, text file, etc.)?

3rd Question:
What do you want to achieve?
e.g.:
- filter the data in the in the spreadsheet (by which filter criteria)
- transfer the data to a new "spreadsheet"
- use data in the "spreadsheet" to filter the data in the original table
- calculate new values based on the data in the spreadsheet.
- etc.
In short I honestly don't understand what you want.

If you don't know what to answer just describe it as good as you can in your own words. We're quite good in figuring out the bottom line (and if not we'll ask you again ... and again ... and again). Wink
Re: SQL Query [message #668379 is a reply to message #668374] Tue, 20 February 2018 05:28 Go to previous messageGo to next message
John Watson
Messages: 7664
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 like
where
(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 Go to previous messageGo to next message
Bill B
Messages: 1814
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 #668383 is a reply to message #668381] Tue, 20 February 2018 08:13 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
IF the 500 row spreadsheet shall act as a filter for the 1 Million row table, then an inner join would probably be the way to go.

But we know nothing ...
Re: SQL Query [message #668384 is a reply to message #668375] Tue, 20 February 2018 11:18 Go to previous messageGo to next message
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


[EDITED by LF: applied [spoiler] tags]
  • Attachment: book.png
    (Size: 53.25KB, Downloaded 153 times)

[Updated on: Tue, 20 February 2018 13:07] by Moderator

Report message to a moderator

Re: SQL Query [message #668385 is a reply to message #668384] Tue, 20 February 2018 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
We still don't know what are your inputs.
We still don't know what are your requirements.
We still don't know what is your expected/desired results

Why do you avoid providing us with requested & needed details?

Post CREATE TABLE statements for all tables involved.
Re: SQL Query [message #668394 is a reply to message #668385] Wed, 21 February 2018 02:10 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
Hello Deep_80,

at least the requirement is clear now. But because your request still lacks a substantial lot of helpful information, I'll go with some assumptions.
1st assumption:
Both Tables ("table_Z" and the "spreadsheet" are tables in an (Oracle) database, which means they are query-able by SQL.
2nd assumption:
You want to enrich the values in the "spreadsheet" with the "status" column from "table_Z".

First of all it seems to me that the "Trans_no" in the screenshot (which I suspect is a excerpt of "spreadheet") is an unique identifier (this assumption is supported by the requirement). Please could you check if
SELECT   COUNT(*), TRANS_NO
    FROM SPREADSHEET
GROUP BY TRANS_NO
  HAVING COUNT(*) > 1;
returns any results? If not you are fine to use this column as the filter.

At next we have to check if "Trans_no" in in "table_Z" is unique too:
SELECT   COUNT(*), TRANS_NO
    FROM TABLE_Z
GROUP BY TRANS_NO
  HAVING COUNT(*) > 1;
if this statement does not return any rows too, you can just join the two tables to get the desired result:
SELECT SPREADSHEET.*, TABLE_Z.STATUS
  FROM SPREADSHEET JOIN TABLE_Z ON (SPREADSHEET.TRANS_NO = TABLE_Z.TRANS_NO)

Please adjust the names of the tables / columns to your needs.

Because there are so many unknown unknowns I can't dig deeper in your issue. Please come back with any results (even error messages are helpful).

We are trying to fly your plane blindfolded and you are not able to provide the basic information we have requested. Probably it's the best if you ask someone at your place who can have a more direct look at you problem. Most IT folk is quite supportive if you ask nicely Wink
Re: SQL Query [message #668400 is a reply to message #668384] Wed, 21 February 2018 07:48 Go to previous message
joy_division
Messages: 4908
Registered: February 2005
Location: East Coast USA
Senior Member
Deep_80 wrote on Tue, 20 February 2018 12:18
Hello 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.
Previous Topic: Grouping of records in a table with a group id
Next Topic: User variable to decide which in statement to use in query
Goto Forum:
  


Current Time: Thu Nov 15 11:12:31 CST 2018