Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

invalid sql query near THEN

Posted on 2016-08-16
24
Medium Priority
?
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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 668 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 41

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
 

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 668 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 41

Assisted Solution

by:Sharath
Sharath earned 664 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 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