MySQL Forums
Forum List  »  Newbie

IF Statement - does nesting order matter?
Posted by: Amanda J
Date: November 14, 2017 09:19PM

I have a nested if:

SELECT
aquaculture_id,
qty_prod,
IF(avg_prod = '0', ':',													[allows for unknown, data not avail; unobtainable] 
	IF(qty_prod < (0.5*max_5_prod),'over-exploited',								[current catch is < 50 percent of the maximum after a 5-year smoothing]
			IF(qty_prod BETWEEN avg_prod_5 AND avg_prod_10, 'fully exploited',				[catches stabilized at/close to last 5-10 yrs peak values.]
				IF(qty_prod >= avg_prod_10 AND area_change_a > '0', 'non-fully exploited',		[catches increased over time when fishing effort has increased]
					IF(qty_prod < max__prod_5 AND area_change_a = '0', 'over-exploited_2', ':')	[catches have dropped significantly from a peak without a clear cut in fishing effort]
				  )
			  )
	  )
  )'bio_a'
FROM fao_aquaculture;

It seeks to identify from the data which species are:
    a. non-fully-exploited
    b. fully exploited
    c. over-exploited
    d. over-exploited_2
    e. unknown, data not avail; unobtainable
There are two distinct definitions used to define an 'over-exploited' population.

My first run of the query returns A LOT of b.'over-exploited' populations. A smattering of a. non-fully exploited, and d.unknown, data not avail. No d. over_exploited_2 results were returned

I need all four definitions to be considered and for the correct 'answer'to be returned.

Is there any rocket science here in the internal ordering of IF statements that I need to be mindful of? I'm looking to have as much certainty as is reasonable that the result I'm getting reflects the data/truth.

And not that I stuffed up the query.

Options: ReplyQuote


Subject
Written By
Posted
IF Statement - does nesting order matter?
November 14, 2017 09:19PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.