Query help

Hi,
I have a single row in a table:

Title Desc Quantity
----------
aaaa bbbbb 4

and I need the query to insert "Qty" number of records into a second table, e.g

Title1 Desc1
------
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb

I reckon its some sort of self join but any help would be appreciated.

gregFor the example below I use a function, but you can also use any table that contains sequencial numbers with no gaps. A table with IDENTITY field that did not have any deletes would do.

set nocount on
create table t1 (
title char(4) not null,
[desc] varchar(50) not null,
quantity int not null)
go
create table t2 (
title1 char(4) not null,
desc1 varchar(50) not null)
go
insert t1 values ('aaaa', 'bbbbb', 4)
insert t1 values ('bbbb', 'ccccc', 1)
insert t1 values ('cccc', 'ddddd', 3)
insert t1 values ('dddd', 'eeeee', 2)
insert t1 values ('eeee', 'fffff', 5)
go
insert t2
select title, [desc] from dbo.fn_CartesianProduct() f
inner join t1 on f.[id] < t1.quantity order by 1
go
drop table t1, t2
go|||Excellent - I have an Integers table that substitutes nicely.
Thanks.

query for row creation/last update

I'm working on a database migration. The destination DB has date fields in each table for row creation and last update the source DB does not. Is there a system table or way to query the transaction long to find the entry date and last update date for a row using the primary key in the source DB?

Thanks for any helpThere is no builtin audit function for that. The typical audit is to create a trigger and keep track of your insert/update/delete.

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them ?
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','tab le6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
fromT9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.A
If it's a left join, why not just use the count for table9 ?
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them ?
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','tab le6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A
|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else ?
Thanks.

>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
('table1','table2','table3','table4','table5','tab le6','tab
>
>.
>
|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else ?

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them '
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
from T9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.AIf it's a left join, why not just use the count for table9 ?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them '
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','table6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else '
Thanks.
>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
>> I am trying to create a header file that lists the
>> table_name, row_count and date. The count for tables are
>> from the tables themselves (Basic row count)except 2
>> tables that have join statement. How can I include the 2
>> with the join statements with the rest of them '
>>
>> SELECT A.name AS name, B.rows AS [row count],
>> LEFT(GETDATE(), 12) AS date
>> FROM sysobjects A
>> JOIN sysindexes B ON A.id = B.id
>> WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
>> le7','table8')
>> AND B.indid < 2
>> ORDER BY A.name
>>
>> --One of the table with the join
>> SELECT Count(*)
>> from T9 table9
>> left join T10 table10
>> on table9.customer_id = table10.oid
>> T.I.A
>
>.
>|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else '

Query for "latest version" of a given row

I am maintaining historical versions of a row in a table. The idea is that a row is not really modified but a new row is created with a reference to the original row. (I guess a sort of generational view or row versioning...)

My problem is that I cannot come up with a query that will give me a list of the rows with only the latest version of the row included. My confusion is the self join -- the possibility (or lack of info before-hand) of how many parent/child rows there are from a given original row.)

Given the following table and data:

Code Snippet

CREATE TABLE Orders (
[OrderId] INT IDENTITY (1, 1),
[ClientName] VARCHAR(10),
[OriginalOrderId] INT
)
GO

INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Peter', NULL) -- should go in as 1
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Joseph', NULL) -- should go in as 2
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Mary', NULL) -- should go in as 3
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Jane', 2) -- should go in as 4 ("replaces" 2)
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Paul', 4) -- should go in as 5 ("replaces" 4)
GO

SELECT * FROM Orders
-- need a query that will return me rows 1, 3, and 5
GO

DROP TABLE Orders
GO

The query should return me

Peter

Paul

Mary

I think another approach would be to replace the IDENTITY column with just an INT while adding a [Generation] or [Version] column. The join would be a simple self-join that figures out the maximum version value for a given Id. I will use this if I have to but I kinda like the idea of not having to manage the Id values myself.

What you have is fine. You just need to add an ORDER BY clause to the SELECT:

SELECT * FROM Orders
ORDER BY OrderID

or if you want the top 3:

SELECT TOP 3 * FROM Orders

ORDER BY OrderID

Adamus

|||

Ok revisiting this...you want to replace records?

This is done through UPDATE not INSERT.

The solution to your problem is adding a LastModified smalldatetime in conjuntion with the IDENTITY field.

Adamus

|||

Thank you for responding Adamus but you are not reading my post fully.

I am fully aware that UPDATE is for modifying existing records but that is not what I am trying to accomplish. My requirement is that instead of overwriting the existing row with the new columns, I need to insert a new row with the new values with that new row maintaining a "reference" to the original row. What I am looking for is a query that will return that "newest" from the set of chained rows. (It is clearly layed out if you look at the comments in the code snippet.)

|||

To do it with this design would likely require cursors or some other iterative approach. Your second idea is the right way to go as that would be a more set based approach like below:

Code Snippet

CREATE TABLE Orders (
[OrderId] INT IDENTITY (1, 1),
[ClientName] VARCHAR(10),
[ClientID] INT
)

INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Peter', 1) -- should go in as 1
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Joseph', 2) -- should go in as 2
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Mary', 3) -- should go in as 3
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Jane', 2) -- should go in as 4 ("replaces" 2)
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Paul', 2) -- should go in as 5 ("replaces" 4)

SELECT ClientName
FROM
Orders
INNER JOIN
(
SELECT ClientID, MAX(OrderID) AS MaxorderID
FROM Orders
GROUP BY ClientID
) gr
ON Orders.ClientID = gr.ClientID
AND Orders.OrderID = gr.Maxorderid

go

|||

For clarity and organization, this should be accomplished with a dbo.History table.

You should not be trying to accomplish this in a single table. The documentation and query would be a maintenance nightmare.

You shouldn't be trying to re-invent the wheel on this. History tables are common practice.

Adamus

|||

Thanks Dave. I figured that was the way I would have to do it but I like to double check these things. In my case I need to maintain the history order (i.e. Joseph --> Jane --> Paul) so I will get rid of the IDENTITY column and all three of those rows will have the same OrderId value. I will also add a [Generation] column and the set them to 1, 2, 3 respectively (for J-->J-->P).

- Jason

|||

Adamus,

I greatly simplified the issue for purposes of posting. I cannot use a history file in this case because technically the "old" rows are still valid -- just happens that there is a "newer" version available. So in this case they all must be in the same table.

Just curious, how would your suggestion of a history table solve my query question? I would still be in the same boat trying to determine the newest version of a given set of rows. Like David mentioned, you would have to resort to cursors or recursive queries since maintaining the parent-child relationship does not automatically tell you how many levels you need to go down to find the last node.

|||

Jason Callas wrote:

Adamus,

I greatly simplified the issue for purposes of posting. I cannot use a history file in this case because technically the "old" rows are still valid -- just happens that there is a "newer" version available. So in this case they all must be in the same table.

Just curious, how would your suggestion of a history table solve my query question? I would still be in the same boat trying to determine the newest version of a given set of rows. Like David mentioned, you would have to resort to cursors or recursive queries since maintaining the parent-child relationship does not automatically tell you how many levels you need to go down to find the last node.

Jason,

As I have mentioned, it is very common that duplicate records are stored in a history table that houses the duplicate with a timestamp. An Instead Of INSERT trigger is used on the table to check for duplicates and insert duplicates into the history table.

A simply join to the history table will expose the chronology. No cursor is required.

Happy coding,

Adamus

|||

Jason Callas wrote:

Thanks Dave. I figured that was the way I would have to do it but I like to double check these things. In my case I need to maintain the history order (i.e. Joseph --> Jane --> Paul) so I will get rid of the IDENTITY column and all three of those rows will have the same OrderId value. I will also add a [Generation] column and the set them to 1, 2, 3 respectively (for J-->J-->P).

- Jason

(praying to God that I will never have to touch this code)

Adamus

Query Editor: how to display individual row vertically

In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)

\

Field Name 1: < data value 1>

Field Name 2: < data value 2>

Field Name 3: < data value 3>

Field Name 4: < data value 4>

etc.

TIA,

barkingdog

Dog:

If you are using SQL Server 2005, you ought to be able to use an UNPIVOT

Dave

|||

Dog:

That SQL 2005 code might look something like this:

-- -
-- In order for UNPIVOT to work, the data must be homogeneous.
-- For this reason, all of the fields are transformed into a
-- varchar (40) string.
--
-- Also note the ISNULL function on field 3. If this is not
-- done, this row will not be displayed.
-- -
select convert (varchar (25), FieldName + ':') as FieldName,
FieldValue
from ( select convert (varchar (40), 'This is a test.')
as [Field Name 1],
convert (varchar (40), 'This is the 2nd field.')
as [Field Name 2],
convert (varchar (40), isnull (null, '[Null]'))
as [Field Name 3],
convert (varchar (40), 45.27)
as [Field Name 4]
) x
unpivot (FieldValue for FieldName
in ( [Field Name 1], [Field Name 2],
[Field Name 3], [Field Name 4]
)
) as xx

--
-- Sample Output:
--

-- FieldName FieldValue
-- - -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- Field Name 3: [Null]
-- Field Name 4: 45.27

-- (4 row(s) affected)

|||

If you are using SQL 2000 you might try something like:

select 'Field Name 1:' as FieldName,
'This is a test.' as fieldValue
union all
select 'Field Name 2:',
'This is the 2nd field.'
union all
select '...', ' '
union all
select 'Field Name N: ', 'Nth piece of data'

--
-- Sample Output:
--

-- FieldName fieldValue
-- -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- ...
-- Field Name N: Nth piece of data

-- (4 row(s) affected)

Query dataset locally from code

Hi all,
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo

query based on a field in the same row correction

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION 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,ItemPersonP
ersonal,
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?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 fie
lds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION 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,ItemPersonP
ersonal,
> 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?|||Helen
CREATE TABLE TAB1 (COL1 INT) IS DDL
INSERT INTO TAB1 VALUES(1) is sample data
Regards
R.D
"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:
>|||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)|||> > the answer must be for the first row (18-10)+1,37
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)
>
>|||the answer must be for the first row (18-10)+1.37
1.37 AND 1.4 IS THE VALUES FOR ItemPinax_AMM WHICH I USE FOR THE CALCULATION
"R.D" wrote:
> 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:
>

query based on a field in the same row

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION 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,ItemPersonP
ersonal,
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?pl.post ddl
"Helen" wrote:

> I have two tables ItemsPinax , ItemPerson. The first table has these fiel
ds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION 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,ItemPersonP
ersonal,
> 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 whe
re
> ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||sorry, I don't understand. What to do?
"R.D" wrote:
> pl.post ddl
> "Helen" wrote:
>