Imitating procedural language using SQL

Database
Enthusiast

Imitating procedural language using SQL

Hi,

I have this requierement to set a flag based on multiple logic. Main problem is flag needs to be set based on value from a previous record in the order. Here is an example. Last coulmn gives the logic to set the flag.









Customer Entry_Date Action From To Flag  
INBUGHUJIK 2/1/2010 10:00:00.000000 WENT-LST Bldg1  Bldg1  N Flag is N because Action is WENT-LST
INBUGHUJIK 2/4/2010 08:12:00.000000 FACT-OUT Bldg1  Bldg1  N Flag is N because From = To
INBUGHUJIK 2/12/2010 12:13:00.000000 FACT-IN  Bldg2  Bldg3 N Flag is N because previous record has From = To
INBUGHUJIK 2/13/2010 11:18:00.000000 COME-FST Bldg2  Bldg2  N Flag is N because previous record has From = To
INBUGHUJIK 2/25/2010 08:51:00.000000 WENT-LST Bldg2  Bldg2  N Flag is N because Action is WENT-LST
INBUGHUJIK 2/26/2010 18:06:00.000000 FACT-OUT Bldg2  Bldg3 Y Flag is Y because previous record has Action as WENT-LST and From <> To
INBUGHUJIK 4/19/2010 10:01:00.000000 FACT-OUT Bldg2  Bldg4  Y Flag is Y because one of previous record has Action as WENT-LST and From <> To and previous record has flag Y
INBUGHUJIK 4/21/2010 22:56:00.000000 FACT-IN  Bldg3  Bldg3 N Flag is N because previous record has From = To

Any one have any idea on how to achieve this using SQL alone?

Regards,

Abin.

3 REPLIES
Enthusiast

Re: Imitating procedural language using SQL

Trying to make the table in more readable format. 

Customer     Entry_Date                           Action       From   To        Flag   Logic

INBUGHUJIK 2/1/2010 10:00:00.000000 WENT-LST Bldg1  Bldg1    N      Flag is N because Action is WENT-LST

INBUGHUJIK 2/4/2010 08:12:00.000000 FACT-OUT Bldg1  Bldg1     N      Flag is N because From = To

INBUGHUJIK 2/12/2010 12:13:00.000000 FACT-IN  Bldg2  Bldg3     N      Flag is N because previous record has From = To

INBUGHUJIK 2/13/2010 11:18:00.000000 COME-FST Bldg2  Bldg2   N       Flag is N because previous record has From = To

INBUGHUJIK 2/25/2010 08:51:00.000000 WENT-LST Bldg2  Bldg2   N      Flag is N because Action is WENT-LST

INBUGHUJIK 2/26/2010 18:06:00.000000 FACT-OUT Bldg2  Bldg3    Y      Flag is Y because previous record has Action as WENT-LST and From <> To

INBUGHUJIK 4/19/2010 10:01:00.000000 FACT-OUT Bldg2  Bldg4    Y      Flag is Y because one of previous record has Action as WENT-LST and From <> To and previous record has flag   Y

INBUGHUJIK 4/21/2010 22:56:00.000000 FACT-IN  Bldg3  Bldg3      N        Flag is N because previous record has From = To

Senior Apprentice

Re: Imitating procedural language using SQL

Hi Abin,

you can probably do that as OLAP functions allow access to the "previous" record, e.g.

   min(Action)
over (partition by customer
order by entry_date
rows between 1 preceding and 1 preceding) as prev_action

and you use it in a CASE:

   case
when prev_Action = 'WENT-LST' and from <> to then 'Y'
when Action = 'WENT-LST' then 'N'

There's only one condition which is more complex: 

Flag is Y because one of previous record has Action as WENT-LST and From <> To and previous record has flag   Y

The first part is:

max(Action)
over (partition by customer
order by entry_date
rows between unbounded preceding and 1 preceding) = 'WENT-LST' and from <> to then 'Y' else 'N' end

but for the "previous record has flag   Y" you need to check if there's another algorithm, because the flag is based on the result of an OLAP function (kind of recursion). 


If OLAP doesn't work you can apply the cursor logic within a WITH RECURSIVE, this will be easier but depending on the number of rows per customer it might be much slower than OLAP. But still much faster than a cursor which is processed sequentially on a single node while WITH is running in parallel.

If you could show the cursor logic i might help further.

Dieter

Enthusiast

Re: Imitating procedural language using SQL

Hi Dieter,

I finally figured out how to achive this using SET logic with UNBOUNDED option. RECURCIVE query si too expensive because the table which the select reads from is large. And as you mentione dprocedure also is expensive because of size of table.

 , MIN(A.Entry_Date ) OVER (PARTITION BY A.Customer ORDER BY A.Entry_Date  DESC RESET WHEN  ( From = To OR Action = 'WENT-LST') ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Entry_Date_START_ACT
 , MIN(A.Entry_Date ) OVER (PARTITION BY A.Customer ORDER BY A.Entry_Date  DESC RESET WHEN  Action = 'WENT-LST' ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Entry_Date_START          
 , CASE WHEN (Action <> 'WENT-LST')
          AND (Entry_Date_START_ACT <= Entry_Date  )
          AND Entry_Date_CARRIER_START = Entry_Date _CARRIER_START_ACT
  THEN 'Y'
  ELSE 'N'

Thanks,

Abin.