0

I'm currently using the COALESCE function to grab the first NOT NULL value from a list of fields. Example:

 COALESCE(header_to_node_13.subsetname, header_to_node_12.subsetname,
 header_to_node_11.subsetname, header_to_node_10.subsetname, 
 header_to_node_9.subsetname, header_to_node_8.subsetname, 
 header_to_node_7.subsetname, header_to_node_6.subsetname,   
 header_to_node_5.subsetname, header_to_node_4.subsetname,
 header_to_node_3.subsetname, header_to_node_2.subsetname,     
 header_to_node_1.subsetname, 
 header_to_node.subsetname, header_to_node.setname) AS prctr1

I need to modify the logic so that I grab the value to the right of the COALESCE value as well, so the two values can be compared to populate a newly created field.

For instance, if the COALESCE value is header_to_node_5.subsetname I need to grab header_to_node_4.subsetname, as well, so the two can be used in a CASE statement.

If the second value (header_to_node_4.subsetname) IS NULL I want the first value (header_to_node_5.subsetname). If the second value is NOT NULL I want to use the second value.

I'm stumped on how to grab the second value. I am using Greenplum/PostgreSQL 8.2

user3329160
  • 165
  • 2
  • 13
  • 1) Your logic isn't entirely clear to me, are you getting 1 value or 2 and somehow concatenating them together? 2) It sounds like you're going to wind up using some form of giant `IF()` statement. I don't see any way around that unless I misunderstand the question. – ebyrob Oct 28 '15 at 20:30
  • Currently I am just getting the first non null value, as performed by the COALESCE function. I need to also grab the value that that would be after that value. So if the COALESCE grabbed header_to_node_10, I also need header_to_node_9 and that should be a simple CASE statement: WHEN header_to_node_9 IS NULL THEN header_to_node_10 ELSE header_to_node_9 – user3329160 Oct 28 '15 at 20:42
  • It sounds like you're just changing the order of your `COALESCE()` with that `CASE` statement. So, better to just figure out the right order the first time. `COALESCE` gets the first non-NULL, you somehow want the second unless it is NULL. – ebyrob Oct 28 '15 at 20:45
  • Right - well that's what I'm trying to figure out how to do.. – user3329160 Oct 29 '15 at 14:38

0 Answers0