isames
asked on
SQL Query
I have created a worksheet in our BI software that is pulling all of it's information from a View in SQL. The View does a Union All to combine current and history info from different tables. Everything works fine, for the most part.
In the BI software, calculations run against different account numbers. Some of the account numbers are Parent Account numbers, and some are Child account numbers.
The worksheet is grouped by Region, Parent Account Number, Customer Name.
When you run the worksheet and drill down to the Parent Account number, the customer name may be the one for the Parent Account number, or it may be for the Child Account number that is attached to the Parent. It's behaving correctly, because if activity was done in the child account it should show the name of the Child account.
However, I would like when the Parent Account number is shown, for it to also show the name of the Parent account, whether or not activity was done.
The code as it is now:
CASE
WHEN LEN(ARCF.ARCF_PARENT_CUST_
THEN (CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT LTRIM(RTRIM(B.ARCF_ALT_CUS
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST
END)
END AS Parent_ACCOUNT,
The column in the ARCF table that has the name that corresponds to the account number is called arcf_name.
So if I ran this query, I would like it show:
parentaccountnumber-custom
In the BI software, calculations run against different account numbers. Some of the account numbers are Parent Account numbers, and some are Child account numbers.
The worksheet is grouped by Region, Parent Account Number, Customer Name.
When you run the worksheet and drill down to the Parent Account number, the customer name may be the one for the Parent Account number, or it may be for the Child Account number that is attached to the Parent. It's behaving correctly, because if activity was done in the child account it should show the name of the Child account.
However, I would like when the Parent Account number is shown, for it to also show the name of the Parent account, whether or not activity was done.
The code as it is now:
CASE
WHEN LEN(ARCF.ARCF_PARENT_CUST_
THEN (CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT LTRIM(RTRIM(B.ARCF_ALT_CUS
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST
END)
END AS Parent_ACCOUNT,
The column in the ARCF table that has the name that corresponds to the account number is called arcf_name.
So if I ran this query, I would like it show:
parentaccountnumber-custom
ASKER
Below is query I used to create the View that the BI software is pulling from. I just select the field I want in the BI worksheet from fields created in the View.
USE [tsiprod]
GO
/****** Object: View [tsi].[SalesImportHistory] Script Date: 04/23/2015 20:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [tsi].[SalesImportHistory] as
SELECT
LTRIM(RTRIM(RSID.RSIH_INVOICE_N)) as INVOICE_N,
RSID.RSID_SEQ_N,
RSIH.RSIH_DATE,
CASE WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N)) ELSE LTRIM(Rtrim(ARCF.ARCF_CUST_N)) END AS CUST_NUM,
CASE
WHEN LEN(ARCF.ARCF_PARENT_CUST_N ) > 1
THEN (CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT LTRIM(RTRIM(B.ARCF_ALT_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N))
END)
END AS SUB_ACCOUNT,
RSIH.RSIH_BILL_NAME,
(SELECT Distinct GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = RSID.RSID_DIV) AS STORE_NUM,
LTRIM(RTRIM(RSID.RSID_PROD_N)) AS PROD_NUM,
RSID.RSID_DESC,
RSID.RSID_TYPE,
RSID.RSID_QTY,
(RSID.RSID_PROD_COST*RSID.RSID_QTY) AS PRODUCTCOST,
RSID.RSID_EXTENDED,
RSID.RSID_DISC_AMT,
ARCF.ARCF_C_RATING,
ARCF.ARCF_PHONE,
ARCF.ARCF_OPENED,
ARCF.ARCF_ADDR1,
ARCF.ARCF_ADDR2,
ARCF.ARCF_CITY,
ARCF.ARCF_PROVINCE,
ARCF.ARCF_POSTAL,
RSIH.RSIH_DISC_PROG_CODE,
ARCF.ARCF_SALESMAN,
CASE WHEN (ARCF.ARCF_CUST_N IN (SELECT ARIC.ARCF_CUST_N FROM ARIC)) THEN (SELECT ARIC.ARCF_SIC_CODE FROM ARIC WHERE ARIC.ARCF_CUST_N = ARCF.ARCF_CUST_N AND ARIC.ARCF_SIC_CODE LIKE 'V%')
ELSE '' END AS VAN_CODE,
'' AS FBH_CODE,
RSPF.RSPF_VENDOR_N,
(SELECT APVF.APVF_NAME FROM APVF WHERE APVF_VENDOR_N = RSPF.RSPF_VENDOR_N) AS VENDOR_NAME,
'' AS FR_REVENUE_TYPE,
'' as ACCT_GRPING_NUM,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNBR,
LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNBR,
RSIH.RSIH_ON_ACCT AS ON_ACCOUNT,
RSIH.RSIH_DOCUMENT_CREATE_DATE,
RSIH.RSIH_TAX_AMT
FROM RSID, RSIH, RSPF, ARCF
WHERE RSIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)
AND RSIH.RSIH_INVOICE_N = RSID.RSIH_INVOICE_N
AND ARCF.ARCF_CUST_N = RSIH.RSIH_CUST_N
AND RSPF.RSPF_PROD_N = RSID.RSID_PROD_N
Union all
SELECT
LTRIM(RTRIM(IHID.RSIH_INVOICE_N)) as INVOICE_N,
IHID.RSID_SEQ_N,
IHIH.RSIH_DATE,
CASE WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N)) ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N)) END AS CUST_NUM,
CASE
WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1
THEN (CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT LTRIM(RTRIM(B.ARCF_ALT_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N))
END)
END AS SUB_ACCOUNT,
IHIH.RSIH_BILL_NAME,
(SELECT DISTINCT GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = IHID.RSID_DIV) AS STORE_NUM,
LTRIM(RTRIM(IHID.RSID_PROD_N)) AS PROD_NUM,
IHID.RSID_DESC,
IHID.RSID_TYPE,
IHID.RSID_QTY,
(IHID.RSID_PROD_COST*IHID.RSID_QTY) AS PRODUCTCOST,
IHID.RSID_EXTENDED,
IHID.RSID_DISC_AMT,
ARCF.ARCF_C_RATING,
ARCF.ARCF_PHONE,
ARCF.ARCF_OPENED,
ARCF.ARCF_ADDR1,
ARCF.ARCF_ADDR2,
ARCF.ARCF_CITY,
ARCF.ARCF_PROVINCE,
ARCF.ARCF_POSTAL,
IHIH.RSIH_DISC_PROG_CODE,
ARCF.ARCF_SALESMAN,
CASE WHEN (ARCF.ARCF_CUST_N IN (SELECT ARIC.ARCF_CUST_N FROM ARIC)) THEN (SELECT ARIC.ARCF_SIC_CODE FROM ARIC WHERE ARIC.ARCF_CUST_N = ARCF.ARCF_CUST_N AND ARIC.ARCF_SIC_CODE LIKE 'V%')
ELSE '' END AS VAN_CODE,
'' AS FBH_CODE,
RSPF.RSPF_VENDOR_N,
(SELECT APVF.APVF_NAME FROM APVF WHERE APVF_VENDOR_N = RSPF.RSPF_VENDOR_N) AS VENDOR_NAME,
'' AS FR_REVENUE_TYPE,
'' as ACCT_GRPING_NUM,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNBR,
LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNBR,
IHIH.RSIH_ON_ACCT AS ON_ACCOUNT,
IHIH.RSIH_DOCUMENT_CREATE_DATE,
IHIH.RSIH_TAX_AMT
FROM IHID, IHIH, RSPF, ARCF
WHERE IHIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)
AND IHIH.RSIH_INVOICE_N = IHID.RSIH_INVOICE_N
AND ARCF.ARCF_CUST_N = IHIH.RSIH_CUST_N
AND RSPF.RSPF_PROD_N = IHID.RSID_PROD_N
GOASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What's the difference between Parent_ACCOUNT and SUB_ACCOUNT?
While I'm waiting for your answers I worked on your query. Just made few changes like using INNER JOIN clauses instead of joining in WHERE clause and also got rid of the sub select for APVF since it could be added to INNER JOIN clause:
ALTER View [tsi].[SalesImportHistory] as
SELECT
LTRIM(RTRIM(RSID.RSIH_INVOICE_N)) as INVOICE_N, RSID.RSID_SEQ_N, RSIH.RSIH_DATE,
CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(Rtrim(ARCF.ARCF_CUST_N))
END AS CUST_NUM,
CASE
WHEN LEN(ARCF.ARCF_PARENT_CUST_N ) > 1 THEN
(CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN
(SELECT LTRIM(RTRIM(B.ARCF_ALT_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N))
END)
END AS SUB_ACCOUNT,
RSIH.RSIH_BILL_NAME,
(SELECT Distinct GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = RSID.RSID_DIV) AS STORE_NUM,
LTRIM(RTRIM(RSID.RSID_PROD_N)) AS PROD_NUM,
RSID.RSID_DESC, RSID.RSID_TYPE,RSID.RSID_QTY,
(RSID.RSID_PROD_COST*RSID.RSID_QTY) AS PRODUCTCOST,
RSID.RSID_EXTENDED,RSID.RSID_DISC_AMT,ARCF.ARCF_C_RATING,
ARCF.ARCF_PHONE,ARCF.ARCF_OPENED,ARCF.ARCF_ADDR1,
ARCF.ARCF_ADDR2,ARCF.ARCF_CITY,ARCF.ARCF_PROVINCE,
ARCF.ARCF_POSTAL,RSIH.RSIH_DISC_PROG_CODE,ARCF.ARCF_SALESMAN,
CASE
WHEN (ARCF.ARCF_CUST_N IN (SELECT ARIC.ARCF_CUST_N FROM ARIC)) THEN
(SELECT ARIC.ARCF_SIC_CODE FROM ARIC WHERE ARIC.ARCF_CUST_N = ARCF.ARCF_CUST_N AND ARIC.ARCF_SIC_CODE LIKE 'V%')
ELSE ''
END AS VAN_CODE,
'' AS FBH_CODE,RSPF.RSPF_VENDOR_N,APVF.APVF_NAME AS VENDOR_NAME,
'' AS FR_REVENUE_TYPE,'' as ACCT_GRPING_NUM,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNBR, LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNBR,
RSIH.RSIH_ON_ACCT AS ON_ACCOUNT, RSIH.RSIH_DOCUMENT_CREATE_DATE, RSIH.RSIH_TAX_AMT
FROM RSID
INNER JOIN RSIH ON RSIH.RSIH_INVOICE_N = RSID.RSIH_INVOICE_N
INNER JOIN RSPF
INNER JOIN APVF ON APVF_VENDOR_N = RSPF.RSPF_VENDOR_N
ON RSPF.RSPF_PROD_N = RSID.RSID_PROD_N
INNER JOIN ARCF ON ARCF.ARCF_CUST_N = RSIH.RSIH_CUST_N
WHERE RSIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)
UNION ALL
SELECT
LTRIM(RTRIM(IHID.RSIH_INVOICE_N)) as INVOICE_N, IHID.RSID_SEQ_N, IHIH.RSIH_DATE,
CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N))
END AS CUST_NUM,
CASE
WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1 THEN
(CASE
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN
(SELECT LTRIM(RTRIM(B.ARCF_ALT_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)
ELSE (SELECT LTRIM(RTRIM(B.ARCF_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)
END)
ELSE (CASE
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
ELSE LTRIM(RTRIM(ARCF.ARCF_CUST_N))
END)
END AS SUB_ACCOUNT,
IHIH.RSIH_BILL_NAME,
(SELECT DISTINCT GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = IHID.RSID_DIV) AS STORE_NUM,
LTRIM(RTRIM(IHID.RSID_PROD_N)) AS PROD_NUM,
IHID.RSID_DESC, IHID.RSID_TYPE, IHID.RSID_QTY,
(IHID.RSID_PROD_COST*IHID.RSID_QTY) AS PRODUCTCOST,
IHID.RSID_EXTENDED,IHID.RSID_DISC_AMT,ARCF.ARCF_C_RATING,
ARCF.ARCF_PHONE,ARCF.ARCF_OPENED,ARCF.ARCF_ADDR1,
ARCF.ARCF_ADDR2,ARCF.ARCF_CITY,ARCF.ARCF_PROVINCE,
ARCF.ARCF_POSTAL,IHIH.RSIH_DISC_PROG_CODE, ARCF.ARCF_SALESMAN,
CASE
WHEN (ARCF.ARCF_CUST_N IN (SELECT ARIC.ARCF_CUST_N FROM ARIC)) THEN
(SELECT ARIC.ARCF_SIC_CODE FROM ARIC WHERE ARIC.ARCF_CUST_N = ARCF.ARCF_CUST_N AND ARIC.ARCF_SIC_CODE LIKE 'V%')
ELSE ''
END AS VAN_CODE,
'' AS FBH_CODE, RSPF.RSPF_VENDOR_N, APVF.APVF_NAME AS VENDOR_NAME,
'' AS FR_REVENUE_TYPE, '' as ACCT_GRPING_NUM,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNBR,
LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNBR,
IHIH.RSIH_ON_ACCT AS ON_ACCOUNT,IHIH.RSIH_DOCUMENT_CREATE_DATE,IHIH.RSIH_TAX_AMT
FROM IHID
INNER JOIN IHIH ON IHIH.RSIH_INVOICE_N = IHID.RSIH_INVOICE_N
INNER JOIN RSPF
INNER JOIN APVF ON APVF_VENDOR_N = RSPF.RSPF_VENDOR_N
ON RSPF.RSPF_PROD_N = IHID.RSID_PROD_N
INNER JOIN ARCF ON ARCF.ARCF_CUST_N = IHIH.RSIH_CUST_N
WHERE IHIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)
GOASKER
I takes about a minute to get results from the query, depending on how I'm grouping.
The parent account is the main account or customer. This is usually the company name. The sub is the child account that is attached to the parent. this is usually a worker for the company that is authorized to make purchases
The parent account is the main account or customer. This is usually the company name. The sub is the child account that is attached to the parent. this is usually a worker for the company that is authorized to make purchases
The suggestions by Vitor are good and you should adopt explicit join syntax instead of using the where clause.
My greatest concern with your query is that there are several points of potential failure, for example:
(SELECT DISTINCT GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = IHID.RSID_DIV) AS STORE_NUM
What guarantees this subquery will only produce a single value?
It could produce 2 or more rows and if that happens the view will stop working.
My suggestions are:
> use joins instead of correlated subqueries if possible, and if a join is not feasible, use OUTER APPLY instead
> if you continue with correlated subqueries within a select clause, make certain they can never return multiple rows
> if performance is poor, examine the Execution Plan. Do you require indexes?
> reconsider the need for LTRIM(RTRIM()) is this truly necessary?
-------------
>>"takes about a minute to get results from the query, depending on how I'm grouping."
what grouping? there isn't any in the query provided
My greatest concern with your query is that there are several points of potential failure, for example:
(SELECT DISTINCT GLDV_ALT_DIV_N FROM GLDV WHERE GLDV.GLDV_DIV = IHID.RSID_DIV) AS STORE_NUM
What guarantees this subquery will only produce a single value?
It could produce 2 or more rows and if that happens the view will stop working.
My suggestions are:
> use joins instead of correlated subqueries if possible, and if a join is not feasible, use OUTER APPLY instead
> if you continue with correlated subqueries within a select clause, make certain they can never return multiple rows
> if performance is poor, examine the Execution Plan. Do you require indexes?
> reconsider the need for LTRIM(RTRIM()) is this truly necessary?
-------------
>>"takes about a minute to get results from the query, depending on how I'm grouping."
what grouping? there isn't any in the query provided
Can you post the complete query please?