January 28, 2022 at 2:16 am
Hello.
I am trying to UNION ALL two tables 1) Current receipts 2) Historical receipts. Both of those tables have CTE because it was the only way how could it change numeric field into date field.
This is my Current receipts table
;WITH t AS
(SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
FROM [Repit].[LEVYDTA].[RECTRNT] r)
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
(DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
,CASE WHEN wm.[ITPPCK] = 'B' THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
ON t.[RCITM#]=wm.[ITITM#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
ON wm.[ITVEN#]=wv.[VNVEN#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON t.[RCWHS#]=ws.[WHWHS#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
ON t.[RCITM#]=wp.[PPPPK#]
WHERE ws.WHAFLG = 'Y'
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
ORDER BY wm.ITPPCK DESC
This is my Historical receipts
;WITH t AS
(
SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
FROM [Repit].[LEVYDTA].[RECTRNH] r
)
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
,CASE WHEN wm.[ITPPCK] = 'B' THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
ON t.[RCITM#]=wm.[ITITM#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
ON wm.[ITVEN#]=wv.[VNVEN#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON t.[RCWHS#]=ws.[WHWHS#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
ON t.[RCITM#]=wp.[PPPPK#]
WHERE ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;
All the column names are the same. I thought that I just need to put UNION ALL in between and put GROUP BY and ORDER BY at the end and it would make it work. However, if I leave both CTEs with Proper Date it basically gives me an error "Incorrect syntax near the line with the second CTE after UNION ALL".
I know it looks like a lot of text, but it is exactly the same fields and same joins between two tables, only difference between both of those 2 tables is that 1 one is from a table [RECTRNT] - recent receipts and 2nd one is from [RECTRNH] - historic receipts.
Everything else is the same.
I will appreciate any ideas.
January 28, 2022 at 3:32 am
I don't think you can use the * wildcard with UNION or UNION ALL. You need to explicitly list the columns.
January 28, 2022 at 1:39 pm
So, you're jamming that semi-colon, which is a statement TERMINATOR, at the front of your WITH clause. Are you doing that on the second query? That could be the issue. The entire statement is the UNION ALL query, so the TERMINATOR would go at the very end. Having the TERMINATOR in the middle will cause problems.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2022 at 2:28 pm
As a side note - Why are you using a LEFT JOIN and a WHERE?
In both queries you use
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON t.[RCWHS#] = ws.[WHWHS#]
WHERE ws.WHAFLG = 'Y'
This is equivalent to an INNER JOIN and can be rewritten as follows
INNER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON t.[RCWHS#] = ws.[WHWHS#] AND ws.WHAFLG = 'Y'
January 28, 2022 at 2:56 pm
I would look at writing the query in a manner similar to this
WITH cteBase AS (
SELECT TOP ( 9223372036854775807 ) /* Force SQL to evaluate the cte first */
*
, ProperDate = CASE WHEN ISDATE( CONVERT( char(8), h.RCLDTE )) = 1 THEN CONVERT( date, CONVERT( char(8), h.RCLDTE )) END
FROM Repit.LEVYDTA.RECTRNH AS h /* Historical data */
/* Pre-filter the data to reduce the number of records processed */
/* However, this will still cause a table|index scan */
WHERE CASE WHEN ISDATE( CONVERT( char(8), h.RCLDTE )) = 1 THEN CONVERT( date, CONVERT( char(8), h.RCLDTE )) END
BETWEEN @Last2WeekDATE AND @LWDATE
UNION ALL
SELECT *
, ProperDate = CASE WHEN ISDATE( CONVERT( char(8), c.RCLDTE )) = 1 THEN CONVERT( date, CONVERT( char(8), c.RCLDTE )) END
FROM Repit.LEVYDTA.RECTRNT AS c /* Current Data */
)
SELECT [Day of Week] = t.ProperDate
, Saturday = DATEADD( DAY, 6 - (( DATEPART( WEEKDAY, t.ProperDate ) + @@DATEFIRST - 1 ) % 7 ), t.ProperDate )
, PREPACKQTY = CASE WHEN wm.ITPPCK = 'B' THEN wp.PPCQTY * SUM( t.RCRQTY ) ELSE 0 END
, wp.PPCQTY
, RCRQTY = SUM( t.RCRQTY )
, t.RCLDTE
, t.RCWHS#
, t.RCITM#
, wm.ITPPCK
, wm.ITCSPK
, ws.WHDNAM
, wv.VNVEN#
, wv.VNVENN
FROM cteBase AS t
INNER JOIN [Repit].[LEVYDTA].[WHSWHSM] AS ws ON t.[RCWHS#] = ws.[WHWHS#] AND ws.WHAFLG = 'Y'
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] AS wm ON t.[RCITM#] = wm.[ITITM#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] AS wv ON wm.[ITVEN#] = wv.[VNVEN#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] AS wp ON t.[RCITM#] = wp.[PPPPK#]
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK, ws.WHDNAM, wv.VNVEN#, wv.VNVENN
ORDER BY wm.ITPPCK DESC;
January 28, 2022 at 3:32 pm
Your use of ISDATE may not work as you think. I would suggest using TRY_CONVERT. Also, what happens if the test for a valid date is false? You code will return NULL. Is that what you want?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 3, 2022 at 1:56 am
>> I am trying to UNION ALL two tables 1) Current receipts 2) Historical receipts. Both of those tables have CTE because it was the only way how could it change the numeric field [sic] into a DATE field [sic]. <<
The first obvious question is why do you think a current receipt is a totally different kind of data element from a historical receipt? In a properly designed table, the receipt date would determine whether the receipt was current or historical. This is why we have VIEWs in SQL. Your mindset is still back with punch cards and magnetic tape! Confusing a physical storage problem with the logical design of the system. Did you ever work with magnetic tapes? A big part of the effort was doing exactly what you're doing with your union all, only we had to do it with the physical tapes and file merges.
In fact, you don't seem to know what a field is in SQL! That's why you're confusing it with the field on that magnetic tape.
Finally, if you've got a date and a strongly typed language like SQL that has temporal data types, why are you doing this at all? One of our little rules for programming in SQL is from Berent Ozar; store data the way you use it and use data the way it's stored. This inability to properly program with datatypes reminds me of one of my favorite T-shirts. The slogan is "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"
Please post DDL and follow ANSI/ISO standards when asking for help.
February 3, 2022 at 5:49 am
The first obvious question is why do you think a current receipt is a totally different kind of data element from a historical receipt?
I guess you've never heard of a history or system versioned temporal table.
--Jeff Moden
February 3, 2022 at 9:56 am
You cannot union two WITH statements together. You also cannot have an order by within a union all unless you also use TOP to limit the rows though you could limit them to a really large number like 1000000000. Also, the columns must match type and position in the results you are unioning together.
I would write the the statement within a single WITH:
;WITH t AS
(
SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
FROM [Repit].[LEVYDTA].[RECTRNT] r
),
u AS
(
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
(DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
,CASE WHEN wm.[ITPPCK] = 'B' THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT JOIN [Repit].[LEVYDTA].[WHSITMM] wm
ON t.[RCITM#]=wm.[ITITM#]
LEFT JOIN [Repit].[LEVYDTA].[WHSVENM] wv
ON wm.[ITVEN#]=wv.[VNVEN#]
LEFT JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON t.[RCWHS#]=ws.[WHWHS#]
LEFT JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
ON t.[RCITM#]=wp.[PPPPK#]
WHERE ws.WHAFLG = 'Y'
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK, ws.WHDNAM, wv.VNVEN#, wv.VNVENN
),
v AS
(
SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
FROM [Repit].[LEVYDTA].[RECTRNH] r
),
w AS
(
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
,CASE WHEN wm.[ITPPCK] = 'B' THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
,v.RCLDTE, v.RCWHS#, v.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM v
LEFT JOIN [Repit].[LEVYDTA].[WHSITMM] wm
ON v.[RCITM#]=wm.[ITITM#]
LEFT JOIN [Repit].[LEVYDTA].[WHSVENM] wv
ON wm.[ITVEN#]=wv.[VNVEN#]
LEFT JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
ON v.[RCWHS#]=ws.[WHWHS#]
LEFT JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
ON v.[RCITM#]=wp.[PPPPK#]
WHERE ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY v.ProperDate, wm.ITPPCK, wp.PPCQTY, v.RCRQTY, v.RCLDTE, v.RCWHS#, v.RCITM#, wm.ITCSPK,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;
)
SELECT * FROM u
UNION ALL
SELECT * FROM w
ORDER BY ITPPCK DESC
February 3, 2022 at 4:44 pm
Yes, and the history table should be a single table with columns that indicate the start and end times of a state of being of the data element being modeled. It should not be a separate table. If I need the most current status of this entity, then I look for (event_end_time IS NULL). I need to write some of Kuznetsov's constraints to guarantee my history is continuous, and probably want to have a view that shows my current status.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 3, 2022 at 8:46 pm
Yes, and the history table should be a single table with columns that indicate the start and end times of a state of being of the data element being modeled. It should not be a separate table. If I need the most current status of this entity, then I look for (event_end_time IS NULL). I need to write some of Kuznetsov's constraints to guarantee my history is continuous, and probably want to have a view that shows my current status.
Gosh, no, Joe. I agree with the Start and End time column in the history table except that it's not really an END time... it should be the date/time when the new row came into being and you can use a nice Closed/Open search whether you have two tables or just one. If the Current row has a NULL in the "end" column, that will mean that you have to look for an end date that ISN'T NULL an end date that IS NULL to do simple PIT (Point-in-Time) queries and that's going to involve an OR or something worse.
The "current" row should contain a standard "enddate" way off in the future. I usually suggest that people use 9999-01-01. The reason why I don't suggest 9999-12-31 is because that leaves no room for "temporal calculations" nor does it support "Closed/Open" criteria very easily.
As for my previous comment, you said...
The first obvious question is why do you think a current receipt is a totally different kind of data element from a historical receipt? In a properly designed table, the receipt date would determine whether the receipt was current or historical.
You made it sound like you were objecting to having a history table. If you were referring to something else, could you explain please? I'm not seeing the likes of something nuts like the use of an "IsActive" flag column.
--Jeff Moden
February 3, 2022 at 9:34 pm
@jeremyu ,
I'm curious... what is the underlying datatype of the RCLDTE column and what does a good or bad date look like in that column?
Also, are you using the [Saturday] calculation as a "week ending date" for reporting purposes?
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply