-1

Below is the scenario I need solution on this in Netezza SQL.

if you look at below input 2nd record 2nd column is empty but I need value form above 'H' record. expected will be "A,CP,2" for 2nd row. same as for 4th row i would require value from 3rd record 'H' so expected would be "E,SP,4"

Input data

H,CP,1

A,,2

H,SP,3

E,,4

H,,5

C,,6

Output:

H,CP,1

A,CP,2

H,SP,3

E,SP,4

H,,5

C,,6

second input example: INPUT

c1,c2,c3

H,P,1

H,Q,2

E,,3

A,,4

H,R,5

A,,6

C,,7

H,,8

E,,9

H,S,10

OUTPUT

H,P,1

H,Q,2

E,Q,3

A,Q,4

H,R,5

A,R,6

C,R,7

H,,8

E,,9

H,S,10

venkatesh
  • 1
  • 1
  • What work have you done so far? – Sorin Mar 15 '21 at 11:06
  • Hi Sorin, I am not good in SQL but I am an ETL developer. i have tried separating H and non H records and trying range lookup in the tool but it would be easy for me if i have SQL to do this. because i have 3 million records to process every day so and tool performance is very bad. – venkatesh Mar 15 '21 at 11:31
  • Please consider rephrasing your question as your problem is very unclear. Hints: https://stackoverflow.com/help/how-to-ask – Sorin Mar 15 '21 at 11:40
  • Hi Sorin, I have given clear example please can you check if its good. – venkatesh Mar 15 '21 at 11:53

1 Answers1

0

I assume from your example that

  1. you can only have one or two rows with a given ‘letter’
  2. that it is only the middle column that is sometimes NULL?

I further assume that 3) the table is called TAB 4) and has 3 columns named C1,C2,C3

In that case this should cover it:

Select a.C1, nvl(a.C2,b.C2), a.C3
From TAB a 
Left Join TAB b
Using (C1)
Lars G Olsen
  • 1,093
  • 8
  • 11
  • Hi lars, input may have many 'H' records and Non 'H' records like below. basically what i would need is find the first 'H' in col1 and next 'H' records and in between those 'H' records propagate the what first 'H' has in column c2 to non 'H' records. no need to update 'H' records. please let me know if it's not clear i will give more examples. INPUT ----- c1,c2,c3 -------- H,P,1 H,Q,2 E,,3 A,,4 H,R,5 A,,6 C,,7 H,,8 E,,9 H,S,10 OUTPUT ----- H,P,1 H,Q,2 E,Q,3 A,Q,4 H,R,5 A,R,6 C,R,7 H,,8 E,,9 H,S,10 – venkatesh Mar 30 '21 at 14:16
  • I dont get the C,R,7 row in the second example, from your først example, it looks as though you only want to carry it forward one step. Why not a H,SP, 5 in the first example? – Lars G Olsen Apr 01 '21 at 21:20
  • Hi Lars I need to carry forward what above H record has until I find next H. in my second example if you consider row number 5,6,7 and 8. here i need to fill the gap between row 5 and 8 that means for row 6 and row 7 will be filling up with row 5 second column value shown as here and in the example. H,R,5 A,R,6 C,R,7 H,,8 – venkatesh Apr 02 '21 at 08:21
  • Ok, let me repeat: ‘H’ has special meaning, so as long as ‘later’ record has a ‘H’ too, you wish to carry C2 values from the previous’H’ record forward as long as they values in between are NULL? – Lars G Olsen Apr 02 '21 at 16:33
  • yes, I want to carry C2 for non H records only most of the records has nulls or space only. just take the Previous H C2 and pass it till you find the next H but for the next H record. – venkatesh Apr 04 '21 at 05:04