Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

invalid sql query near THEN

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
actsoft
Asked:
actsoft
  • 11
  • 6
  • 5
  • +1
4 Solutions
 
Randy PooleCommented:
Most wont download or open docx files, including myself. Post the internals using the Code block.
0
 
actsoftAuthor Commented:
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
 
wittysloganCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
actsoftAuthor Commented:
the  = '0816' is the result of the WHEN statement.
The [0716] & [0816] are other numeric fields in the table.
0
 
Randy PooleCommented:
How do you have the fields [0716] and [0816] declared in the table?
0
 
Randy PooleCommented:
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
 
wittysloganCommented:
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
 
actsoftAuthor Commented:
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
 
Randy PooleCommented:
Did you try my solution above?
0
 
actsoftAuthor Commented:
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
 
SharathData EngineerCommented:
Can you explain what are you trying to achieve?  If we know that, it is easy to write the CASE statement.
0
 
Randy PooleCommented:
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
 
actsoftAuthor Commented:
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
 
wittysloganCommented:
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
 
actsoftAuthor Commented:
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
 
wittysloganCommented:
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
 
actsoftAuthor Commented:
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
 
wittysloganCommented:
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
 
actsoftAuthor Commented:
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
 
Randy PooleCommented:
Can you paste you ENTIRE SQL statement, not just a portion of it.
0
 
SharathData EngineerCommented:
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
 
actsoftAuthor Commented:
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
 
actsoftAuthor Commented:
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
 
actsoftAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 11
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now