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_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 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-customername
LVL 1
isamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
There may be better ways of handling this that just using case expressions with correlated subqueries.

Can you post the complete query please?
0
isamesAuthor Commented:
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





GO

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What is the performance of that View?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the difference between Parent_ACCOUNT and SUB_ACCOUNT?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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)

GO

Open in new window

0
isamesAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.