4 Replies Latest reply: Aug 13, 2012 7:07 AM by madsupcom RSS

    DB2 script => loading an joining multiple tables, and handling 'null' vs missing values



      First, i'd like to thank you for reading my post, and all contributors Qview community wich has already helped me a lot in my guesses about scripting.




      Then, to business ...


      I'm loading lots  of DB2 tables (among other things) ... And doing so, i got a big problem, that i'll try to explain as :


      I got multiple tables, for each type of data i'm willing to use. For instance, 'client' is spread among 4 main tables each containing different items


      ie : cl-tab1containing (name, adress, etc.), cl-tab2 (containing mail, telephone), cl-tab3 (containing date of birth, etc.)


      I'm loading and chaining them with no problem using the primary keys used by my db2 system... No problem, or so i thought.


      => some clients doesnt have ANY data in one or more category : so, the primary key doesnt exist in this specific table

      (ex : no date of birth => client doesn't exist in cl-tab3...)


      => no primary key => no data for these items and to my surprise, 'NO DATA' <> Null.

      I can manipulate these items, and for instance count null occurrences, but this wont count 'no data occurence'


      Am i doing something wrong ?


      I tried lots of scripts, functions, null propagation doesnt allow me to adress this problem.


      My last try was to join these tables (two of them, as a test) and no luck... I got 'null' for existing rows with no values, but i got 'nill' for unexisting rows !


      Thx in advance.