Temp table and update 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)

Temp table and update help

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-14 : 13:58:41

I am having a little problem getting the right output from 2 queries that I am using. If I use the 2 queries by itself, then I get all the correct data I needed. But what I wanted to do is combine those 2 queries together to get the same result. The problem is that when I combine the 2 queries together, I am getting extra records. My attempt is to create a temp table and insert it with the first query and then update it with the second query. I really have no idea how I would update it with the second query. Here's what I got so far.

IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
WHERE NAME LIKE '#TEMP%')
DROP TABLE #TEMP1
GO

IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
WHERE NAME LIKE '#TEMP%')
DROP TABLE #TEMP2
GO

DECLARE @STARTDATE AS SMALLDATETIME
DECLARE @ENDDATE AS SMALLDATETIME

SET @STARTDATE = '01/01/2004'
SET @ENDDATE = '12/31/2004'
/*----------------------------------
CREATE TABLE
-----------------------------------*/

CREATE TABLE #TEMP1(
CUST_ORDER_ID VARCHAR(15),
WAREHOUSE_ID VARCHAR(15),
INVOICED_DATE SMALLDATETIME,
INVOICE_ID VARCHAR(15),
SHIPPED_QTY DECIMAL(14,4),
UNIT_PRICE DECIMAL(15,6),
TRADE_DISC_PERCENT DECIMAL(6,3),
NAME VARCHAR(50),
CITY VARCHAR(30),
COUNTRY VARCHAR(50),
PART_ID VARCHAR(30),
DESCRIPTION VARCHAR(40),
CUSTOMER_ID VARCHAR(15)
)

CREATE TABLE #TEMP2(
BASE_ID VARCHAR(30),
ACT_LABOR_COST DECIMAL(15,2),
ACT_BURDEN_COST DECIMAL(15,2),
ACT_MATERIAL_COST DECIMAL(15,2),
ACT_SERVICE_COST DECIMAL(15,2),
ID VARCHAR(15),
DEMAND_BASE_ID VARCHAR(30),
CUSTOMER_ID VARCHAR(15)
)

/*-----------------------------------
INSERT INTO TEMP TABLE
------------------------------------*/

BEGIN
INSERT INTO #TEMP1(
CUST_ORDER_ID,
WAREHOUSE_ID,
INVOICED_DATE,
INVOICE_ID,
SHIPPED_QTY,
UNIT_PRICE,
TRADE_DISC_PERCENT,
NAME,
CITY,
COUNTRY,
PART_ID,
DESCRIPTION,
CUSTOMER_ID
)

SELECT COL.CUST_ORDER_ID, COL.WAREHOUSE_ID, S.INVOICED_DATE, S.INVOICE_ID,
SL.SHIPPED_QTY, SL.UNIT_PRICE, COL.TRADE_DISC_PERCENT, C.NAME, C.CITY,
C.COUNTRY, COL.PART_ID, PART.DESCRIPTION, CO.CUSTOMER_ID
FROM SHIPPER_LINE SL INNER JOIN
SHIPPER S ON SL.PACKLIST_ID = S.PACKLIST_ID INNER JOIN
CUST_ORDER_LINE COL ON SL.CUST_ORDER_LINE_NO = COL.LINE_NO
AND S.CUST_ORDER_ID = COL.CUST_ORDER_ID INNER JOIN
CUSTOMER_ORDER CO ON S.CUST_ORDER_ID = CO.ID INNER JOIN
CUSTOMER C ON CO.CUSTOMER_ID = C.ID LEFT OUTER JOIN
PART ON COL.PART_ID = PART.ID
WHERE (S.SHIPPED_DATE BETWEEN @STARTDATE AND @ENDDATE) AND (COL.PRODUCT_CODE IS NOT NULL)
AND (COL.PRODUCT_CODE = 'PARTSTR') AND (COL.CUST_ORDER_ID = 'TR32225')
ORDER BY COL.CUST_ORDER_ID
END

/*-------------------------------------
I WANT THE QUERY BELOW TO UPDATE. NOW
ALL IT DOES IS INSERT, BUT NOT GETTING
THE RESULT I'M AFTER.
-------------------------------------*/

BEGIN
INSERT INTO #TEMP2(
BASE_ID,
ACT_LABOR_COST,
ACT_BURDEN_COST,
ACT_MATERIAL_COST,
ACT_SERVICE_COST,
ID,
DEMAND_BASE_ID,
CUSTOMER_ID
)

SELECT DISTINCT
WORK_ORDER.BASE_ID, WORK_ORDER.ACT_LABOR_COST, WORK_ORDER.ACT_BURDEN_COST, WORK_ORDER.ACT_MATERIAL_COST,
WORK_ORDER.ACT_SERVICE_COST, CUSTOMER_ORDER.ID, DEMAND_SUPPLY_LINK.DEMAND_BASE_ID,
CUSTOMER_ORDER.CUSTOMER_ID
FROM DEMAND_SUPPLY_LINK INNER JOIN
CUST_ORDER_LINE ON DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND
DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN
CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID INNER JOIN
WORK_ORDER ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = WORK_ORDER.BASE_ID AND
DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID = WORK_ORDER.LOT_ID AND DEMAND_SUPPLY_LINK.SUPPLY_SPLIT_ID = WORK_ORDER.SPLIT_ID AND
DEMAND_SUPPLY_LINK.SUPPLY_SUB_ID = WORK_ORDER.SUB_ID INNER JOIN
SHIPPER ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
WHERE (SHIPPER.SHIPPED_DATE BETWEEN @STARTDATE AND @ENDDATE)
AND (DEMAND_SUPPLY_LINK.SUPPLY_TYPE = 'WO')
AND (WORK_ORDER.TYPE = 'W')
AND (WORK_ORDER.SUB_ID = '0')
END

/*--------------------------------------
SELECT FROM TEMP TABLE
---------------------------------------*/
SELECT *
FROM #TEMP1 INNER JOIN #TEMP2 ON
#TEMP1.CUST_ORDER_ID = #TEMP2.DEMAND_BASE_ID

Can anyone help me or advise me on a way of being able to combine 2 queries to get the same result or a workaround.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-14 : 14:04:26

I don't get it....You want to rows from the first select, but if tere are data values from the second one, you want to use those, otherwise use the original?

I would just do a 1 operation join of all the data and use coalesce on the values....or Case

Brett

8-)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-14 : 14:48:42

Maybe I'm not making myself clear. The first query is able to get all the records I need, but when apply with the second query I'm getting extra records. For example the first query will pull 2 records, but if I use with with the second query, it pulls 2 more records making it 4. I need to use the second query because it is pulling some fields that I need in my report. I'm not sure how a Case statement would apply to my select query.

Someone told me that I can first insert my query to a temp table, and then update that temp table with my second query. The problem is I don't know that would work.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-14 : 15:09:17

Just an FYI. I can get it to work in Reporting Services by using subreports. The problem is that subreports can't be exported to excel which is what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources