SQLTeam.com | Weblogs | Forums

Where to insert a CAST function

To begin with, I have a query that works in SSMS but not in DNN. The issue is that my query uses an implicit conversion from char to int and back to char which the DNN module can't handle. I am needing to reformulate it a bit. Needing to add some code to the c3 and c4 segments that cast/convert the periodyear field to int and then back to char. Further below is the code that I have constructed in hopes of doing this but when I place it in C3, I get an error about " [Incorrect syntax near 'CAST', expected 'AS'] ". I am probably incorrect but assuming that the last piece of code would go in C3 after the second case when statement and need where in front of it.

So, my first question is where (if at all) in the C3 section do I put this cast function (or some other convert statement). Am I wrong in thinking that it would need to go in both c3 and c4?

WITH c1 AS
(SELECT e.stfips, e.areatype, e.area, e. periodyear, e.period, e.seriescode, 
e.empces
FROM ces as e
WHERE e.periodtype='03'
And e.supprecord='0'
and e.periodyear=

(Select Max(periodyear)
From ces)
and e.period=
(Select Top(1)period
From ces
Order by periodyear desc, period desc)
and e.stfips='32'
and e.adjusted='1'
and e.areatype='01'
),
C2 AS
(Select Distinct c1.periodyear, c1.period
From c1
),
C3 As
(Select
(Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As
'month',
(Case When c2.period='01' Then (c2.periodyear -1) Else (c2.periodyear) END) As
'year'
From C2
),
C4 AS
(Select c.stfips, c.areatype, c.area, c.periodyear, c.period, c.seriescode, c.empces
From ces as c, c2
Where c.period = c2.period
And c.periodyear = (c2.periodyear-1)
And c.supprecord='0'
and c.stfips='32'
and c.adjusted='1'
and c.areatype='01'
),

And c.periodyear = ( cast ( cast(c2.periodyear as int) -1 ) as char(10) )

Need the DDL for table "ces". We have no idea what the data types of each column are.

@ScottPletcher
Period char(2)
periodyear char(4)
periodtype char(2)

do you need all of them?

I forgot to include it but I have discovered that it is the c3 section that makes it not work

The syntax of C3 is all messed up too, that might be causing the problem more than the conversions.

I don't see how the WHERE condition in C3 could ever be true: how could a year be equal to the same year minus 1? I assume it's supposed to like the WHERE in C4.

Here's my best guess at roughly what you need, getting rid of any implicit conversions:

C3 As
(Select Case When c2.period='01' Then '12' Else right('0' + 
    cast((cast(c2.period as tinyint)-1) as varchar(2)), 2) end As 'month',
 Case When c2.period='01' Then Cast(c2.periodyear -1 as char(4)) 
     Else (c2.periodyear) END As 'year'
 From C2
 where c2.periodyear = cast(cast(c.periodyear as smallint)+1 as char(4)) 
 ),
1 Like

@ScottPletcher

Msg 4104, Level 16, State 1, Line 27
The multi-part identifier "c.periodyear" could not be bound.

This is referring to the 4th line in the C3 section.

I copied in the wrong code. Not that different but still the above code is what works in SSMS but not DNN

please provide both DDL and DML. and enough sample data to work out the final result you want to see.

IF OBJECT_ID('ces') IS NOT NULL 
    DROP TABLE ces

create table dbo.ces(empces int, supprecord char(1), stfips int, 
areatype int, area int, Period char(2),
periodyear char(4),
seriescode int, periodtype char(2), adjusted char(2)
)

CREATE TABLE ces
(
stfips char(2) NOT NULL,
areatype char(2) NOT NULL,
area char(6) NOT NULL,
periodyear char(4) NOT NULL,
periodtype char(2) NOT NULL,
period char(2) NOT NULL,
seriescode char(8) NOT NULL,
adjusted char(1) NOT NULL,
benchmark char(4) NULL,
prelim char(1) NULL,
empces numeric(9) NULL,
empprodwrk numeric(9) NULL,
empfemale numeric(9) NULL,
hours numeric(3,1) NULL,
earnings numeric(8,2) NULL,
hourearn numeric(6,2) NULL,
supprecord char(1) NULL,
supphe char(1) NULL,
supppw char(1) NULL,
suppfem char(1) NULL,
hoursallwrkr numeric(3,1) NULL,
earningsallwrkr numeric(8,2) NULL,
hourearnallwrkr numeric(6,2) NULL,
suppheallwrkr char(1) NULL
);

stfips areatype area periodyear periodtype period seriescode adjusted benchmark prelim empces empprodwrk empfemale hours earnings hourearn supprecord supphe supppw suppfem hoursallwrkr earningsallwrkr hourearnallwrkr suppheallwrkr
32 01 000000 2019 03 11 00000000 0 2018 0 1453600 0 0 0.0 0.00 0.00 0 1 1 1 0.0 0.00 0.00 1
32 01 000000 2019 03 11 00000000 1 2018 0 1443600 0 0 0.0 0.00 0.00 0 1 1 1 0.0 0.00 0.00 1
32 01 000000 2019 03 12 00000000 0 2018 1 1446400 0 0 0.0 0.00 0.00 0 1 1 1 0.0 0.00 0.00 1
32 01 000000 2019 03 12 00000000 1 2018 1 1440100 0 0 0.0 0.00 0.00 0 1 1 1 0.0 0.00 0.00 1

Please post that data as consumable dml rather than an image which we would thave to manually carve. Folks are busy and take out of their busy time to help you.

Insert into ces
Select 32, '01', etc

Help us help you

Ok, I thought that you could download it. I will work on that tomorrow.

1 Like

Insert into cestest (stfips,areatype,area,periodyear,periodtype,period,seriescode,adjusted,benchmark,prelim,empces,empprodwrk,empfemale,hours,earnings,hourearn,supprecord,supphe,supppw,suppfem,hoursallwrkr,earningsallwrkr,hourearnallwrkr,suppheallwrkr)

Values ('32','01','000000','2019','03','11','00000000','0','2018','0','1453600','0','0','0.0','0.00','0.00','0','1','1','1','0.0','0.00','0.00','1'),

('32','01','000000','2019','03','11','00000000','1','2018','0','1443600','0','0','0.0','0.00','0.00','0','1','1','1','0.0','0.00','0.00','1'),

('32','01','000000','2019','03','12','00000000','0','2018','1','1446400','0','0','0.0','0.00','0.00','0','1','1','1','0.0','0.00','0.00','1'),

('32','01','000000','2019','03','12','00000000','1','2018','1','1440100','0','0','0.0','0.00','0.00','0','1','1','1','0.0','0.00','0.00','1');

Have never done this before. If this does not work, let me know.

please try out both the DDL and DML on a test db and see what results you get.

edited the last post. the table creates and the rows insert. have to go but I will continue this in the morning.

Please test sample scrips you post?

When creating the values in the insert into, does one put single quotes (') or double quotes (") around each value? I am hoping to keep the leading zeros in place.

depends on the target column data type . If numeric or decimal then you don't I believe. just try it out and see. The only thing one needs to do is when posting something, we make sure it works on our machine first. due diligence.

I specify in the create table script that most are character. However, upon running the insert into, it drops the leading zeros. Trying to avoid using the import wizard.

if they are char r varchar add ' single quote each end
so far this is what we have from you


CREATE TABLE ces
(
stfips char(2) NOT NULL,
areatype char(2) NOT NULL,
area char(6) NOT NULL,
periodyear char(4) NOT NULL,
periodtype char(2) NOT NULL,
period char(2) NOT NULL,
seriescode char(8) NOT NULL,
adjusted char(1) NOT NULL,
benchmark char(4) NULL,
prelim char(1) NULL,
empces numeric(9) NULL,
empprodwrk numeric(9) NULL,
empfemale numeric(9) NULL,
hours numeric(3,1) NULL,
earnings numeric(8,2) NULL,
hourearn numeric(6,2) NULL,
supprecord char(1) NULL,
supphe char(1) NULL,
supppw char(1) NULL,
suppfem char(1) NULL,
hoursallwrkr numeric(3,1) NULL,
earningsallwrkr numeric(8,2) NULL,
hourearnallwrkr numeric(6,2) NULL,
suppheallwrkr char(1) NULL
);


Insert into ces (stfips,areatype,area,periodyear,periodtype,period,seriescode,adjusted,benchmark,prelim,empces,empprodwrk,empfemale,hours,earnings,hourearn,supprecord,supphe,supppw,suppfem,hoursallwrkr,earningsallwrkr,hourearnallwrkr,suppheallwrkr)

Values (32,01,'000000',2019,03,11,'00000000',0,2018,0,1453600,0,0,0.0,0.00,0.00,0,1,1,1,0.0,0.00,0.00,1)

(32,01,'000000',2019,03,11,'00000000',1,2018,0,1443600,0,0,0.0,0.00,0.00,0,1,1,1,0.0,0.00,0.00,1)

(32,01,'000000',2019,03,12,'00000000',0,2018,1,1446400,0,0,0.0,0.00,0.00,0,1,1,1,0.0,0.00,0.00,1)

(32,01,'000000',2019,03,12,'00000000',1,2018,1,1440100,0,0,0.0,0.00,0.00,0,1,1,1,0.0,0.00,0.00,1)


Did you test this on your side as is exactly?

edited the scripts. Now trying to run the following query on it. No errors but no results. My guess is that it has to do with the source. My original query was centered around a data source, ces (not cestest). But that source has confidential information hence why I created this cestest table and query.

WITH c1 AS
(SELECT e.stfips, e.areatype, e.area, e. periodyear, e.period, e.seriescode, e.empces
FROM cestest as e
WHERE e.periodtype='03'
And e.supprecord='0'
and e.periodyear=
(Select Max(periodyear)
From cestest)
and e.period=
(Select Top(1)period
From cestest
Order by periodyear desc, period desc)
and e.stfips='32'
and e.adjusted='1'
and e.areatype='01'
),
C2 AS
(Select Distinct c1.periodyear, c1.period
From c1
),
C3 As
(Select
(Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As 'month',
(Case When c2.period='01' Then (c2.periodyear -1) Else (c2.periodyear) END) As 'year'
From C2
),
C4 AS
(Select c.stfips, c.areatype, c.area, c.periodyear, c.period, c.seriescode, c.empces
From cestest as c, c2
Where c.period = c2.period
And c.periodyear = (c2.periodyear-1)
And c.supprecord='0'
and c.stfips='32'
and c.adjusted='1'
and c.areatype='01'
),
C5 AS
(Select s.stfips, s.areatype, s.area, s.periodyear, s.period, s.seriescode, s.empces
From cestest as s, C1, C3
Where s.supprecord='0'
And s.period = c3.month
And s.periodyear = c3.year
and s.stfips='32'
and s.adjusted='1'
and s.areatype='01'
),
C6 As
(Select d.seriescode, d.seriesttls, d.stfips
From cescode d, C1
Where d.stfips=C1.stfips
And d.seriescode=C1.seriescode
And (d.serieslvl ='0' Or d.serieslvl='2' Or d.serieslvl='7')
)
Select Distinct C6.seriesttls as 'Super Sector', c6.seriescode As 'Code', C1.empces As 'Employment(p)', C5.empces As 'Last Month', C4.empces As 'Last Year',
c1.empces-c5.empces As 'Net Change Mnth',
(Cast(Cast(((c1.empces /(c5.empces)-1)*100 )AS decimal (6,2) )as varchar)+ '%') '% Chg Mnth',
c1.empces-c4.empces As 'Net Change Year',
(Cast(Cast(((c1.empces /(c4.empces)-1)*100 )AS decimal (6,2) )as varchar)+ '%') '% Chg Year'
From C1, C4, C5, C6
Where (c6.seriescode = c1.seriescode and c6.seriescode = c4.seriescode and c6.seriescode = c5.seriescode)
order by c6.seriescode
;

and now we have a new table called cescode that you have not provided DDL or DML. You see how complicated this gets? and how much guess work we would be doing and possibly give you erroneous information? instead of real tables you can also give us temp tables

create table #ces
create table #cescode 

this is what I do, install sql developer locally, its free, then create a database called sqlteam. and do things there.