November 20, 2018 at 4:14 pm
I'm trying to add a new column to some code I have, and I am completely stumped.
I was wondering if anyone could assist or point me in the right direction.
Here is what I have:
SELECT CASE
WHEN (
cus_nm LIKE '%(%'
AND cus_lkp_nm NOT LIKE '%REG%'
)
THEN 'N'
ELSE 'Y'
END AS Main
,*
FROM arrcus_rec
WHERE cus_dun_no IN (
SELECT cus_dun_no
FROM arrcus_rec
WHERE cus_usg_sts = 'A'
GROUP BY cus_dun_no
HAVING COUNT(cus_dun_no) > 1
)
Basically what I want to do is create a new column called Job_Account that is blank if I returned Y in the CASE statement, or if I returned N, displays the cus_num for the record that would have returned Y.
These records will have different customer numbers, but share a cus_dun_no.
So basically if Main is N, show the main customer number (record that would have returned Y).
Results would looke like:
| Main | Job Account | cus_cus_no | cus_lkp_nm | cus_nm | |
| Y | 6625 | ABC, INC. | ABC, INC. | ||
| N | 6625 | 6694 | ABC (123) | ABC, INC.(123) |
Sorry if that doesn't make perfect sense. It's hard for me to spell out.
Thanks in advance, and let me know if I can clarify.
November 20, 2018 at 7:31 pm
SELECT T.Main,
IIF(T.Main='N',cus_nm,'') AS JobAccount
,*
FROM arrcus_rec
CROSS APPLY(SELECT CASE
WHEN (
cus_nm LIKE '%(%'
AND cus_lkp_nm NOT LIKE '%REG%'
)
THEN 'N'
ELSE 'Y'
END) T(Main)
WHERE cus_dun_no IN (
SELECT cus_dun_no
FROM arrcus_rec
WHERE cus_usg_sts = 'A'
GROUP BY cus_dun_no
HAVING COUNT(cus_dun_no) > 1
)
November 20, 2018 at 8:29 pm
There's no real reason to keep beeting up that poor arrcus_rec table... You can actually do everything with a single index scan.
The following "looks" different but is logically equivilant and should be much more efficient... Especially if the covering index is in place to get rid of the sort operation.
----------------------------
-- create some test data...
IF OBJECT_ID('tempdb..#arrcus_rec', 'U') IS NOT NULL
BEGIN DROP TABLE #arrcus_rec; END;
CREATE TABLE #arrcus_rec (
cus_cus_no INT NOT NULL PRIMARY KEY,
cus_dun_no INT NOT NULL,
cus_lkp_nm VARCHAR(20) NOT NULL,
cus_nm VARCHAR(20) NOT NULL,
cus_usg_sts CHAR(1) NOT NULL
);
INSERT #arrcus_rec(cus_cus_no, cus_dun_no, cus_lkp_nm, cus_nm, cus_usg_sts) VALUES
(6625, 5555, 'ABC, INC.', 'ABC, INC.', 'A'),
(6694, 5555, 'ABC (123)', 'ABC, INC.(123)', 'A'),
(6699, 5555, 'ABC (123)', 'ABC, INC.(123)', 'B'),
(6794, 5555, 'ABC (123)', 'ABC, INC.(123)', 'B'),
(6894, 6666, 'XYZ, 123', 'XYZ, INC.', 'A'),
(6994, 6666, 'XYZ (123)', 'ABC, INC.(123)', 'B'),
(7694, 6666, 'XYZ (123)', 'ABC, INC.(123)', 'C');
----------------------------
-- add a covering index...
CREATE UNIQUE NONCLUSTERED INDEX ix_arrcusrec_cusdunno
ON #arrcus_rec (cus_dun_no, cus_cus_no)
INCLUDE (cus_lkp_nm, cus_nm, cus_usg_sts);
--===============================================================
----------------------------
-- the actual solution...
WITH
cte_add_calc AS (
SELECT
m. Main,
job_account = NULLIF(MAX(CASE WHEN m.main = 'Y' THEN ar.cus_cus_no END) OVER (PARTITION BY ar.cus_dun_no), ar.cus_cus_no),
ar.cus_cus_no,
ar.cus_lkp_nm,
ar.cus_nm,
cnt = COUNT(CASE WHEN ar.cus_usg_sts = 'A' THEN 1 END) OVER (PARTITION BY ar.cus_dun_no)
FROM
#arrcus_rec ar
CROSS APPLY ( VALUES (CASE WHEN ar.cus_nm LIKE '%(%' AND ar.cus_lkp_nm NOT LIKE '%REG%' THEN 'N' ELSE 'Y' END) ) m (main)
)
SELECT
ac.main,
ac.job_account,
ac.cus_cus_no,
ac.cus_lkp_nm,
ac.cus_nm
FROM
cte_add_calc ac
WHERE
ac.cnt > 1;
Results:main job_account cus_cus_no cus_lkp_nm cus_nm
---- ----------- ----------- -------------------- --------------------
Y NULL 6625 ABC, INC. ABC, INC.
N 6625 6694 ABC (123) ABC, INC.(123)
N 6625 6699 ABC (123) ABC, INC.(123)
N 6625 6794 ABC (123) ABC, INC.(123)
November 21, 2018 at 6:57 am
Thank you guys! I really really appreciate it.
This one had my eyes crossing.
I'm going to try this morning.
Thanks!!
November 27, 2018 at 8:30 am
Thanks again.
Unfortunately, I am running this against an Informix database, and didn't realize the typical T-SQL doesn't work.
However, for what I asked, the answers were perfect, and much appreciated.
Just my ignorance to blame.
Hopefully I can figure out a way to get it to work here.
Thank you!
November 27, 2018 at 9:37 am
caldrumr1234 - Tuesday, November 27, 2018 8:30 AMThanks again.
Unfortunately, I am running this against an Informix database, and didn't realize the typical T-SQL doesn't work.
However, for what I asked, the answers were perfect, and much appreciated.
Just my ignorance to blame.
Hopefully I can figure out a way to get it to work here.
Thank you!
Unfortunatly, I don't know anything about Informix or it's specific flavor of SQL. This forum probably isn't going to be much help either. There may be a small handful of people around here that work in both but I wouldn't hold my breath waiting for one to come around and provide an answer.
It feels a little sacrilegious saying this, but, I'd suggest taking this over to StackOverflow from here. I imagine that will give you the best shot at finding someone who is preficient with both platforms. Just tag your question with both SQL Server and Informix labels, explain the problem and explan that you were able to get a working SQL Server version but you need help translating it to Informix.
Feel free to repost my code there or link back to this thread so that the next person build off what you have so far.
November 28, 2018 at 7:35 am
From what I remember Informix isn't very good at telling you what part of the code it is having issues with, it could be the CTE or the CROSS Apply. It's been a while since I had to work with Informix.
This removes the CTE:SELECT
ac.main,
ac.job_account,
ac.cus_cus_no,
ac.cus_lkp_nm,
ac.cus_nm
FROM
(
SELECT
m. Main,
job_account = NULLIF(MAX(CASE WHEN m.main = 'Y' THEN ar.cus_cus_no END) OVER (PARTITION BY ar.cus_dun_no), ar.cus_cus_no),
ar.cus_cus_no,
ar.cus_lkp_nm,
ar.cus_nm,
cnt = COUNT(CASE WHEN ar.cus_usg_sts = 'A' THEN 1 END) OVER (PARTITION BY ar.cus_dun_no)
FROM
#arrcus_rec ar
CROSS APPLY ( VALUES (CASE WHEN ar.cus_nm LIKE '%(%' AND ar.cus_lkp_nm NOT LIKE '%REG%' THEN 'N' ELSE 'Y' END) ) m (main)
) ac
WHERE
ac.cnt > 1;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply