***Help***Complex SQLs

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:09:39 CST