Home » SQL & PL/SQL » SQL & PL/SQL » Simple SQL query
Simple SQL query [message #440711] Tue, 26 January 2010 14:43 Go to next message
salama
Messages: 36
Registered: January 2006
Member
Guys I have a very basic SQl question

I have four columns

Birth_DATE_TPP
DRUG_START_TPP
VAX_DATE
BIRTH_DATE_TPQ

All I want to do is to subtract vax_date from birth_date_tpq
when Birth_DATE_TPP and DRUG_START_TPP >= 4

So when Birth_DATE_TPP and DRUG_START_TPP >= 4 I want
vax_date - birth_date_tpq
Re: Simple SQL query [message #440712 is a reply to message #440711] Tue, 26 January 2010 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>when Birth_DATE_TPP and DRUG_START_TPP >= 4
What does this mean?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Simple SQL query [message #440713 is a reply to message #440711] Tue, 26 January 2010 14:52 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member

These two columns contain values from 1-6. Only when Birth_DATE_TPP and DRUG_START_TPP >= 4 then

vax_date - birth_date_tpq
Re: Simple SQL query [message #440714 is a reply to message #440713] Tue, 26 January 2010 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
using CUT & PASTE show what you tried & the results.

Please read & follow Posting Guidelines.
Re: Simple SQL query [message #440715 is a reply to message #440711] Tue, 26 January 2010 15:12 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
Not sure what part of the guidelines I am not following, I apologize if I am not. I tried writing a case statement, however I am getting an error ORA-00905: missing keyword. The syntax I wrote is obviously wrong.


( CASE WHEN T2.Birth_DATE_TPP >=4 THEN T6.VAX_DATE-T2.BIRTH_DATE_TPQ WHEN T6.DRUG_START_TPP >=4 THEN T6.VAX_DATE-T2.BIRTH_DATE_TPQ)
Re: Simple SQL query [message #440716 is a reply to message #440715] Tue, 26 January 2010 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SELECT vax_date - birth_date_tpq
FROM ....???
WHERE ...???
Re: Simple SQL query [message #440717 is a reply to message #440713] Tue, 26 January 2010 15:16 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
salama wrote on Tue, 26 January 2010 15:52

These two columns contain values from 1-6. Only when Birth_DATE_TPP and DRUG_START_TPP >= 4 then

vax_date - birth_date_tpq


Do you want to update a column or just return a value in output? Is my assumption incorrect?

.
.
.
if C.Birth_DATE_TPP >= 4 and C.DRUG_START_TPP >= 4 then
   {some variable} := C.vax_date - C.birth_date_tpq;
end if;

[Updated on: Tue, 26 January 2010 15:17]

Report message to a moderator

Re: Simple SQL query [message #440727 is a reply to message #440711] Tue, 26 January 2010 16:48 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
Thanks joy_division that helped. This is what worked for me

( CASE WHEN Birth_DATE_TPP >=4 AND DRUG_START_TPP >=4 THEN (VAX_DATE-BIRTH_DATE_TPQ)/365.25 END)

From the case statement above I get results in years such as:
73.6
0.07
0.865

However I also want to make a further conversion. For instance I also want a month column that will convert 73.6 to .6 months
0.07 to .1 and 0.865 to .9 months etc. So basically I want to convert those year values to the nearest decimal

So 0.865 years should be converted to 9months and 0.07years to .1 months etc. Hope this is not too confusing.
Re: Simple SQL query [message #440728 is a reply to message #440727] Tue, 26 January 2010 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions135.htm#i78633


It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 26 January 2010 16:57]

Report message to a moderator

Re: Simple SQL query [message #440825 is a reply to message #440727] Wed, 27 January 2010 03:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
( CASE WHEN Birth_DATE_TPP >=4 AND DRUG_START_TPP >=4 THEN (VAX_DATE-BIRTH_DATE_TPQ)/365.25 END)

From the case statement above I get results in years such as:
73.6
0.07
0.865 


No - you're not getting a result in years. If you can find me a year that has 365.25 days in it then I'll buy you a lollipop.

If you want to get the number of years between two dates, I'd use:
MONTHS_BETWEEN(VAX_DATE,BIRTH_DATE_TPQ)/12


Quote:
For instance I also want a month column that will convert 73.6 to .6 months
0.07 to .1 and 0.865 to .9 months

I'd use MONTHS_BETWEEN again.
Re: Simple SQL query [message #440859 is a reply to message #440711] Wed, 27 January 2010 08:45 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
I Believe in the Julian calendar, the average length of a year is 365.25 days. In a non-leap year, there are 365 days but regardless my question still remains. I would highly appreciate if anyone can help out with the correct SQL syntax

How do I convert using the round and truncate function

73.6 to .6
0.07 to .1
0.865 .9

Basically I need to get rid of everything to the left of the decimal and then round everything to the right. Does that make any sense. Thanks
Re: Simple SQL query [message #440860 is a reply to message #440859] Wed, 27 January 2010 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I convert using the round and truncate function...

SQL> with 
  2    data as (
  3      select 73.6 val from dual
  4      union all
  5      select 0.07 val from dual
  6      union all
  7      select 0.865 val from dual
  8    )
  9  select val, round(val,1)-trunc(round(val,1)) new_val
 10  from data
 11  /
       VAL    NEW_VAL
---------- ----------
      73.6         .6
       .07         .1
      .865         .9

3 rows selected.

Regards
Michel

[Updated on: Wed, 27 January 2010 08:53]

Report message to a moderator

Re: Simple SQL query [message #440861 is a reply to message #440859] Wed, 27 January 2010 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
salama wrote on Wed, 27 January 2010 14:45
I Believe in the Julian calendar, the average length of a year is 365.25 days.

While that's true, unless the difference between your years is significant you are going to get noticable rounding errors.

Did you try JRowbottom's suggestion of using months_between? It's coded to cope with leap years so should always give the right answer.

EDIT: typo

[Updated on: Thu, 28 January 2010 02:18]

Report message to a moderator

Re: Simple SQL query [message #440862 is a reply to message #440711] Wed, 27 January 2010 09:24 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
Gentlemen that answers all my questions, thanks for all the help. It is much appreciated.
Re: Simple SQL query [message #440939 is a reply to message #440862] Thu, 28 January 2010 00:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
salama wrote on Wed, 27 January 2010 16:24
Gentlemen that answers all my questions, thanks for all the help. It is much appreciated.

I am afraid you are missing the point JRowbottom & cookiemonster are making. By sticking to what looks to be a suboptimal solution, you seem to introduce unnecessary flaws in your code.
Previous Topic: Null values top by ascending order
Next Topic: How to call procedure in the select statement
Goto Forum:
  


Current Time: Fri Sep 30 07:17:30 CDT 2016

Total time taken to generate the page: 0.12954 seconds