September 12, 2018 at 2:57 pm
I am a beginner to SQL querying but am learning alot. I am taking over for another IT Manager and have this query I am trying to create. But not working. I am having trouble understanding how to use the WITH statement to make a column, Here is the Query I have so far:
USE [WYN]
GO
/****** Object: StoredProcedure [dbo].[wynne_insurance_census_report] Script Date: 9/12/2018 10:20:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[wynne_insurance_census_report] @DATEIN VARCHAR(8)
AS
WITH icr_emp (emp, nam, ssc, gen, brt, ben,hd)
as
(SELECT a.EMPLOYID, rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME), SOCSCNUM, GENDER, BRTHDATE, b.BENEFIT, a.BENADJDATE
from UPR00100 a left join UPR00600 b on a.EMPLOYID = b.EMPLOYID
where a.INACTIVE = '0' and b.INACTIVE = '0'
)
select nam, ssc,
case
when gen = '1' then 'Male'
else 'Female'
end,
brt,
ben,
hd,
from icr_emp
order by EMPLOYID
September 12, 2018 at 2:59 pm
Remove the comma from the last column name before the FROM:
...
hd--, --<--remove this comma
...
from icr_emp
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 12, 2018 at 10:33 pm
Also note that your left join is effectively converted into an inner join by your where clause
September 12, 2018 at 11:51 pm
thunter 5669 - Wednesday, September 12, 2018 2:57 PMI am a beginner to SQL querying but am learning alot. I am taking over for another IT Manager and have this query I am trying to create. But not working. I am having trouble understanding how to use the WITH statement to make a column, Here is the Query I have so far:
USE [WYN]
GO
/****** Object: StoredProcedure [dbo].[wynne_insurance_census_report] Script Date: 9/12/2018 10:20:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[wynne_insurance_census_report] @DATEIN VARCHAR(8)
AS
WITH icr_emp (emp, nam, ssc, gen, brt, ben,hd)
as
(SELECT a.EMPLOYID, rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME), SOCSCNUM, GENDER, BRTHDATE, b.BENEFIT, a.BENADJDATE
from UPR00100 a left join UPR00600 b on a.EMPLOYID = b.EMPLOYID
where a.INACTIVE = '0' and b.INACTIVE = '0'
)
select nam, ssc,
case
when gen = '1' then 'Male'
else 'Female'
end,
brt,
ben,
hd,from icr_emp
order by EMPLOYID
This should work
😎
CREATE PROCEDURE [dbo].[wynne_insurance_census_report]
(
@DATEIN VARCHAR(8)
)
AS
WITH icr_emp (emp, nam, ssc, gen, brt, ben,hd) AS
(
SELECT
a.EMPLOYID
,rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME)
,SOCSCNUM
,GENDER
,BRTHDATE
,b.BENEFIT
,a.BENADJDATE
from UPR00100 a
left join UPR00600 b
on a.EMPLOYID = b.EMPLOYID
AND b.INACTIVE = '0'
where a.INACTIVE = '0'
)
select
nam
,ssc
,case
when gen = '1' then 'Male'
else 'Female'
end
,brt
,ben
,hd
from icr_emp
order by EMPLOYID ASC;
Two suggestions, always use schema qualified object names and adopt a readable and consistent coding style.
September 13, 2018 at 7:46 am
Thank you for your help I was able to get the query to run successfully. I want to add another table to the query, is there a way to JOIN 3 tables? My 3 tables are: UPR00100, UPR00600, an UPR00102. Here is what I am trying:
USE [WYN]
GO
/****** Object: StoredProcedure [dbo].[wynne_insurance_census_report] Script Date: 9/13/2018 8:35:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[wynne_insurance_census_report] @DATEIN VARCHAR(8)
AS
WITH icr_emp (emp, nam, brt, gen, ssc, zip, hd, ben)
as
(SELECT a.EMPLOYID, rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME), BRTHDATE, GENDER, SOCSCNUM, b.BENEFIT, a.BENADJDATE
from UPR00100 a left join UPR00600 b left join UPR00102 c on a.EMPLOYID = b.EMPLOYID = c.EMPLOYID
where a.INACTIVE = '0'
select nam, brt,
case
when gen = '1' then 'Male'
else 'Female'
end,
ssc,
zip,
hd,
ben
from icr_emp
order by 1
September 13, 2018 at 7:55 am
thunter 5669 - Thursday, September 13, 2018 7:46 AMThank you for your help I was able to get the query to run successfully. I want to add another table to the query, is there a way to JOIN 3 tables? My 3 tables are: UPR00100, UPR00600, an UPR00102. Here is what I am trying:USE [WYN]
GO
/****** Object: StoredProcedure [dbo].[wynne_insurance_census_report] Script Date: 9/13/2018 8:35:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[wynne_insurance_census_report] @DATEIN VARCHAR(8)
AS
WITH icr_emp (emp, nam, brt, gen, ssc, zip, hd, ben)
as
(SELECT a.EMPLOYID, rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME), BRTHDATE, GENDER, SOCSCNUM, b.BENEFIT, a.BENADJDATE
from UPR00100 a left join UPR00600 b left join UPR00102 c on a.EMPLOYID = b.EMPLOYID = c.EMPLOYID
where a.INACTIVE = '0'select nam, brt,
case
when gen = '1' then 'Male'
else 'Female'
end,
ssc,
zip,
hd,
benfrom icr_emp
order by 1
Each join needs it's own ON clause (unless it's a CROSS JOIN).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 13, 2018 at 8:01 am
Hi Drew,
Thank you for your response. Can you give me an example of the ON clause and also CROSS JOIN. Thanks!
September 13, 2018 at 8:35 am
thunter 5669 - Thursday, September 13, 2018 8:01 AMHi Drew,Thank you for your response. Can you give me an example of the ON clause and also CROSS JOIN. Thanks!
I find it useful to put a few new lines in code to see what its doingSELECT a.EMPLOYID, rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME), BRTHDATE, GENDER, SOCSCNUM, b.BENEFIT, a.BENADJDATE
from UPR00100 a
left join UPR00600 b
on b.EMPLOYID = a.EMPLOYID
left join UPR00102 c
on c.EMPLOYID = a.EMPLOYID
where a.INACTIVE = '0'
CROSS JOIN is just a cartesian join the same as comma separating the tables in the from clause.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply