Solved

invalid sql query near THEN

Posted on 2016-08-16
24
37 Views
Last Modified: 2016-08-23
I had this question after viewing need sql CASE statement to replace excel formula.

I thought the solution presented fixed the problem, however it did not.

The attached documents show the error messages after a couple different changes to the expression.

I can't find any examples of expressions that add multiple numeric fields after the "THEN" portion of the CASE WHEN expression.
Is this type of statement just not valid?
case.docx
CASE-WHEN-errors.docx
0
Comment
Question by:actsoft
  • 11
  • 6
  • 5
  • +1
24 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41758070
Most wont download or open docx files, including myself. Post the internals using the Code block.
0
 

Author Comment

by:actsoft
ID: 41758087
CASE
 WHEN  (month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
 = '0816' THEN [0716]+[0816]
 END

or
CASE
 WHEN  (month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
 = 0816 THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")
 END

both produce the error "invalid sql query near THEN (repeat the statement after THEN)
the fields I'm trying to add together are numeric data.

0716 & 0816 are expression names within the table.
thanks
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 41758111
i think what you are trying to do is more like:

select CASE
 WHEN  datepart(month,GETDATE()) < 10 then '0'+cast(datepart(month,GETDATE()) as varchar)
else cast(datepart(month,GETDATE())  as varchar)
end
0
 

Author Comment

by:actsoft
ID: 41758144
the  = '0816' is the result of the WHEN statement.
The [0716] & [0816] are other numeric fields in the table.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41758149
How do you have the fields [0716] and [0816] declared in the table?
0
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 167 total points
ID: 41758164
I created a temp table, you just need to alter the Select to fit your needs
create table #t ([0716] int,[0816] int)
insert into #t select 1,1
insert into #t select 5,5

Select 
CASE
(CASE WHEN month(GETDATE())< 10 then '0'+cast(month(GETDATE()) as varchar) ELSE cast(month(GETDATE()) as varchar) END) 
+substring(cast(year(GETDATE()) as varchar),3,2)
WHEN
	 '0816' THEN [0716]+[0816]
ELSE 
	''
END from #t
drop table #t

Open in new window

0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 41758177
try this:
 select case when (CASE
 WHEN  datepart(month,GETDATE()) < 10 then '0'+cast(datepart(month,GETDATE()) as varchar)
else cast(datepart(month,GETDATE())  as varchar)
end
+substring(cast(datepart(yy,getdate())as char(4)),3,2))
= 0816 THEN ('"CI_UDT_ITEM_SALES_PROJ"."UDF_0716"'+'"CI_UDT_ITEM_SALES_PROJ"."UDF_0816"')
 END
0
 

Author Comment

by:actsoft
ID: 41758199
with that the entire statement fails before it was only after the "THEN" that failed.

the fields "0716" & "0816" etc are:
Expression Name: 0716
Expression Type: Data Field
Expression Source: CI_UDT_ITEM_SALES_PROJ.UDF_0716
Lookup Type: Assumed from Base Table

Thanks again
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41758210
Did you try my solution above?
0
 

Author Comment

by:actsoft
ID: 41758251
Hi Randy,
I tried it and it failed as well. But I see where you are going with it.
I'll try some other create tables and see where I get.
thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41758277
Can you explain what are you trying to achieve?  If we know that, it is easy to write the CASE statement.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41758298
Please post your exact SQL statement which you stated failed..  Unfortunately that kind of response gives us nothing to go on. Kind of taking your car to a mechanic and saying it's broken.    If you copy and paste the SQL I gave you, it works perfectly based on what you are trying to do.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:actsoft
ID: 41758429
Randy, I copied your exact statement:
create table #t ([0716] int,[0816] int)
 insert into #t select 1,1
 insert into #t select 5,5
Select  
CASE  
(CASE WHEN month(GETDATE())< 10 then '0'+cast(month(GETDATE()) as varchar) ELSE cast(month(GETDATE()) as varchar) END) +substring(cast(year(GETDATE()) as varchar),3,2)
 WHEN
 '0816' THEN [0716]+[0816]
 ELSE
  ''
 END from #t
 drop table #t

For all:
I am using Sage Intelligence for Sage 100 ERP.
I have a table containing columns labeled 0116; 0216; 0316; 0416...1216. These fields contain numbers for projected quantities sold in a given month.

This table links to other data tables where I have formulas that use indexing, matching and using vlookups to match data in those other tables.

in this table I have a expression labeled TDMMYY
expression for that field is:
case when month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
(it works fine)

I now need a field that accumulates the values in 0716 & 0816 if the resulting value in TDMMYY is 0816.
If the value in TDMMYY is 0916 then accumulate the values in 0716; 0816 & 0916.
if the value in TDMMYY is 1016 then accumulate the values in 0716; 0816; 0916 and 1016
etc.

back in the beginning of this post I attached word docs with screen shots. I understand that no one wants to open them, so I'm trying my best to describe without visuals.

thank you all for your assistance and your patience.
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 41758518
hello,
What error are you getting from:
select case when (CASE
 WHEN  datepart(month,GETDATE()) < 10 then '0'+cast(datepart(month,GETDATE()) as varchar)
else cast(datepart(month,GETDATE())  as varchar)
end
+substring(cast(datepart(yy,getdate())as char(4)),3,2))
= 0816 THEN ('"CI_UDT_ITEM_SALES_PROJ"."UDF_0716"'+'"CI_UDT_ITEM_SALES_PROJ"."UDF_0816"')
 END

i get the answer:

"CI_UDT_ITEM_SALES_PROJ"."UDF_0716""CI_UDT_ITEM_SALES_PROJ"."UDF_0816"

what answer are you expecting?

i can see from your document that you are trying to add dates together 0716+0816+0916.  What sort of answer are you expecting from that?
thanks
0
 

Author Comment

by:actsoft
ID: 41758572
the error is "invalid sql query", then repeats the formula and the table names.

The fields labeled 0716; 0816 0916 etc are not dates they are just labels that resemble dates.
The fields contain numbers for each item in the table.

partial table layout
item                0116     0216     0316     0416     0516     0616     0716     0816     0916     1016
ABC123             5            6            7           8           9           10           11         12          13         14
DEF456             10          11         12        13          14          15           16         17          18         19

The answer for "0816" item ABC123 would be 23 (11 + 12)
"0916" for item ABC123 would be 36 (11 +12 + 13)
for item DEF456 "0816" the answer would be 33 (16 + 17)
for item DEF456 "1016"  the answer would be 70 (16 + 17+ 18+19)
this particular formula is not using the columns 0116 through 0616, others will. (assuming I can get one to work)

So if TDMMYY (Todays date mmyy) = "0816" then add together the values in "0716" and "0816" for each item.

I hope that helps. thanks
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 41759037
hello,
can you post a screen shot of the query in query analyzer producing the above error you mention.

From what i am now understanding the question is not one of syntax it is about gettign the UDFs to work.  is that correct?
0
 

Author Comment

by:actsoft
ID: 41759258
This:
CASE
 WHEN  (month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
 = 0816 THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")
 END

Produces this error:
---------------------------
Check Failed
---------------------------
Invalid sql query near  THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")

 END
The way I understand this, all is OK up until the word "THEN", correct?









 FROM ("CI_UDT_ITEM_SALES_PROJ"

INNER JOIN "CI_Item"

  ON "CI_UDT_ITEM_SALES_PROJ"."UDF_ITEM_CODE" = "CI_Item"."ItemCode")

 WHERE 1 = 0
---------------------------
OK  
---------------------------
0
 
LVL 7

Assisted Solution

by:wittyslogan
wittyslogan earned 167 total points
ID: 41759519
you have inputted:
CASE
 WHEN  (month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
 = 0816 THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")
 END

I suggested:
select case when (CASE
 WHEN  datepart(month,GETDATE()) < 10 then '0'+cast(datepart(month,GETDATE()) as varchar)
else cast(datepart(month,GETDATE())  as varchar)
end
+substring(cast(datepart(yy,getdate())as char(4)),3,2))
= 0816 THEN ('"CI_UDT_ITEM_SALES_PROJ"."UDF_0716"'+'"CI_UDT_ITEM_SALES_PROJ"."UDF_0816"')
 END

what error do you get from what i suggested?
0
 

Author Comment

by:actsoft
ID: 41759581
from what you suggested:

---------------------------
Check Failed
---------------------------
Invalid sql query near e when (CASE

WHEN datepart(month,GETDATE()) < 10 then '0'+cast(datepart(month,GETDATE()) as varchar)

else cast(datepart(month,GETDATE()) as varchar)

end

+substring(cast(datepart(yy,getdate())as char(4)),3,2))

= 0816 THEN ('"CI_UDT_ITEM_SALES_PROJ"."UDF_0716"'+'"CI_UDT_ITEM_SALES_PROJ"."UDF_0816"')

END











 FROM ("CI_UDT_ITEM_SALES_PROJ"

INNER JOIN "CI_Item"

  ON "CI_UDT_ITEM_SALES_PROJ"."UDF_ITEM_CODE" = "CI_Item"."ItemCode")

 WHERE 1 = 0
---------------------------
OK  
---------------------------
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41759639
Can you paste you ENTIRE SQL statement, not just a portion of it.
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 41759781
try this.
SELECT CASE WHEN substring(replace(convert(varchar,getdate(),5),'-',''),3,4) = 0816 
            THEN '"CI_UDT_ITEM_SALES_PROJ"."UDF_0716"'+'"CI_UDT_ITEM_SALES_PROJ"."UDF_0816"' END

Open in new window

0
 

Author Comment

by:actsoft
ID: 41760894
Sharath, your suggestion produced the same error as before.

Randy,
the complete statement I am using is:

CASE
 WHEN  (month(GETDATE()) < 10 then '0'+cast(month(GETDATE()) as varchar)
else cast(month(GETDATE()) as varchar)
end +
substring(cast(year(GETDATE()) as varchar),3,2)
 = 0816 THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")
 END

Which I change with each suggestion above. None of them seem to make a difference.

They produce basically this same error:

Invalid sql query near  THEN ("CI_UDT_ITEM_SALES_PROJ"."UDF_0716"+"CI_UDT_ITEM_SALES_PROJ"."UDF_0816")

  END
 
  FROM ("CI_UDT_ITEM_SALES_PROJ"

 INNER JOIN "CI_Item"

   ON "CI_UDT_ITEM_SALES_PROJ"."UDF_ITEM_CODE" = "CI_Item"."ItemCode")

  WHERE 1 = 0
 ---------------------------
 OK  

The 1st part of the statement produces a Varchar, then adding 2 fields with numeric data.
Is there a need for a clarification or switch to numbers?

thanks
0
 

Accepted Solution

by:
actsoft earned 0 total points
ID: 41761009
To All who responded, Thank You.
The statement that finally worked is:
CASE WHEN
CASE
when (month(GETDATE()) < 10
then
'0'+ cast(month(GETDATE()) as varchar)
else
cast(month(GETDATE()) as varchar)
end +substring(cast(year(GETDATE()) as varchar),3,2)
= '0816'
THEN
CI_UDT_ITEM_SALES_PROJ.UDF_0716 + CI_UDT_ITEM_SALES_PROJ.UDF_0816
END
0
 

Author Closing Comment

by:actsoft
ID: 41766626
solution provided by a different technician than those responding here. After all the work that was contributed, I wanted to post the result that worked for future reference.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
The duplicate key value is (<NULL>) 14 46
SQL JOIN 6 39
scheduling a stored procedure in azue sql server 3 19
Stored procedure 23 25
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now