Declare an integer

[Michael57]

I have some code that subtracts to items to get a result HRDIF and this code works fine until I add a where statement that asks to show only records wher HRDIF is >0. At this point I get an error "Conversion failed when converting the varchar value 'HRDIF' to data type int." How can I stop this.

 

[SQLDenis]

HRDIF > '0'

however you will get back letters also if you have them, filter those out by using ISNUMERIC (there is a FAQ from George Mastros somewhere here)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

THis does not work. I don't get the error but it does not bring back any data. All data is numbers no letters.

 

[SQLDenis]

please post DDL and DML in that case

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

What does DDL and DML mean

 

[SQLDenis]

Data Definition Language (CREATE TABLE...)

Data Manipulation Language (INSERT, UPDATE...)

I want YOU to post a create table script, sample data and expected output

That way I know exactly what you are talking about

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

Select PMCATGT.CONTRACT,MAX (PM.MANAGER)as Manager,PMCATGT.PROJECT,PMCATGT.COSTTYPE,
SUM (PMCATGT.CURQTY) AS BUDGETHRS,SUM (PMCATGT.ACTQTY) AS ACTUALHRS,
SUM(CASE WHEN PMCATGT.COSTTYPE = 'ASSYLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'CNCLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'ENGLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'PRESSLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'QCLAB' THEN 0
ELSE PMCATGT.CURCOSTHM END) AS BUDGETCOST,
SUM(CASE WHEN PMCATGT.COSTTYPE = 'ASSYLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'CNCLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'ENGLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'PRESSLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'QCLAB' THEN 0
ELSE PMCATGT.ACTCOSTHM END) AS ACTUALCOST,
MAX (PM.STATUS),SUM( PMCATGT.CURQTY -PMCATGT.ACTQTY) AS HRDIF,
SUM(CASE WHEN PMCATGT.COSTTYPE = 'ASSYLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'CNCLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'ENGLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'PRESSLAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'QCLAB' THEN 0
ELSE (PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM) END)
AS COSTDIF from STDDAT.dbo.PMCATGT PMCATGT join STDDAT.dbo.PMCONTS PM
on PMCATGT.CONTRACT=PM.CONTRACT
Where (PM.STATUS=30 And PMCATGT.CONTRACT > '793'
And PMCATGT.CONTRACT NOT IN ('MAIN', 'ADM', 'TRN', 'TRV')
AND 'HRDIF' <'0')
Group By PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE

 

[SQLSister]

you have
AND 'HRDIF' <'0')
try
AND HRDIF >'0')

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

Sister, that is exactly what I posted

HRDIF > '0'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

I tried this and it returns all records including negative ones.

 

[SQLDenis]

>>I tried this and it returns all records including negative ones.


that should work, run this

select * from(
select '-2' as a
union all
select '-3'
union all
select '2'
union all
select '12'
union all
select '-22'
union all
select '4') x
where a > '0'

like I said post DML and DDL if you want help which won't be 50 replies. I don't need the select statement I need the create tbale and insert sample data statements

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

When number are stored as text, then the <> do not mean the same thing as when they are stored as numbers. Suggest you change the datatype of HRDIF to integer. If this isnot possible and you are positive all values in HDRIF are numbers then try:

cast(HRDIF as int) >0

"NOTHING is more important in a database than integrity." ESquared

 

[Michael57]

SQLSister, HRDIF is created in the code and does not go to a talbe is there a way I can declare it and integer to avoid this problem. They are all numbers.

 

[SQLDenis]

>>AND 'HRDIF' <'0')



??

why do you have ticks around HRDIF?

maybe you mean

AND HRDIF <'0'

what table does HRDIF come from

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ca8msm]

Sister, that is exactly what I posted

HRDIF > '0'

No, what you posted was quite different:

AND [!]'[/!]HRDIF[!]'[/!] [!]<[/!]'0')

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Michael57]

HRDIF comes from the subtraction of variables in two different tables.

 

[Michael57]

When I do not put quotation marks around HRDIF it tells me it is not a column name.

 

[SQLDenis]

AND SUM( PMCATGT.CURQTY -PMCATGT.ACTQTY)> 0

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions