Sproc error in having cluase

Below im creating a string within sproc and trying to excute it but there is a problem in the having clause. With the Having clause in the string I keep getting an error saying :
error:
'Syntax error converting the varchar value ' SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE, DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging FROM PMT_USERS ....'

But if i remove the having clause from the string it works fine not sure why...?

Having clause:
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + @input_days+'
CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css 	VARCHAR(25),
@input_days 	int,
@input_ranges 	int,
@step_range 	int,
@pend_sort 	varchar(50),
@prog_sort 	varchar(50),
@held_sort 	varchar(50),
@due_sort 	varchar(50),
@furture_sort 	varchar(50),
@aging_sort 	varchar(50),
@follow_sort 	varchar(50),
@comp_sort 	varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending 	varchar (1000),
 @SQL_prog		varchar (1000),
 @SQL_held	 	varchar (1000),
 @SQL_heldOld	 	varchar (1000),
 @SQL_heldOutput 	varchar (1000),
 @SQL_due	 	varchar (1000),
 @SQL_furture	 	varchar (5000),
 @SQL_aging	 	varchar (1000),
 @SQL_follow	 	varchar (1000),
 @SQL_comp		varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '+ char(39)+'n'+ char(39)+'
AND
((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' 
					  	OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR
(REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + @input_days+'
 
ORDER BY   '+ @aging_sort
 
EXEC (@SQL_AGING)
 
GO

Open in new window

WirtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
That's because @input_days is an int.  

Try this:

CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css 	VARCHAR(25),
@input_days 	int,
@input_ranges 	int,
@step_range 	int,
@pend_sort 	varchar(50),
@prog_sort 	varchar(50),
@held_sort 	varchar(50),
@due_sort 	varchar(50),
@furture_sort 	varchar(50),
@aging_sort 	varchar(50),
@follow_sort 	varchar(50),
@comp_sort 	varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending 	varchar (1000),
 @SQL_prog		varchar (1000),
 @SQL_held	 	varchar (1000),
 @SQL_heldOld	 	varchar (1000),
 @SQL_heldOutput 	varchar (1000),
 @SQL_due	 	varchar (1000),
 @SQL_furture	 	varchar (5000),
 @SQL_aging	 	varchar (1000),
 @SQL_follow	 	varchar (1000),
 @SQL_comp		varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '  char(39) 'n'  char(39) '
AND
((TAB_OWNER = ' char(39) @input_css char(39) ' and (OWNER_STATUS ='  char(39)  'Sent to Remote Support'  char(39) ' 
					  	OR OWNER_STATUS = '  char(39) 'Open / In Progress'  char(39) '))
OR
(REMOTE_OWNER ='  char(39)  @input_css  char(39) ' and REMOTE_STATUS = '  char(39) 'Open / In Progress'  char(39) '))
AND OVERALL_STATUS <> '  char(39) 'Canceled'  char(39) ' AND OVERALL_STATUS <> '  char(39) 'ON HOLD'  char(39) ' AND OVERALL_STATUS <> '  char(39) 'Returned to Sales'  char(39) ' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= '   ltrim(str(@input_days))  '
 
ORDER BY   '  @aging_sort
 
EXEC (@SQL_AGING)
 
GO

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Wirt,


try this

Regards,

Aneesh
CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css       VARCHAR(25),
@input_days       int,
@input_ranges       int,
@step_range       int,
@pend_sort       varchar(50),
@prog_sort       varchar(50),
@held_sort       varchar(50),
@due_sort       varchar(50),
@furture_sort       varchar(50),
@aging_sort       varchar(50),
@follow_sort       varchar(50),
@comp_sort       varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending       varchar (1000),
 @SQL_prog            varchar (1000),
 @SQL_held             varchar (1000),
 @SQL_heldOld             varchar (1000),
 @SQL_heldOutput       varchar (1000),
 @SQL_due             varchar (1000),
 @SQL_furture             varchar (5000),
 @SQL_aging             varchar (1000),
 @SQL_follow             varchar (1000),
 @SQL_comp            varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '+ char(39)+'n'+ char(39)+'
AND
((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' 
                                      OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR
(REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + CAST(@input_days as varchar) +'
 
ORDER BY   '+ @aging_sort
 
EXEC (@SQL_AGING)
 
GO

Open in new window

0
dwe761Software EngineerCommented:
You need to convert int to varchar to append it to sql string.

Also, if you print out your string, you'll often get clues to your problems as shown with Print.  Take out when it is correct.

Private Sub DeletetempTable()
 Dim SqlText As String
 
 Check if temp table exists in sysobjects,if present then drop it
 SqlText = "if object_id('tempdb..#temptablename') is not null
drop table #temptablename"
          
                
                DBHandle.BeginTrans
                DBHandle.Execute (SqlText)
                DBHandle.CommitTrans
              
End Sub
 
 
 
 
 
CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css     VARCHAR(25),
@input_days     int,
@input_ranges   int,
@step_range     int,
@pend_sort      varchar(50),
@prog_sort      varchar(50),
@held_sort      varchar(50),
@due_sort       varchar(50),
@furture_sort   varchar(50),
@aging_sort     varchar(50),
@follow_sort    varchar(50),
@comp_sort      varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending   varchar (1000),
 @SQL_prog              varchar (1000),
 @SQL_held              varchar (1000),
 @SQL_heldOld           varchar (1000),
 @SQL_heldOutput        varchar (1000),
 @SQL_due               varchar (1000),
 @SQL_furture           varchar (5000),
 @SQL_aging             varchar (1000),
 @SQL_follow            varchar (1000),
 @SQL_comp              varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '+ char(39)+'n'+ char(39)+'
AND
((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' 
                                                OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR
(REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + CONVERT(varchar,@input_days) +'
 
ORDER BY   '+ @aging_sort
 
PRINT @SQL_AGING
EXEC (@SQL_AGING)
 
GO

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

WirtAuthor Commented:
All these make it not crash but now the having clause logic isnt working...?
0
BrandonGalderisiCommented:
Well that's a different issue since your question was related to the syntax error.

But generally a HAVING is used to apply criteria to an aggregate.  

example:

having sum(SomeField)>1

You aren't doing anything on an aggregate so a HAVING is unnecessary and it should be in a WHERE clause.
0
dwe761Software EngineerCommented:
please send the output of the print command

Temporarily comment out the EXEC line and the bottom and put in the PRINT command as I suggested.

As Brandon has pointed out, your Having clause should be part of your WHERE clause.  The way I debug my SQL syntax is to paste the output of the PRINT command into a new query window in SQLServer Management Studio.

0
WirtAuthor Commented:
forgive me, this is my first sprcoc... How do i see the printed output?
0
WirtAuthor Commented:
Also i cant put the Having into the where here is my example:

SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231
AND order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'
OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC

 
 
ERROR:

Msg 147, Level 15, State 1, Line 12
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
0
dwe761Software EngineerCommented:
Oh, sorry.  
You'd only see the output of the PRINT if you are running the sproc from SQL Management Studio.

Going back to your original query, you ARE using an aggregate and you will want the HAVING clause because you are using the MIN.  However, in order to use the having clause, it must also be in the GROUP BY clause.

Do you have access to SQL Server Management studio?  

Since we don't have your table structures, we have to make our best guesses at correct syntax.  Try the query below and get it working.  THen translate it back into your code.
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
AND order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'
OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC ,
DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 
HAVING DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231

Open in new window

0
WirtAuthor Commented:
ERROR:

Msg 144, Level 15, State 1, Line 21
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
0
WirtAuthor Commented:
Also, i can get the SQL to work outside the sproc like this

SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
HAVING DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231
 
which is the same as the sproc but for some reason the having clause is being skipped in the sproc....?
0
dwe761Software EngineerCommented:
Have you made any changes to the sproc or is it the same as in your original post?
If changed, please send current script of sproc that does not work.
0
WirtAuthor Commented:
Just the having clasue

--AgingNonOrders
set @SQL_AGING = '
SELECT
 PROJECT_ACTIVITY.PROJECT_ID,
 PMT_TABS.TAB_ID,
 TAB_NAME,
 TAB_TYPE,
 CUST_NAME,
 PROJECT_DESC,
 TAB_TYPE,
 DATEDIFF(DAY,MIN(TAB_RECD_DT),
 getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),
 getDate())*2 as aging
FROM
 PMT_USERS INNER JOIN PROJECT_ACTIVITY
 ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
 INNER JOIN  PMT_CUSTOMERS
 ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
 INNER JOIN PMT_TABS
 ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
 join OPTIONS_ACT_TYPE
 on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
  ((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR (REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND  order_req = '+ char(39)+'n'+ char(39)+'
AND  OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+'

GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
--THIS HAS CHANGED
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= '  +  ltrim(str(@input_days)) +  '
 
ORDER BY   '+ @aging_sort
 
0
WirtAuthor Commented:
Even when i do this it doesnt work
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231'  
 
0
dwe761Software EngineerCommented:
I believe your query got longer than 1000 chars.  Change as below and try again.

 @SQL_aging             varchar (8000),
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WirtAuthor Commented:
I FREAKING LOVE YOU!!!!!!!!!!! Thank you so much i've beeing working on this for about 12 hrs
0
dwe761Software EngineerCommented:
Happy to assist you.
I've been stung by this same problem so when I didn't see your ORDER BY clause, the light bulb went on.  I've come to the point that whenever I set up a variable that will be used to create a SQL string, I just automatically set it to Varchar(8000) the maximum size allowed.  Then in most cases, I don't have to worry about this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.