SQL Query Help - SQL Server Forums

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

Transact-SQL (2000)

SQL Query Help

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-28 : 15:16:17

I'm trying to get this query to work in SQL Analyzer, but I'm stuck. Here's the problem. I am migrating some SQL queries from ASP to reporting service and in my ASP I can reference objects. For example I would reference it base on headings. The problem is I can't reference objects in Reporting Services, so I need to do it at the SQL level.

Example:
SELECT CUST_ID, EMP_ID, SALES_ID
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '3232'

The second query will display the Sales_Amount base on the object from the first query.

SELECT SALES_AMOUNT
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
AND SALES.ID = '"&SALES_ID&"'

These are the objects:

'"&CUST_ID&"'
'"&EMP_ID&"'
'"&SALES_ID&"'

Below is a CREATE TABLE and INSERT query of some of the TABLES, FIELDS, and DATA that I'm trying to work with. I am desperately in need of some expert help here. The query below will create 5 tables and insert some sample data into it. After that there will be two queries that I'm currently working with. Both produce the result that I'm after, but when combined together it will not display those that have NULL values for the second query. I'm fairly new to working with MS SQL so please bear with me.

The 3rd query at the bottom is the expected results that I'm trying to get the two queries to do.

DROP TABLE PART
DROP TABLE REQUIREMENT_BINARY
DROP TABLE REQUIREMENT
DROP TABLE DEMAND_SUPPLY_LINK
DROP TABLE PURCHASE_ORDER

CREATE TABLE [PART] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DESCRIPTION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STOCK_UM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO

CREATE TABLE [REQUIREMENT] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[WORKORDER_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_BASE_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_LOT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_SPLIT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_SUB_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OPERATION_SEQ_NO] [smallint] NOT NULL ,
[PIECE_NO] [smallint] NOT NULL ,
[SUBORD_WO_SUB_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PART_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QTY_PER] [decimal](15, 8) NOT NULL ,
[CALC_QTY] [decimal](14, 4) NULL ,
[ISSUED_QTY] [decimal](14, 4) NOT NULL CONSTRAINT [DF__REQUIREME__ISSUE__6D381B7D] DEFAULT (0),
[EST_MATERIAL_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__EST_M__73E5190C] DEFAULT (0),
[EST_LABOR_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__EST_L__74D93D45] DEFAULT (0),
[EST_BURDEN_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__EST_B__75CD617E] DEFAULT (0),
[ACT_MATERIAL_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__ACT_M__7B863AD4] DEFAULT (0),
[ACT_LABOR_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__ACT_L__7C7A5F0D] DEFAULT (0),
[ACT_BURDEN_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__REQUIREME__ACT_B__7D6E8346] DEFAULT (0),
CONSTRAINT [PK_REQUIREMENT] PRIMARY KEY CLUSTERED
(
[WORKORDER_TYPE],
[WORKORDER_BASE_ID],
[WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID],
[WORKORDER_SUB_ID],
[OPERATION_SEQ_NO],
[PIECE_NO]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO

CREATE TABLE [REQUIREMENT_BINARY] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[WORKORDER_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_BASE_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_LOT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_SPLIT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WORKORDER_SUB_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OPERATION_SEQ_NO] [smallint] NOT NULL ,
[PIECE_NO] [smallint] NOT NULL ,
[TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BITS] [image] NULL ,
[BITS_LENGTH] [int] NOT NULL ,
CONSTRAINT [PK_REQUIREMENT_BINARY] PRIMARY KEY CLUSTERED
(
[WORKORDER_TYPE],
[WORKORDER_BASE_ID],
[WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID],
[WORKORDER_SUB_ID],
[OPERATION_SEQ_NO],
[PIECE_NO],
[TYPE]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FKEY0141] FOREIGN KEY
(
[WORKORDER_TYPE],
[WORKORDER_BASE_ID],
[WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID],
[WORKORDER_SUB_ID],
[OPERATION_SEQ_NO],
[PIECE_NO]
) REFERENCES [REQUIREMENT] (
[WORKORDER_TYPE],
[WORKORDER_BASE_ID],
[WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID],
[WORKORDER_SUB_ID],
[OPERATION_SEQ_NO],
[PIECE_NO]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [CHK0031] CHECK ([TYPE] = 'D' or [TYPE] = 'B')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [DEMAND_SUPPLY_LINK] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[ID] [int] NOT NULL ,
[DEMAND_BASE_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DEMAND_LOT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEMAND_SPLIT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEMAND_SUB_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEMAND_SEQ_NO] [int] NULL ,
[DEMAND_NO] [int] NULL ,
[SUPPLY_BASE_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUPPLY_LOT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPLY_SPLIT_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPLY_SUB_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPLY_SEQ_NO] [int] NULL ,
[SUPPLY_NO] [int] NULL ,
[DEMAND_PART_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPLY_PART_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_DEMAND_SUPPLY] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [PURCHASE_ORDER] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VENDOR_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__PURCHASE___STATU__522F1F86] DEFAULT (' '),
CONSTRAINT [PK_PURCHASE_ORDER] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO

INSERT INTO [PART]([ID], [DESCRIPTION], [STOCK_UM])
VALUES('WGKT-SI-.035', 'PLASTIC SHEET', 'EA')
GO
INSERT INTO [PART]([ID], [DESCRIPTION], [STOCK_UM])
VALUES('WGKT-SI','STEEL SHEET', 'LB')
GO
INSERT INTO [PART]([ID], [DESCRIPTION], [STOCK_UM])
VALUES('SGH-310','BETA FLANGE','EA')
GO
INSERT INTO [PART]([ID], [DESCRIPTION], [STOCK_UM])
VALUES('SGH-310-L22','STEEL ROD','')
GO

INSERT INTO [REQUIREMENT]([WORKORDER_TYPE], [WORKORDER_BASE_ID], [WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [SUBORD_WO_SUB_ID], [PART_ID],
[QTY_PER], [CALC_QTY], [ISSUED_QTY], [EST_MATERIAL_COST], [EST_LABOR_COST], [EST_BURDEN_COST], [ACT_MATERIAL_COST],
[ACT_LABOR_COST], [ACT_BURDEN_COST])
VALUES('W','32259','1','0','34','10','60','','WGKT-SI','1.000','2.000','.000','.00','.00','.00','.00','.00','.00')
GO
INSERT INTO [REQUIREMENT]([WORKORDER_TYPE], [WORKORDER_BASE_ID], [WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [SUBORD_WO_SUB_ID], [PART_ID],
[QTY_PER], [CALC_QTY], [ISSUED_QTY], [EST_MATERIAL_COST], [EST_LABOR_COST], [EST_BURDEN_COST], [ACT_MATERIAL_COST],
[ACT_LABOR_COST], [ACT_BURDEN_COST])
VALUES('W','32259','1','0','34','10','10','','','1.000','2.000','.000','.00','.00','.00','.00','.00','.00')
GO
INSERT INTO [REQUIREMENT]([WORKORDER_TYPE], [WORKORDER_BASE_ID], [WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [SUBORD_WO_SUB_ID], [PART_ID],
[QTY_PER], [CALC_QTY], [ISSUED_QTY], [EST_MATERIAL_COST], [EST_LABOR_COST], [EST_BURDEN_COST], [ACT_MATERIAL_COST],
[ACT_LABOR_COST], [ACT_BURDEN_COST])
VALUES('W','32259','1','0','34','20','60','','SGH-310','2.000','2.000','.000','.00','.00','.00','.00','.00','.00')
GO
INSERT INTO [REQUIREMENT]([WORKORDER_TYPE], [WORKORDER_BASE_ID], [WORKORDER_LOT_ID],
[WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [SUBORD_WO_SUB_ID], [PART_ID],
[QTY_PER], [CALC_QTY], [ISSUED_QTY], [EST_MATERIAL_COST], [EST_LABOR_COST], [EST_BURDEN_COST], [ACT_MATERIAL_COST],
[ACT_LABOR_COST], [ACT_BURDEN_COST])
VALUES('W','32259','1','0','34','30','60','','','2.000','2.000','.000','.00','.00','.00','.00','.00','.00')
GO

INSERT INTO [REQUIREMENT_BINARY]([WORKORDER_TYPE], [WORKORDER_BASE_ID],
[WORKORDER_LOT_ID], [WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [TYPE],
[BITS], [BITS_LENGTH])
VALUES('W','32259','1','0','0','10','60','D','0x434F4E54524F4C53', '9')
GO
INSERT INTO [REQUIREMENT_BINARY]([WORKORDER_TYPE], [WORKORDER_BASE_ID],
[WORKORDER_LOT_ID], [WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [TYPE],
[BITS], [BITS_LENGTH])
VALUES('W','32259','1','0','0','10','10','D','0x534D414C4C205041525453', '12')
GO
INSERT INTO [REQUIREMENT_BINARY]([WORKORDER_TYPE], [WORKORDER_BASE_ID],
[WORKORDER_LOT_ID], [WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [TYPE],
[BITS], [BITS_LENGTH])
VALUES('W','32259','1','0','0','20','60','D','0x48415443482D4241434B2057494E444F57532C200D0A28312920333720312F3422205820333020312F34220D0A28312920333720312F3422205820343620312F3422', '67')
GO
INSERT INTO [REQUIREMENT_BINARY]([WORKORDER_TYPE], [WORKORDER_BASE_ID],
[WORKORDER_LOT_ID], [WORKORDER_SPLIT_ID], [WORKORDER_SUB_ID], [OPERATION_SEQ_NO], [PIECE_NO], [TYPE],
[BITS], [BITS_LENGTH])
VALUES('W','32259','1','0','0','30','60','D','0x4D414348494E4544205041525453', '15')
GO

INSERT INTO [DEMAND_SUPPLY_LINK]([ID], [DEMAND_BASE_ID], [DEMAND_LOT_ID], [DEMAND_SPLIT_ID], [DEMAND_SUB_ID],
[DEMAND_SEQ_NO], [DEMAND_NO], [SUPPLY_BASE_ID], [SUPPLY_LOT_ID], [SUPPLY_SPLIT_ID], [SUPPLY_SUB_ID], [SUPPLY_SEQ_NO],
[SUPPLY_NO], [DEMAND_PART_ID], [SUPPLY_PART_ID])
VALUES('14199','32259', '1', '0', '34', '10', '60', 'CBS23996', '', '', '', '1','', 'WGKT-SI', 'WGKT-SI-.33')
GO
INSERT INTO [DEMAND_SUPPLY_LINK]([ID], [DEMAND_BASE_ID], [DEMAND_LOT_ID], [DEMAND_SPLIT_ID], [DEMAND_SUB_ID],
[DEMAND_SEQ_NO], [DEMAND_NO], [SUPPLY_BASE_ID], [SUPPLY_LOT_ID], [SUPPLY_SPLIT_ID], [SUPPLY_SUB_ID], [SUPPLY_SEQ_NO],
[SUPPLY_NO], [DEMAND_PART_ID], [SUPPLY_PART_ID])
VALUES('14217','32259', '1', '0', '34', '10', '10', 'CBS24038', '', '', '', '2','', '', '')
GO
INSERT INTO [DEMAND_SUPPLY_LINK]([ID], [DEMAND_BASE_ID], [DEMAND_LOT_ID], [DEMAND_SPLIT_ID], [DEMAND_SUB_ID],
[DEMAND_SEQ_NO], [DEMAND_NO], [SUPPLY_BASE_ID], [SUPPLY_LOT_ID], [SUPPLY_SPLIT_ID], [SUPPLY_SUB_ID], [SUPPLY_SEQ_NO],
[SUPPLY_NO], [DEMAND_PART_ID], [SUPPLY_PART_ID])
VALUES('14218','32259', '1', '0', '34', '20', '60', 'CBS22008', '', '', '', '3','', 'SGH-310', 'SGH-.33')
GO
INSERT INTO [DEMAND_SUPPLY_LINK]([ID], [DEMAND_BASE_ID], [DEMAND_LOT_ID], [DEMAND_SPLIT_ID], [DEMAND_SUB_ID],
[DEMAND_SEQ_NO], [DEMAND_NO], [SUPPLY_BASE_ID], [SUPPLY_LOT_ID], [SUPPLY_SPLIT_ID], [SUPPLY_SUB_ID], [SUPPLY_SEQ_NO],
[SUPPLY_NO], [DEMAND_PART_ID], [SUPPLY_PART_ID])
VALUES('16120','32259', '1', '0', '34', '30', '60', 'CBS14008', '', '', '', '4','', '', '')
GO

INSERT INTO [PURCHASE_ORDER]([ID], [VENDOR_ID], [STATUS])
VALUES('CBS23996', '467500', 'C')
GO
INSERT INTO [PURCHASE_ORDER]([ID], [VENDOR_ID], [STATUS])
VALUES('CBS24038', '160500', 'C')
GO
INSERT INTO [PURCHASE_ORDER]([ID], [VENDOR_ID], [STATUS])
VALUES('CBS2208', '160500', 'C')
GO
INSERT INTO [PURCHASE_ORDER]([ID], [VENDOR_ID], [STATUS])
VALUES('CBS14008', '160500', 'C')
GO

--QUERY 1:

SELECT
R.PART_ID, P.DESCRIPTION, R.WORKORDER_SUB_ID,
R.OPERATION_SEQ_NO, R.PIECE_NO, QTY_PER,CALC_QTY,R.ISSUED_QTY, P.STOCK_UM,
EST_MATERIAL_COST+EST_LABOR_COST+EST_BURDEN_COST AS ESTIMATED_COST,
R.ACT_LABOR_COST+R.ACT_BURDEN_COST+R.ACT_MATERIAL_COST AS ACTUAL_COST
FROM
REQUIREMENT R,PART P, REQUIREMENT_BINARY RB
WHERE R.WORKORDER_BASE_ID='32259'
AND R.PART_ID*=P.ID
AND R.WORKORDER_BASE_ID*=RB.WORKORDER_BASE_ID
AND R.WORKORDER_SUB_ID*=RB.WORKORDER_SUB_ID
AND R.OPERATION_SEQ_NO*=RB.OPERATION_SEQ_NO
AND R.PIECE_NO*=RB.PIECE_NO
AND R.SUBORD_WO_SUB_ID =''
ORDER BY R.PART_ID,R.WORKORDER_SUB_ID,R.OPERATION_SEQ_NO

--QUERY 2:
SELECT TOP 1 SUPPLY_BASE_ID, SUPPLY_SEQ_NO, PO.STATUS
FROM DEMAND_SUPPLY_LINK DSL, PURCHASE_ORDER PO, REQUIREMENT R
WHERE
DSL.DEMAND_BASE_ID='32259'
AND DSL.SUPPLY_BASE_ID=PO.ID
AND DSL.DEMAND_SUB_ID=R.WORKORDER_SUB_ID
AND DSL.DEMAND_SEQ_NO=R.OPERATION_SEQ_NO
AND DSL.DEMAND_NO=R.PIECE_NO

--WHAT I KIND OF WANT:
SELECT
R.PART_ID, P.DESCRIPTION, R.WORKORDER_SUB_ID,
R.OPERATION_SEQ_NO, R.PIECE_NO, QTY_PER,CALC_QTY,R.ISSUED_QTY, P.STOCK_UM,
EST_MATERIAL_COST+EST_LABOR_COST+EST_BURDEN_COST AS ESTIMATED_COST,
R.ACT_LABOR_COST+R.ACT_BURDEN_COST+R.ACT_MATERIAL_COST AS ACTUAL_COST,
SUPPLY_BASE_ID, SUPPLY_SEQ_NO, PO.STATUS
FROM
REQUIREMENT R,PART P, REQUIREMENT_BINARY RB, DEMAND_SUPPLY_LINK DSL, PURCHASE_ORDER PO
WHERE R.WORKORDER_BASE_ID='32259'
AND R.PART_ID*=P.ID
AND R.WORKORDER_BASE_ID*=RB.WORKORDER_BASE_ID
AND R.WORKORDER_SUB_ID*=RB.WORKORDER_SUB_ID
AND R.OPERATION_SEQ_NO*=RB.OPERATION_SEQ_NO
AND R.PIECE_NO*=RB.PIECE_NO
AND R.SUBORD_WO_SUB_ID =''
AND DSL.DEMAND_BASE_ID=R.WORKORDER_BASE_ID
AND DSL.SUPPLY_BASE_ID=PO.ID
AND DSL.DEMAND_SUB_ID=R.WORKORDER_SUB_ID
AND DSL.DEMAND_SEQ_NO=R.OPERATION_SEQ_NO
AND DSL.DEMAND_NO=R.PIECE_NO
ORDER BY R.PART_ID,R.WORKORDER_SUB_ID,R.OPERATION_SEQ_NO

--HERE'S HOW IT SHOULD BE, BUT I CAN'T SEEM TO GET IT TO WORK:

SET NOCOUNT ON

DECLARE @TEMP TABLE(PART_ID VARCHAR(20) NOT NULL, Description VARCHAR(80) NOT NULL, WORKORDER_SUB_ID VARCHAR(04) NOT NULL,
OPERATION_SEQ_NO VARCHAR(04) NOT NULL,PIECE_NO VARCHAR(04) NOT NULL, CALC_QTY VARCHAR(04) NOT NULL, ISSUED_QTY VARCHAR(04) NOT NULL,
STOCK_UM VARCHAR(04) NOT NULL, ESTIMATED_COST VARCHAR(10) NOT NULL, ACTUAL_COST NVARCHAR(10) NOT NULL, SUPPLY_BASE_ID NVARCHAR(10),
SUPPLY_SEQ_NO NVARCHAR(10), STATUS NVARCHAR(04))

INSERT INTO @TEMP
SELECT '', '', '34', '20', '10', '1.00','2.00','','.00', '.00','CBS24038','2','C' UNION ALL
SELECT '', '','34','30','60','2.00','2.00','','.00','.00','CB14008','4','C' UNION ALL
SELECT 'SGH-310', 'BETA FLANGE','34','20','60','2.00','2.00','EA','.00','.00','','','' UNION ALL
SELECT 'WGKT-SI', 'STEEL SHEET','34','10','60','1.00','2.00','LB','.00','.00','CBS23996','1','C'

SELECT PART_ID, DESCRIPTION, WORKORDER_SUB_ID, OPERATION_SEQ_NO, PIECE_NO, CALC_QTY, ISSUED_QTY, STOCK_UM,
ESTIMATED_COST, ACTUAL_COST, SUPPLY_BASE_ID + '/' +
SUPPLY_SEQ_NO + '-' + STATUS AS PO#
FROM
(
SELECT PART_ID, DESCRIPTION, WORKORDER_SUB_ID, OPERATION_SEQ_NO, PIECE_NO, CALC_QTY, ISSUED_QTY, STOCK_UM,
ESTIMATED_COST, ACTUAL_COST, SUPPLY_BASE_ID,
SUPPLY_SEQ_NO,STATUS
FROM @TEMP
GROUP BY PART_ID, DESCRIPTION, WORKORDER_SUB_ID, OPERATION_SEQ_NO, PIECE_NO, CALC_QTY, ISSUED_QTY, STOCK_UM,
ESTIMATED_COST, ACTUAL_COST, SUPPLY_BASE_ID,
SUPPLY_SEQ_NO,STATUS
) t
GROUP BY PART_ID, DESCRIPTION, WORKORDER_SUB_ID, OPERATION_SEQ_NO, PIECE_NO, CALC_QTY, ISSUED_QTY, STOCK_UM,
ESTIMATED_COST, ACTUAL_COST, SUPPLY_BASE_ID,
SUPPLY_SEQ_NO,STATUS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-28 : 15:28:26

you need to read up on LEFT OUTER JOINs in books on line, and get away from defining joins in your WHERE clauses. Your WHERE clause should only include fitlering criteria; your relationships in your query should be done using JOIN syntax.

read up on it in books on line, do some googling if needed, check out a book on beginning SQL Server. experiment with sample data, such as Northwind.

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 09:15:32

I'm an old Oracle user so I'm still in the habit of using my joins with my Where clauses.

As with the SQL below, it is using a left outer join, but when I try to do a left outer join with my second query it gives me an aggregate error. That's where the problem lies. I need to be able to combine the 2 queries and get the expected result. I have been working on this one problem for days and hopefully someone with experience can point me to the right direction.

Thanks for you reply though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources