Query question (with more information)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query question (with more information)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:04:07

Charles writes "I am working through series of queries in an effort to merge them into one query if possible. The procs to create sample data and the two queries in question are below.

The main proc in question is dbo.a_Statement. First, the union is not returning all of the records. I have been unsuccessful in understanding why. Second, I want to add a column to the results of the a_Statement proc that will show the BBF (balance brought forward) as calculated in the second proc, dbo.CalculateBBF.

There are three procs below.
1. Code to create tables and insert sample data into them:
********** START CODE BLOCK

-- Transaction Types
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_TxType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [a_TxType] (
[RMDTYPAL] [int] NOT NULL ,
[Multiplier] [int] NULL)
GO
-- Customers (Everything but customer number omitted
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [a_Customers] ([CUSTNMBR] [nvarchar] (4))
GO
-- Create Current Transactions table
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_CurrentTransactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [a_CurrentTransactions] (
[CUSTNMBR] [nvarchar] (15) ,
[RMDTYPAL] [smallint] ,
[ORTRXAMT] [numeric](19, 5) ,
[VOIDSTTS] [smallint]
)
GO
-- Create Archived Transactions table
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_ArchivedTransactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [a_ArchivedTransactions] (
[CUSTNMBR] [nvarchar] (15) ,
[RMDTYPAL] [smallint] ,
[ORTRXAMT] [numeric](19, 5) ,
[VOIDSTTS] [smallint]
)
GO
-- Create Aging table
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a_Aging]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [a_Aging] (
[CUSTNMBR] [nvarchar] (15) ,
[CUSTBLNC] [numeric](19, 5) ,
[AGPERAMT_1] [numeric](19, 5) ,
[AGPERAMT_2] [numeric](19, 5) ,
[AGPERAMT_3] [numeric](19, 5) ,
[AGPERAMT_4] [numeric](19, 5)
)
GO
-- Inserting data into Transaction Types table - clear existing data 1st
DELETE FROM a_TxType WHERE RMDTYPAL > 0
GO
INSERT INTO[dbo].[a_TxType] VALUES(1,1)
INSERT INTO[dbo].[a_TxType] VALUES(2,-1)
INSERT INTO[dbo].[a_TxType] VALUES(3,1)
INSERT INTO[dbo].[a_TxType] VALUES(4,0)
INSERT INTO[dbo].[a_TxType] VALUES(5,0)
INSERT INTO[dbo].[a_TxType] VALUES(6,0)
INSERT INTO[dbo].[a_TxType] VALUES(7,-1)
INSERT INTO[dbo].[a_TxType] VALUES(8,-1)
INSERT INTO[dbo].[a_TxType] VALUES(9,-1)
GO
--Inserting data into Customer table - clear existing data 1st
DELETE FROM a_Customers WHERE CUSTNMBR > 0
GO
INSERT INTO [dbo].[a_Customers] VALUES('1000')
INSERT INTO [dbo].[a_Customers] VALUES('2000')
INSERT INTO [dbo].[a_Customers] VALUES('3000')
GO
--Inserting data into Aging table - clear existing data 1st
DELETE FROM a_Aging WHERE CUSTNMBR > 0
GO
INSERT INTO[dbo].[a_Aging]VALUES('1000', '5000', '300', '0','250', '300')
INSERT INTO[dbo].[a_Aging]VALUES('2000', '7000','0' ,'75000','0','0')
INSERT INTO[dbo].[a_Aging]VALUES('3000',' 0', '0','0' ,'0',' 0')
GO
--Inserting data into CurrentTransactions table - clear existing data 1st
DELETE FROM a_CurrentTransactions WHERE CUSTNMBR > 0
GO
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,500, 0)
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,500, 0)
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,1500, 0)
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,1500, 0)
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,11500, 0)
INSERT INTO[dbo].[a_CurrentTransactions] VALUES('1000',1,11500, 0)
INSERT INTO[dbo].[a_Cur

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 03:48:20

Though i cant make out anything from this.

but still, try out Union all if union is not returning all the values.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-02 : 09:23:04

I think you have been caught by the post character limit. Try posting in separate sections.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources