netezza sql, how to come true?

Question

create table p(pid int, st int, v int,hid int,ht varchar(2));
data:
insert into p(pid,st,hid, ht,v )
select 1    ,20160131   ,100    ,'FO'   ,10
union select 1  ,20160131   ,101    ,'FO'   ,20
union select 1  ,20160131   ,102    ,'FO'   ,30
union select 1  ,20160131   ,103    ,'ST'   ,40
union select 1  ,20160229   ,104    ,'ST'   ,50
union select 207    ,20160229   ,301    ,'ST'   ,80
union select 207    ,20160229   ,302    ,'ST'   ,20
union select 207    ,20160331   ,303    ,'FO'   ,40
union select 102    ,20160229   ,205    ,'ST'   ,60
union select 102    ,20160229   ,206    ,'ST'   ,20
union select 102    ,20160229   ,207    ,'FO'   ,20
union select 100    ,20160131   ,201    ,'ST'   ,50
union select 100    ,20160131   ,202    ,'ST'   ,50
union select 101    ,20151231   ,203    ,'ST'   ,50
union select 101    ,20151231   ,204    ,'ST'   ,50


result:
pid v   hid ht  path    pre_st  next_st st
1   40  103 ST  1   NULL    NULL    20160131
100 50  201 ST  1--->100    NULL    NULL    20160131
100 50  202 ST  1--->100    NULL    NULL    20160131
101 50  203 ST  1--->101    20151231    NULL    20160131
101 50  204 ST  1--->101    20151231    NULL    20160131
102 20  206 ST  1--->102    NULL    20160229    20160131
102 60  205 ST  1--->102    NULL    20160229    20160131
207 20  302 ST  1--->102--->207 NULL    20160331    20160229
207 80  301 ST  1--->102--->207 NULL    20160331    20160229
303 99  304 ST  1--->102--->207--->303  NULL    20160720    20160331


I can do it in SQLServer first. 
select pid,v,hid,ht, path=cast(pid as varchar) ,
 (select max(st) from p where pid = a.pid and st < a.st) as pre_st,
 (select max(st) from p where pid = a.pid and st > a.st) as next_st,
 a.st into #t1
 from p a where pid=? and st=?
but netezza cannot support this grammer.

I need to according by pid,st as parameter to do it. assumption we use pid=1 and st=20160131, I will save the parameter set in one table. create table i(pid int, st int) insert into i(pid,st) select 1,20160131 how to come true the above result?

score 0 · Answer 1 · edited May 23 '17 at 11:51

If you're just looking to create a temporary table (the equivalent of your SQL Server syntax), then it's this:

create temporary table t1 as
select pid,v,hid,ht, path=cast(pid as varchar) ,
(select max(st) from p where pid = a.pid and st < a.st) as pre_st,
(select max(st) from p where pid = a.pid and st > a.st) as next_st,
a.st 
from p a where pid=? and st=?

I'm going to assume that the ? values you have there are because you're using ODBC parameters. If you're trying to get Netezza to prompt you for something, you can't.

netezza sql, how to come true?

1 Answers1