Home » SQL & PL/SQL » SQL & PL/SQL » Select value from multiple dates (Oracle 10g)
Select value from multiple dates [message #685197] Thu, 04 November 2021 08:58 Go to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
	select  fam_id,			
	        stuid,			
	      (case  when fdate is not null then paid			
	          else (case when fdate is not null and date2 is not null then damt			
	else (0)			
	end)			
	end			
	)  			
	from fees  			
	 where fam_id in (2,3,9)			
	and fmonth='November'			
	and fyear=2021			
	  order by fam_id,fdate			
	/			
i am using this query. and getting this output in command line Sql*plus as shown in the figure.i have table name fees.
Quote:


Fam_id number(6) Primary key
stuid number(6)
fmonth varchar2(50)
fyear number(5)
tot number(7)
paid number(7)
fdate date
damt number(6)
date2 date

/foru/forum/fa/14567/0/
  • Attachment: 123432.jpg
    (Size: 175.84KB, Downloaded 834 times)

[Updated on: Thu, 04 November 2021 09:03]

Report message to a moderator

Re: Select value from multiple dates [message #685200 is a reply to message #685197] Thu, 04 November 2021 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Lalit Kumar B wrote on Sat, 07 June 2014 19:01
...
Please post the insert statements for the tables.

Michel Cadot wrote on Wed, 01 October 2014 11:12

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you how to do it.

Michel Cadot wrote on Sun, 30 November 2014 08:14

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Michel Cadot wrote on Wed, 30 December 2015 10:36

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Michel Cadot wrote on Fri, 01 January 2016 11:26
Michel Cadot wrote on Wed, 30 December 2015 10:36

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
please help

Michel Cadot wrote on Fri, 01 April 2016 11:13

Once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Bill B wrote on Fri, 01 April 2016 16:39
What you want is easy to do, but doing is is different depending on what your oracle database is. Please type

select * from v$version;

and paste what is returned in this issue.

Michel Cadot wrote on Tue, 12 April 2016 08:10

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Michel Cadot wrote on Sat, 02 january 2021 13:35
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

(Just an extract of all reminders.)

Re: Select value from multiple dates [message #685201 is a reply to message #685200] Thu, 04 November 2021 11:19 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Test Case:
create table fees (
fam_id number(7),stuid number(7),
fmonth varchar2(50),fyear number(15),
totamt number(7),paid number(6), fdate date,
damt number(6),date2 date,bal number(7));

----------------------------------- iNSERT 
insert into fees (fam_id,stuid,fmonth,fyear,totamt,paid,fdate,damt,date2,bal)
values
(1,1,'November',2021,3000,2000,'01-NOV-2021',1000,'01-NOV-2021',0);
insert into fees (fam_id,stuid,fmonth,fyear,totamt,paid,fdate,damt,date2,bal)
values
(2,2,'November',2021,3400,800,'01-NOV-2021',2600,'03-NOV-2021',0);
insert into fees (fam_id,stuid,fmonth,fyear,totamt,paid,fdate,damt,date2,bal)
values
(2,558,'November',2021,1500,1100,'01-NOV-2021',400,'03-NOV-2021',0);
insert into fees (fam_id,stuid,fmonth,fyear,totamt,paid,fdate,bal)
values
(3,671,'November',2021,2500,0,'01-NOV-2021',2500);
insert into fees (fam_id,stuid,fmonth,fyear,totamt,paid,fdate,bal)
values
(9,406,'November',2021,3000,1400,'01-NOV-2021',1600);
COMMIT;



Re: Select value from multiple dates [message #685204 is a reply to message #685201] Thu, 04 November 2021 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/207794/685134/#msg_685134

Re: Select value from multiple dates [message #685205 is a reply to message #685201] Thu, 04 November 2021 13:49 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
What is the relationship between fmonth and fyear? Why should they not be combined into a single DATE column? And even if there is justification for keeping them in separate columns (extremely doubtful), why a VARCHAR2(50) for a name of a month that will never exceed nine characters? And for the year - number(15)? Seriously? reserving 15 digits to hold a 4-digit number? And a NUMBER is a terrible way to store a date. There is nothing to prevent your fyear from being 0, or 999999999. Nothing to prevent your fmonth from being 'get a clue'.

And please give some thought to a sane naming standard for your columns. Column names should, in and of themselves, convey an idea of the business use of the data represented in the column. I recommend all column names be in the format 'adjective_noun'. And some standardized, consistent abbreviations. The guy that has to maintain the code after you will thank you. And it will make presenting test cases much more comprehensible, so people trying to help you will thank you.

Previous Topic: Days Query
Next Topic: Sequence number to be entered by a user
Goto Forum:
  


Current Time: Thu Mar 28 12:19:31 CDT 2024