August 5, 2018 at 2:45 am
I have three seperate tables that can be joined to eachother. For examples sake We'll call them three tables MainTableOne, MainTableTwo, MainTableThree.
All together these tables have these columns.
PolicyNumber
BOCBranch
CIFNumber
EmployeeNumber
PremiumSign
RegistrationDate
ActivityCode
ActivityDescription
PolicyTypeCode
PolicyTypeDescription
ContributionCode
ContributionDescription
ActivityMilimetra
SourceCode
Premium
PolicyNumber
BOCBranch
CIFNumber
EmployeeNumber
PremiumSign
RegistrationDate
ActivityCode
ActivityDescription
PolicyTypeCode
PolicyTypeDescription
ContributionCode
ContributionDescription
ActivityMilimetra
SourceCode
Premium
PolicyNumber
BOCBranch
CIFNumber
EmployeeNumber
PremiumSign
RegistrationDate
ActivityCode
ActivityDescription
PolicyTypeCode
PolicyTypeDescription
ContributionCode
ContributionDescription
ActivityMilimetra
SourceCode
Premium
PolicyNumber
BOCBranch
CIFNumber
EmployeeNumber
PremiumSign
RegistrationDate
ActivityCode
ActivityDescription
PolicyTypeCode
PolicyTypeDescription
ContributionCode
ContributionDescription
ActivityMilimetra
SourceCode
Premium
I have one more table called FIELDS_ACTIVATIONS which is attached as a txt file.
You'll notice that the columns of the three tables are the FieldNames in the FIELDS_ACTIVATIONS table.
Right now, I am running a stored procedure which selects all columns from the three tables and shows the results in a .txt file. Due to GDPR I have been tasked with seperating all these columns into 4 categories. Each column will belong to a category, for example PolicyNumber belongs to Category 1 and that shows because it is true for IsActive. PolicyNumber for Category 2 is false for IsActive. This applies to all columns.
So what I need to do is:
Where the column name of the three tables is equal to the FieldName in FIELDS_ACTIVATIONS, check if that record is Active and SELECT it. If it isn't active, select it, but as an empty string.
So for example:
PolicyNumber is True for Category 1, Get me the column along with it's values.
PolicyNumber is False for Category 2. Get me the column, without it's values, as in empty string.
I need to do this for all columns, so as a result, in the .txt file I'll have every column displayed, some empty and some with their values.
As I mentioned earlier to Andy I have this code:
WITH active AS
(
SELECT FieldName
FROM Field_Activations
WHERE CategoryId=1 AND IsActive=1
)SELECT
CASE WHEN EXISTS (SELECT * FROM active WHERE FieldName=COL_NAME FROM FIELDS_ACTIVATIONS...)
THEN REPLICATE('0', 10-LEN(PolicyNumber)) + PolicyNumber
ELSE '' END AS PolicyNumber,
CASE WHEN EXISTS (SELECT * FROM active WHERE FieldName=COL_NAME FROM FIELDS_ACTIVATIONS...)
then REPLICATE('0', 7-LEN(BOCBranch)) + BOCBranch
ELSE '' end AS BOCBranch,
--...
FROM MainTableOne, MainTableTwo, MainTableThree.
ORDER BY PolicyNumber;
That's the general gist of the whole thing. If it's category one and true, print it or return an empty column.
I'm afraid I don't have access to any more code to help because it doesn't belong to me.
Does this make sense?
CODE UPDATE
CategoryOne
WITH CTECategoryOne AS
(
SELECT FieldName
FROM Company.FileExtraction.FIELDS_ACTIVATION
WHERE CategoryId = 1 AND IsActive = 1
)
SELECT
--Category 1
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),1))
THEN REPLICATE('0', 10-LEN(PolicyNumber)) + PolicyNumber
ELSE '' END AS PolicyNumber,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),2))
THEN REPLICATE('0', 7-LEN(BOCBranch)) + BOCBranch
ELSE '' END AS BOCBranch,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),3))
THEN CIFNumber + REPLICATE(' ', 8-LEN(CIFNumber))
ELSE '' END AS CIFNumber,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),4))
THEN REPLICATE('0', 7-LEN(EmployeeNumber)) + EmployeeNumber
ELSE '' END AS EmployeeNumber,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),5))
THEN PremiumSign
ELSE '' END AS PremiumSign,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),6))
THEN REPLACE(REPLICATE('0',16-LEN(CAST(Premium AS VARCHAR))) + CAST(Premium AS VARCHAR),'.','')
ELSE '' END AS Premium,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),7))
AND RegistrationDate IS NOT NULL
THEN REPLACE(CONVERT(VARCHAR(10),RegistrationDate,103),'/','')
ELSE REPLICATE(' ', 8)
END AS RegistrationDate,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),8))
THEN ActivityCode + REPLICATE(' ', 10-LEN(ActivityCode))
ELSE '' END AS ActivityCode,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),9))
THEN ActivityDescription + REPLICATE(' ', 255-LEN(ActivityDescription))
ELSE '' END AS ActivityDescription,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),10))
THEN PolicyTypeCode + REPLICATE(' ', 10-LEN(PolicyTypeCode))
ELSE '' END AS PolicyTypeCode,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),11))
THEN PolicyTypeDescription + REPLICATE(' ', 255-LEN(PolicyTypeDescription))
ELSE '' END AS PolicyTypeDescription,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),12))
THEN ContributionCode + REPLICATE(' ', 10-LEN(ContributionCode))
ELSE '' END AS ContributionCode,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),13))
THEN ContributionDescription + REPLICATE(' ', 255-LEN(ContributionDescription))
ELSE '' END AS ContributionDescription,
CASE WHEN EXISTS (SELECT * FROM CTECategoryOne WHERE FieldName = COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),14))
THEN ActivityMilimetra + REPLICATE(' ', 1-LEN(ActivityMilimetra))
ELSE '' END AS ActivityMilimetra
FROM Company.FileExtraction.EXTR_MILIMETRA
GROUP BY PolicyNumber,
BOCBranch,
CIFNumber,
EmployeeNumber,
PremiumSign,
Premium,
RegistrationDate,
ActivityCode,
ActivityDescription,
PolicyTypeCode,
PolicyTypeDescription,
ContributionCode,
ContributionDescription,
ActivityMilimetra,
SourceCode
ORDER BY PolicyNumber
August 5, 2018 at 4:08 am
Something like:select
Category,
-- Included in category
IIF(Category In (1), '1', '') As Col1,
IIF(Category In (4,2), '2', '') As Col2,
IIF(Category In (2), '3', '') As Col3,
IIF(Category In (1,3), '4', '') As Col4,
-- Flipping parameter order gives NOT IN category
IIF(Category In (4), '', '5') As Col5,
IIF(Category In (1), '', '6') As Col6,
IIF(Category In (1,2,3), '', '7') As Col7,
IIF(Category In (2,4), '', '8') As Col8,
-- Or you could give alternates
IIF(Category In (2,4), 'X', 'Y') As Col8
from (Values (1),(2),(3),(4)) As T(Category)
August 5, 2018 at 4:21 am
Alternatively:
Select Category, col1,col2,col3,col4,'' col5,'' col6,'' col7 From #temp Where Category = 1
Union All Select Category, '' col1,'' col2,'' col3,col4,col5,col6,col7 From #temp Where Category = 2
Union All Select Category, col1,'' col2,col3,'' col4,col5,'' col6,col7 From #temp Where Category = 3
Union All Select Category, col1,col2,'' col3,'ERROR' col4,col5,col6,'' col7 From #temp Where Category = 4
Which works best depends a lot on the specifics of the situation
August 5, 2018 at 4:55 am
Slight edit in the code.
When Category = 1
Then SELECT Col1, SELECT Col2, SELECT Col3, SELECT Col4, SELECT Col5
SELECT Col6 AS EMPTY STRING, SELECT Col7 AS EMPTY STRING,SELECT Col8 AS EMPTY STRING,SELECT Col9 AS EMPTY STRING,SELECT Col10 AS EMPTY STRING
When Category = 2
Then SELECT Col6, SELECT Col7, SELECT Col8, SELECT Col9, SELECT Col10
SELECT Col1 AS EMPTY STRING, SELECT Col2 AS EMPTY STRING,SELECT Col3 AS EMPTY STRING,SELECT Col4 AS EMPTY STRING,SELECT Col11 AS EMPTY STRING
I can't use UNION because the amount of columns in each category differ.
August 5, 2018 at 5:13 am
If they need to return a different number of columns, they need to be completely different SELECT statements. You could do something like this in a stored procedure:
CREATE PROCEDURE DoSomething @Category Int
As
BEGIN
IF @Category = 1
SELECT col1, col2, col3 FROM #Data WHERE Category = @Category
ELSE IF @Category =2
SELECT col3, col4, col5, col6 FROM #Data WHERE Category = @Category
END
But I'd strongly advise against it, as it leads to poor query plans and generally causes issues trying to consume the result set as most automated tools will assume any given query returns a consistent result set structure. You're usually better off at that point creating entirely different stored procedures for the different categories
August 5, 2018 at 6:30 am
“You're usually better off at that point creating entirely different stored procedures for the different categories”
Totally agree Andy. In fact, I hate this approach but it’s been thrown at me by a senior colleague. I have started doing each category seperately to get the bulk work done then I’ll focus on the next bit.
In a nutshell, a sp exists which writes all ten columns to a txt file. Due to gdpr theyve told me to seperate those 10 columns along with another say 20 additional columns, stick all the columns in one category and alter that stored procedure to show all 30 columns in the txt file but depending on its category as the code above represents show the column or show the column with an empty value.
Does this make sense to you? Personally I’m smelling a design failure somewhere but I’m a junior dev and I can’t really talk. It seems tomake perfect sense to the person who gave it to me. I’m stuck on this for a week. I’m not sure what to do here.
I’m writing all four cats to one query file to at least know I can show he columns in each category but bringing all this together without unions and without joins into one set is total bollocks.
This is my actual code so far.
WITH active AS
(
SELECT FieldName
FROM Field_Activations
WHERE CategoryId=1 AND IsActive=1
)
SELECT
CASE WHEN EXISTS (SELECT * FROM active WHERE FieldName='PolicyNumber')
THEN REPLICATE('0', 10-LEN(PolicyNumber)) + PolicyNumber
ELSE '' END AS PolicyNumber,
CASE WHEN EXISTS (SELECT * FROM active WHERE FieldName='BOCBranch')
then REPLICATE('0', 7-LEN(BOCBranch)) + BOCBranch
ELSE '' end AS BOCBranch,
--...
FROM FileExtraction.EXTR_MILIMETRA
ORDER BY PolicyNumber;
Thats category one with two columns. Ive done the rest of the columns snd started on cat two.
Question is, how the hell do I get all this in one data set.
What my senior fails to understand is that getting the columns in ONE file is possible with my code. BUT how do you specify which category is active in the sql so you know which columns to show?? The answer I get is “The FIELD ACTIVATION table specifies this”.
So, Cat 1 has column 1 Active. Cat 2 has column 1 Inactive. How will column one be shown in the file? Theres a FIELD ACTIVATION table hat says column one is active for category one and Inactive for the rest of the categories. Fine but how do I specify which category the file views? The answer I get is the same. “The field activations table says it”.
Sorry guys but does this make any sense at all to you?
If I can’t get through this by mid week I’ll have to go to my manager. I just don’t want to show that I’m incapable 4 months into the job but delaying it too much will probably cause more harm than good.
August 5, 2018 at 6:59 am
Drop Table If Exists #Data
Create Table #Data
(
prod varchar(20),
category int,
col1 varchar(20),
col2 varchar(20),
col3 varchar(20),
col4 varchar(20)
)
Insert Into #Data ValuesAugust 5, 2018 at 9:06 am
I’ve lost you Andy. I don’t what the code is doing I’ll have to look into it. Eg I don’t know what pivot is so I’ll have to look that up.
Just a note. All my columns come from 3 tables (say 30 cols), which can join between themselves. What can’t be joined is the FIELD ACTIVATION table that specifies which column name is Active/Inactive.
So basically I would be saying If category 1, show me all 30 columns but the category one columns with values the rest empty values. And so on.
If you want ill post some sample code later.
August 5, 2018 at 9:25 am
The key bit is how PIVOT works. So this code like this:
Select
Category,
Col1,
Col2,
Col3,
Col4
From #FieldLookup
Pivot
(
Max(Active) For FieldName In
(
[col1],
[col2],
[col3],
[col4])
) PVT
Takes a table like:
Category FieldName Active
1 col1 1
1 col2 1
2 col3 1
2 col4 0
And "pivots" some of the rows into columns instead, so we end up with a derived table like:
Category Col1 Col2 Col3 Col4
1 1 1 NULL NULL
2 NULL NULL 1 0
The IsNulls are turning that into a set of 1 or 0 for each column, showing whether it should appear for that category or not. Now we can join our original data onto this derived table and use IIF to decide on a row-by-row basis whether or not we return the actual result of a column or just some empty placeholder - basically "IF the corresponding column in our derived table = 1 return the column value, otherwise return a placeholder". It's worth playing around with PIVOT to get your head around what it's doing, as it makes solving this kind of problem a lot easier.
Note, I'm assuming here that Category is some property of each row - but if it's just a parameter to the report instead it shouldn't require much effort to change (basically just the join conditions).
August 5, 2018 at 12:17 pm
Andy check the original post again please I've redone the question to help more and added some code.
Have a look when you can and let me know if your answers still apply and I'll give it a bash at work tomorrow.
Thank a lot for your time.
August 5, 2018 at 12:29 pm
Yup, will totally will work with what you have. Have a look into my example and I'll give you a more tailored example when I'm not on my phone in a pub! 😉
August 5, 2018 at 12:51 pm
LOL.
I’ll go into work tomorrow, have a read through your answer and try to implement it. I’ll post back and let you know.
I still haven’t understood how it’s all going to work without specifying a category but I am a beginner so I won’t rant on about it.
Do you get what I’m not getting? PolicyNumber is in Cat 1 and it’s active for that category. Lovely. For category two it isn’t active but it can be for three,later it could be active for all categories! how will PolicyNumber show up in the file then, as an empty column or with it’s values?
I’ll end up in a pub too in a minute if my brain can’t get round this 🙂
August 5, 2018 at 12:57 pm
I recommend a pub at this point, but don't worry, it's a solvable problem.
August 5, 2018 at 11:29 pm
I think the only detail unclear here is quite how the category ID relates to the data in your three tables. Is that something specified in a column in one of the tables, something inherent about which table the data is in (i.e. MainTable1 is Category1, MainTable2 is Category 2) or is it just some property of the report passed in as a parameter and applied to all data? Each of those leads to a slightly different variant of basically the same solution.
August 5, 2018 at 11:29 pm
Morning Andy. I've posted a code update. It's category one. I've got exactly the same for CategoryTwo and CategoryThree each with their own columns.
Just need to get it all into one as we said.
Any chance of taking a look?
Thanks
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply