query based on a field in the same row

23 Sep 2005 11:49 AM

Helen

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
       A0_01                    1,37
(18-ItemPersonPersonal)*ItemPinax_AMM
       A0_02                    1,4
(18-ItemPersonPersonal)*ItemPinax_AMM

I need to calculate the value of   ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
     ItemPersonAFM ItemPersonCode     ItemPersonPersonal
           041                        A0_01                     10
           041                        A0_02                     12
the answer must be for the first row      (18-10)*1,37
                              for the second row (18-12)*1,4
I use this code

DECLARE @sql VARCHAR(655)
SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode where
ItemPersonAFM='041'
EXEC(@sql)

and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 11:53 AM

R.D

pl.post ddl

Show quote

"Helen" wrote:

> I have two tables ItemsPinax , ItemPerson. The first table has these fields
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
>        A0_01                    1,37
> (18-ItemPersonPersonal)*ItemPinax_AMM
>        A0_02                    1,4
> (18-ItemPersonPersonal)*ItemPinax_AMM
>
> I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
>      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
>            041                        A0_01                     10
>            041                        A0_02                     12
> the answer must be for the first row      (18-10)*1,37
>                               for the second row (18-12)*1,4
> I use this code
>
> DECLARE @sql VARCHAR(655)
> SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode where
> ItemPersonAFM='041'
> EXEC(@sql)
>
> and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:03 PM

Helen

sorry, I don't understand. What to do?

Show quote

"R.D" wrote:

> pl.post ddl
>
> "Helen" wrote:
>
> > I have two tables ItemsPinax , ItemPerson. The first table has these fields
> > ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
> >        A0_01                    1,37
> > (18-ItemPersonPersonal)*ItemPinax_AMM
> >        A0_02                    1,4
> > (18-ItemPersonPersonal)*ItemPinax_AMM
> >
> > I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> > ItemPersonPersonal from table ItemPerson who has these fields
> >      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
> >            041                        A0_01                     10
> >            041                        A0_02                     12
> > the answer must be for the first row      (18-10)*1,37
> >                               for the second row (18-12)*1,4
> > I use this code
> >
> > DECLARE @sql VARCHAR(655)
> > SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> > ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> > ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> > FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode where
> > ItemPersonAFM='041'
> > EXEC(@sql)
> >
> > and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 11:58 AM

Helen

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
        A0_01                    1,37
(18-ItemPersonPersonal)+ItemPinax_AMM
        A0_02                    1,4
(18-ItemPersonPersonal)+ItemPinax_AMM

I need to calculate the value of   ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
     ItemPersonAFM ItemPersonCode     ItemPersonPersonal
            041                        A0_01                     10
            041                        A0_02                     12
the answer must be for the first row      (18-10)+1,37
                               for the second row (18-12)+1,4
I use this code

DECLARE @sql VARCHAR(655)
SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
where   ItemPersonAFM='041'
EXEC(@sql)

and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:01 PM

R.D

Helen
DDL Means table definition and sample data means insert commadns that you
use. so that we can simulate here
Regards
R.D

Show quote

"Helen" wrote:

>
>
>
>
> I have two tables ItemsPinax , ItemPerson. The first table has these fields
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
>         A0_01                    1,37
> (18-ItemPersonPersonal)+ItemPinax_AMM
>         A0_02                    1,4
> (18-ItemPersonPersonal)+ItemPinax_AMM
>
> I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
>      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
>             041                        A0_01                     10
>             041                        A0_02                     12
> the answer must be for the first row      (18-10)+1,37
>                                for the second row (18-12)+1,4
> I use this code
>
> DECLARE @sql VARCHAR(655)
> SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> where   ItemPersonAFM='041'
> EXEC(@sql)
>
> and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:12 PM

R.D

Helen
CREATE TABLE TAB1 (COL1 INT) IS DDL
INSERT INTO TAB1 VALUES(1) is sample data
Regards
R.D

Show quote

"R.D" wrote:

> Helen
> DDL Means table definition and sample data means insert commadns that you
> use. so that we can simulate here
> Regards
> R.D
>
> "Helen" wrote:
>
> >
> >
> >
> >
> > I have two tables ItemsPinax , ItemPerson. The first table has these fields
> > ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
> >         A0_01                    1,37
> > (18-ItemPersonPersonal)+ItemPinax_AMM
> >         A0_02                    1,4
> > (18-ItemPersonPersonal)+ItemPinax_AMM
> >
> > I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> > ItemPersonPersonal from table ItemPerson who has these fields
> >      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
> >             041                        A0_01                     10
> >             041                        A0_02                     12
> > the answer must be for the first row      (18-10)+1,37
> >                                for the second row (18-12)+1,4
> > I use this code
> >
> > DECLARE @sql VARCHAR(655)
> > SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> > ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> > ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> > FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> > where   ItemPersonAFM='041'
> > EXEC(@sql)
> >
> > and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:28 PM

Helen

CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
decimal ,ItemPinax_FUNCTION varchar(100)

INSERT INTO ItemsPinax
VALUES( A0_01,   1.37, 18-ItemPersonPersonal+ItemPinax_AMM)

    CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
varchar(6),   ItemPersonPersonal int)

INSERT INTO ItemPerson VALUES(041, A0_01,   10)

Show quote

> > > I have two tables ItemsPinax , ItemPerson. The first table has these fields
> > > ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
> > >         A0_01                    1,37
> > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > >         A0_02                    1,4
> > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > >
> > > I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> > > ItemPersonPersonal from table ItemPerson who has these fields
> > >      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
> > >             041                        A0_01                     10
> > >             041                        A0_02                     12
> > > the answer must be for the first row      (18-10)+1,37
> > >                                for the second row (18-12)+1,4
> > > I use this code
> > >
> > > DECLARE @sql VARCHAR(655)
> > > SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> > > ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> > > ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> > > FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> > > where   ItemPersonAFM='041'
> > > EXEC(@sql)
> > >
> > > and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:45 PM

R.D

> > the answer must be for the first row (18-10)+1,37
> > > for the second row (18-12)+1,4
> > > I use this code

what is 1,37 and 1,4 ?

I dont really understand what exactly you want calculated feild or
substracted from function definition.
it is already 6 pm here, we will tomorrow
Regards
R.D

Show quote

"Helen" wrote:

> CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
> decimal ,ItemPinax_FUNCTION varchar(100)
>
> INSERT INTO ItemsPinax
> VALUES( A0_01,   1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
>
>     CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
> varchar(6),   ItemPersonPersonal int)
>
> INSERT INTO ItemPerson VALUES(041, A0_01,   10)
>
>
>
> > > > I have two tables ItemsPinax , ItemPerson. The first table has these fields
> > > > ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
> > > >         A0_01                    1,37
> > > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > > >         A0_02                    1,4
> > > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > > >
> > > > I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> > > > ItemPersonPersonal from table ItemPerson who has these fields
> > > >      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
> > > >             041                        A0_01                     10
> > > >             041                        A0_02                     12
> > > > the answer must be for the first row      (18-10)+1,37
> > > >                                for the second row (18-12)+1,4
> > > > I use this code
> > > >
> > > > DECLARE @sql VARCHAR(655)
> > > > SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> > > > ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> > > > ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> > > > FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> > > > where   ItemPersonAFM='041'
> > > > EXEC(@sql)
> > > >
> > > > and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 12:49 PM

Helen

the answer must be for the first row (18-10)+1.37

> > > > for the second row (18-12)+1.4

1.37 AND 1.4 IS THE VALUES FOR ItemPinax_AMM WHICH I USE FOR THE CALCULATION

Show quote

"R.D" wrote:

> > > the answer must be for the first row      (18-10)+1,37
> > > >                                for the second row (18-12)+1,4
> > > > I use this code
>
> what is 1,37 and 1,4 ?
>
> I dont really understand what exactly you want calculated feild or
> substracted from function definition.
> it is already 6 pm here, we will tomorrow
> Regards
> R.D
>
> "Helen" wrote:
>
> > CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
> > decimal ,ItemPinax_FUNCTION varchar(100)
> >
> > INSERT INTO ItemsPinax
> > VALUES( A0_01,   1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
> >
> >     CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
> > varchar(6),   ItemPersonPersonal int)
> >
> > INSERT INTO ItemPerson VALUES(041, A0_01,   10)
> >
> >
> >
> > > > > I have two tables ItemsPinax , ItemPerson. The first table has these fields
> > > > > ItemPinax_CODE,ItemPinax_AMM,ItemPinax_FUNCTION             with values:
> > > > >         A0_01                    1,37
> > > > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > > > >         A0_02                    1,4
> > > > > (18-ItemPersonPersonal)+ItemPinax_AMM
> > > > >
> > > > > I need to calculate the value of   ItemPinax_FUNCTION taking the value of
> > > > > ItemPersonPersonal from table ItemPerson who has these fields
> > > > >      ItemPersonAFM ItemPersonCode     ItemPersonPersonal
> > > > >             041                        A0_01                     10
> > > > >             041                        A0_02                     12
> > > > > the answer must be for the first row      (18-10)+1,37
> > > > >                                for the second row (18-12)+1,4
> > > > > I use this code
> > > > >
> > > > > DECLARE @sql VARCHAR(655)
> > > > > SELECT @sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonPersonal,
> > > > > ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> > > > > ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> > > > > FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> > > > > where   ItemPersonAFM='041'
> > > > > EXEC(@sql)
> > > > >
> > > > > and I get 1,37 and 1,4. How can I have the right answers?

23 Sep 2005 1:22 PM

--CELKO--

This psuedo-code will not work. You have bad syntax, no keys, a column
cannot hold a formula and the names are very confusing. This is not
SQL at all.

Since you did not know what DDL was, that columns are not fields, or
how to design a table, you probably need more help thanyou can get in
a newsgroup. Get someone who knows SQL to put the formula in a VIEW
column after they re-do your schema.
..