March 22, 2018 at 2:43 pm
I'm running the code in sql server. I'm trying to obtain datagroups A, B, and C at the same time by passing 0 as a parameter for DataNumbers.
Following is the code snippet of my query,
with group1 as
( select
1 DataNumber, 1 Data_Sort
union select 2 DataNumber, 2 Data_Sort
union select 3 Data_Number, 3 Data_Sort
),
group2 as
(select
Case
When grp.DataNumber = 1 Then 'A'
When grp.DataNumber = 2 Then 'B'
When grp.DataNumber = 3 Then 'C'
End datagroup,
grp.DataNumber,
grp.Data_Sort
from group1 grp
where
(cast(grp.Data_Group_Number AS char(1)) in (@Data_Group_Number) or '0' in (@Data_Group_Number))
I applied the same logic in oracle and it works. The code I used in oracle was as follows;
to_char(grp.Data_Group_Number) in :Data_Group_Number or '0' in :Data_Group_Number
Please let me know what I need to do inorder to retrieve all the three data groups at the same time by passing the value 0 as a parameter.
Thanks in advance!
March 30, 2018 at 1:23 pm
Try this: WITH group1 AS (
select 1 DataNumber, 1 Data_Sort
union
select 2 DataNumber, 2 Data_Sort
union
select 3 Data_Number, 3 Data_Sort
),
group2 AS (
SELECT
CASE grp.DataNumber
WHEN 1 THEN 'A'
WHEN 2 Then 'B'
WHEN 3 Then 'C'
END AS datagroup,
grp.DataNumber,
grp.Data_Sort
FROM group1 AS grp
WHERE CAST(grp.Data_Group_Number AS char(1)) = @Data_Group_Number
OR @Data_Group_Number = '0'
)
Oracle and SQL Server are rather different animals. Logic and Oracle don't always go together...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 30, 2018 at 1:43 pm
I modified Steve's code: This will return all 3 rows if you send the value of '0'.DECLARE @Data_Group_Number CHAR(1);
SET @Data_Group_Number = '0';
WITH group1 AS (
select 1 DataNumber, 1 Data_Sort
union
select 2 DataNumber, 2 Data_Sort
union
select 3 DataNumber, 3 Data_Sort
)
--,
-- group2 AS (
SELECT
CASE grp.DataNumber
WHEN 1 THEN 'A'
WHEN 2 Then 'B'
WHEN 3 Then 'C'
END AS datagroup,
grp.DataNumber,
grp.Data_Sort
FROM group1 AS grp
WHERE CAST(grp.DataNumber AS char(1)) = @Data_Group_Number
OR @Data_Group_Number = '0'
--)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 31, 2018 at 8:30 am
below86 - Friday, March 30, 2018 1:43 PMI modified Steve's code: This will return all 3 rows if you send the value of '0'.DECLARE @Data_Group_Number CHAR(1);SET @Data_Group_Number = '0';
WITH group1 AS (
select 1 DataNumber, 1 Data_Sort
union
select 2 DataNumber, 2 Data_Sort
union
select 3 DataNumber, 3 Data_Sort
)
--,
-- group2 AS (SELECT
CASE grp.DataNumber
WHEN 1 THEN 'A'
WHEN 2 Then 'B'
WHEN 3 Then 'C'
END AS datagroup,
grp.DataNumber,
grp.Data_Sort
FROM group1 AS grp
WHERE CAST(grp.DataNumber AS char(1)) = @Data_Group_Number
OR @Data_Group_Number = '0'
--)
Both encapsulation of a column name in a formula and adding an OR is going to lead to some nasty performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2018 at 1:01 pm
Jeff, If you have a better solution please include it. I would really like to understand how it could be done without the OR.DECLARE @Data_Group_Number CHAR(1);
SET @Data_Group_Number = '0';
WITH group1 AS
(
SELECT 1 DataNumber, 1 Data_Sort
UNION
SELECT 2 DataNumber, 2 Data_Sort
UNION
SELECT 3 DataNumber, 3 Data_Sort
)
,
group2 AS
(
SELECT CAST(grp.DataNumber AS CHAR(1)) AS DataNumber,
Data_Sort
FROM group1 AS grp
)
,
Data_Group AS
(
SELECT DataNumber
FROM group2
WHERE DataNumber = @Data_Group_Number
OR @Data_Group_Number = '0'
GROUP BY DataNumber
)
SELECT
CASE grp.DataNumber
WHEN '1' THEN 'A'
WHEN '2' Then 'B'
WHEN '3' Then 'C'
END AS datagroup,
grp.DataNumber,
grp.Data_Sort
FROM group2 AS grp
WHERE grp.DataNumber IN (SELECT DataNumber
FROM Data_Group)
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 2, 2018 at 1:12 pm
below86 - Monday, April 2, 2018 1:01 PMJeff, If you have a better solution please include it. I would really like to understand how it could be done without the OR.DECLARE @Data_Group_Number CHAR(1);SET @Data_Group_Number = '0';
WITH group1 AS
(
SELECT 1 DataNumber, 1 Data_Sort
UNION
SELECT 2 DataNumber, 2 Data_Sort
UNION
SELECT 3 DataNumber, 3 Data_Sort
)
,
group2 AS
(
SELECT CAST(grp.DataNumber AS CHAR(1)) AS DataNumber,
Data_Sort
FROM group1 AS grp
)
,
Data_Group AS
(
SELECT DataNumber
FROM group2
WHERE DataNumber = @Data_Group_Number
OR @Data_Group_Number = '0'
GROUP BY DataNumber
)
SELECT
CASE grp.DataNumber
WHEN '1' THEN 'A'
WHEN '2' Then 'B'
WHEN '3' Then 'C'
END AS datagroup,
grp.DataNumber,
grp.Data_Sort
FROM group2 AS grp
WHERE grp.DataNumber IN (SELECT DataNumber
FROM Data_Group)
;
Here's the link that I consider to be the definitive article on the subject especially since it's saved my hinny on more than one occasion.
https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply