***Help***Complex Queries***

Could somebody look into my problems given below and try to send me some  solutions, if possible (fast)?

(1) Transpose columns to rows dynamically

 The user is allowed to choose the columns she/he wishes to pick thru a front end (Forms 4.5) or in SQL+. Right now I want to try it through SQL+. So, say the use user picks up proj, prot, labcode(s), labresult(s), maxvalue(s), minvalue(s), labdate(s). So u see the columns chosen could be multiple and as such the rows become much more. This results in a lot more columns per proj, prot, labcode, labdate. We are using Oracle 8.3, so I believe there could be a maximum of 1000 columns. In order to illustrate the problem I have taken the example explicitly.

 So table A contains many columns some of which are -

    Proj  Prot  Pid   Labcode  Labdt      Rslt    Maxv      Minv
    001    1    1     001      10-Jan-83  100.00  1000.00   100.00
    001    1    1     002      10-Jan-83  200.00  1000.00   200.00
    001    1    1     003      10-Jan-83  300.00  2000.00   300.00
    001    1    1     002      12-Jan-83  150.00  1000.00   100.00
    001    1    1     004      12-Jan-83  250.00  1000.00   200.00
    001    1    2     001      10-Jan-83  150.00  1000.00   100.00
    001    1    2     002      10-Jan-83  112.00  1000.00   200.00
    001    1    2     003      10_Jan-83  150.00  2000.00   300.00
    001    1    2     004      10-Jan-83  102.00  1000.00   200.00

 Table B contains Labcode and desciption

 Labcode Description

 001       RBC
 002       WBC
 003       HRD
 004       HGR
 005       RTY

 So table B might contain about say 50 labcodes and their description. This table
 is needed in the transposed table to get the description for the labcodes chosen
 by the user

 So once the user selects the query like for proj=001, prot=1, pid=all,  labcode=all, labdt between 10-Jan-83 and 15-Jan-83 give me rslt and maxv. So  the transposed table (say Table C) would look something like this:-

 Proj Prot Pid  Labdt      RBC(Rslt) RBC(Maxv)  WBC(Rslt)  WBC(Maxv) ..
 001    1    1  10-Jan-83  100.00    1000.00    200.00     1000.00   ..
 001    1    1  12-Jan-83                       150.00     1000.00   ..
 001    1    2  10-Jan-83   150.00   1000.00    112.00     1000.00   ..

 So as u see if the user wants to query for 2 columns(Rslt and Maxv for each labcode), each row spans across by a multiplication factor of 2. That means if there are 10 labcodes, there would be 10*2 (20) columns horizontally, in addition to the columns like Proj, Prot, Pid, Labdt. The heading for each column should be constructed dynamically as has been shown in Table C.

(2) Updating a value depending on comparison.

 Table A is as follows:-

 Labcode         Labstd          Sfct_obs
 01              7
 01              4
 01              10
 01              6
 ...             ...             ...
 02              ...             ...

 Table B is :-

 Labcode         Lowerlimit      Upperlimit      Sfactor
 01              4               Null            Null
 01              5               7               10
 01              Null            9               15
 01              Null            Null            Null

 Now, the query should pick all the records from Table A one by one, compare the value of Labstd with the Sfactor of Table B (4 conditions) and update the value of Sfct_obs with Sfactor.

 The pseudo-code is :-

 Update Sfct_obs(in Table A) with Sfactor (from Table B)

 Where labcode in Table A = labcode in Table B  And

 If
(lowerlimit is not null and

  Upperlimit is not null and
  lowerlimit < labstd < upperlimit)

 elseif
(lowerlimit is not null and

  Upperlimit is null and
  labstd <= lowerlimit)

 elseif
(lowerlimit is null and

  Upperlimit is not null and
  labstd >= upperlimit)

 elsif
(lowerlimit is null and

  Upperlimit is null)

 else
  dont change the value of sfct_obs

 Thanks in advance.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Dec 13 1998 - 23:11:10 CST