Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

T-SQL Using a CTE as a View and "Adding" a Field from Another CTE

Hello:

Attached are four Word documents.  The docs entitled "ITEMAGEPOS" and "ITEMAGENEG" represent CTEs.  The other two docs are screenshots showing samples of results from running either CTE.

I'd like to place "ITEMAGEPOS" in a view.  And, in that view, I'd like to add the [Quantity Remaining] field from "ITEMAGENEG" to the Quantity Remaining field of "ITEMAGEPOS" for every match of [Item Number], [Location Code], and [Date Range] in "ITEMAGEPOS".  

How do I accomplish both of these tasks?

I'm probably going to have to create a view for "ITEMAGENEG", ad well.  But, the only data that I want from "ITEMAGENEG" to be "within" "ITEMAGEPOS" is [Quantity Remaining].

Thanks!  Much appreciated!

TBSupport
ITEMAGEPOS.docx
Results-of-ITEMAGEPOS.docx
ITEMAGENEG.docx
Results-of-ITEMAGENEG.docx
0
TBSupport
Asked:
TBSupport
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I will take a look, but as an introductory note you can nest CTEs; therefore, one solution could be the following.

WITH cte1 AS ( ... ), cte2 AS ( ... )
SELECT ... FROM cte1 JOIN cte2 ON ...

I will open the Word documents, though, and see if my understanding of what you need changes.
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, reviewing your code, the nested CTE option still is a viable solution; however, I notice that the only difference between the two queries is the filter on IV30300.DOCTYPE and IV30300.TRXQTY.  Therefore, you may be able to accomplish this with one query, leveraging conditional aggregates.

In other words, you would exclude WHERE conditions for IV30300.DOCTYPE, or make them inclusive of both queries (e.g., IV30300.DOCTYPE IN (1, 4, 6, 7) and IV30300.TRXQTY <> 0).  Subsequently, you could have multiple columns that use SUM(...) OVER(...).

Instead of: SUM(IV30300.TRXQTY) OVER (...)
Use:
SUM(
  CASE 
    WHEN IV30300.DOCTYPE IN (1, 6) AND IV30300.TRXQTY < 0 
      THEN IV30300.TRXQTY
    ELSE 0
  END
) OVER (...)

Open in new window


In fact, you can use this technique to perform the different aging calculations in one query also, avoiding the UNION (repetition of JOINs).  You would just have one SUM for each date range with a different WHEN condition on each that includes the document type, transaction quantity, and document date filters.

If you wish to stay with the multiple CTEs, the approach is as I stated above.

WITH CTE_NEG 
AS (
    /* query for CTE_NEG here. */
)
, CTE_POS 
AS (
    /* query for CTE_POS here. */
)
SELECT /* columns you want. */
FROM CTE_POS p
LEFT JOIN CTE_NEG n ON /* join fields */ AND n.RowNumber = p.RowNumber
;

Open in new window


I hope that helps!

P.S. For completeness, note the nested CTE approach also allows you do keep your current final SELECTs in their own CTE, then do whatever you want at the end.

WITH CTE_NEG 
AS (
    /* query for CTE_NEG here. */
)
, CTE_POS 
AS (
    /* query for CTE_POS here. */
)
, CTE_POS_SEL
AS (
    SELECT [Date Range], [Item Number], [Item Description], [Doc]
         , CASE 
             WHEN RowNumber = 1 THEN CAST([SUMQTY] AS VARCHAR(20)) 
             ELSE '' END AS [Quantity Remaining]
         , [Location Code], [Document Type], [Document Date], [Qty], [U Of M]
         , [Unit Cost], [Extended Cost], [Use]
    FROM CTE_POS
)
, CTE_NEG_SEL
AS (
    /* SELECT query from CTE_NEG like one above for CTE_POS here. */
)
SELECT /* columns you want. */
FROM CTE_POS_SEL p
LEFT JOIN CTE_NEG_SEL n ON /* join fields */
;

Open in new window

0
 
TBSupportAuthor Commented:
Thank you!  This worked!

TBSupport
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!

Best regards and happy coding,

Kevin
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now