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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy