UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Some 2019 Express Problems, Any Versions    
 
   
firlandsfarm
post Jan 18 2020, 01:26 AM
Post#1



Posts: 393
Joined: 28-April 02
From: Heathfield, England


In my attempts to move into SQL (2019 Express database with link to 2008 R2 database) I'm finding many idiosyncrasies but am managing to solve most. However some are defeating me. In the code …

CODE
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
WHERE        iif([90%ers] / TotOrders > 0.90, CustNo, NULL) > 0

I get the Error message …

"Error in list of function arguments: '>' not recognized."

... on Executing. If I click OK the view seems to run OK. What's the problem with ">" and why does it seem to ignore the problem and run as expected on me saying "OK"?

Also you may have noticed the repetition of "[90%ers] / TotOrders". This is because if I try to use "[orders90%]" as defined I get an "invalid Column Name" error. It seems that unlike Access if I want to use a computed column for a further computation I need to layer another query on top. Is this really necessary?

And finally when using the following line of code in a New Query …

CODE
SUM(iif([IRPrices].[IR_Lowest] / ([HIR].[HIR_BSP] + 1)<0.9, 0.11 * 0.98, - 1) AS [90%Return],

I get a "Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'iif'." and the whole line has a 'wiggly' red line under it. If I hover the mouse over the line I'm told "SUM is not a recognised built-in function name" … really! Every SQL help page on the Internet says it is an SQL function!

And finally if I put that same code into the view screen I get an "incorrect syntax near ','" error message after a ""<" not recognised" error message (that I OK'ed on as above)!

SQL really is the most Marmite language (maybe only those in the UK will understand that! smile.gif )

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
nvogel
post Jan 18 2020, 04:46 AM
Post#2



Posts: 1,061
Joined: 26-January 14
From: London, UK


You have mentioned both 2008 and 2019 versions of SQL Server but the code you have shown us is ony valid in 2019. IIF function was new in version 2012. The fact that you are getting syntax errors here suggests either that you might be using an older version of Management Studio or that you might be running this code against SQL Server 2008. You can download the latest version of Management Studio here: https://docs.microsoft.com/en-us/SQL/ssms/d...ent-studio-ssms

CASE is much more widely used than IIF and it works in all versions. Try:

SELECT HIR_HNo, [90%ers] / TotOrders AS [orders90%],
CASE WHEN [90%ers] / TotOrders > 0.90 THEN CustNo END AS [90%Orderer]
FROM dbo.[%ers]
WHERE CASE WHEN [90%ers] / TotOrders > 0.90 THEN CustNo END > 0;

As for the "invalid column name" error, there is a logical order of evaluation and scoping rules that apply to SQL queries. There are some sound resons for this and nearly every SQL DBMS follows the same logic. Unfortunately the Access ACE engine is the exception. You could rewrite the query like this:

SELECT *
FROM
(
SELECT HIR_HNo, [90%ers] / TotOrders AS [orders90%],
CASE WHEN [90%ers] / TotOrders > 0.90 THEN CustNo END AS [90%Orderer]
FROM dbo.[%ers]
) AS t
WHERE [90%Orderer] > 0;

By the way, % symbols in column names? Most developers will avoid using symbol characters in column names as a matter of convention and style. Just a suggestion though.


This post has been edited by nvogel: Jan 18 2020, 04:48 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2020, 08:44 AM
Post#3


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


Just a general observation about the distinction between "SQL Syntax" as used in Access, and "TSQL Syntax" as used in SQL Server. SQL, or Structured Query Language, is the standard, but each RDMS implements it in slightly different ways. Access SQL and TSQL for SQL Server are, as you have found, different. As are the SQL dialects in Oracle, PostgreSQL, etc.

So, you need to allocate time in your exploration to learn how those differences impact your own queries. IMO, TSQL is far more robust and in the long run, it'll pay off. In the short term, of course, there's more of that pesky "new stuff" to learn.

Also, see our Wikipedia article on the importance of naming conventions and avoidance of special characters.
This post has been edited by GroverParkGeorge: Jan 18 2020, 08:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Jeff B.
post Jan 18 2020, 09:39 AM
Post#4


UtterAccess VIP
Posts: 10,380
Joined: 30-April 10
From: Pacific NorthWet


I'll extend George's observation one step further …

If your db has a field named "[90%ers]", I wonder if it has other fields named "[xx%ers]" and "[yy%ers]". If yes, then the underlying table (?tables?) may need further normalization.

JOPO (just one person's opinion)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
cheekybuddha
post Jan 18 2020, 10:56 AM
Post#5


UtterAccess Moderator
Posts: 12,343
Joined: 6-December 03
From: Telegraph Hill


@Jeff, since this appears to be a data warehouse of historical data for analysis purposes, then de-normalisation may well be warranted.

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Jan 18 2020, 06:47 PM
Post#6


UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada


Your first query looks just fine.
What version of SSMS are you using? If not, download + install the latest version.
(in fact Microsoft is now suggesting to download + install SSMS separate - it not even included with standard installs anymore).

Somthing here is not right. This looks just fine:
CODE
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
WHERE        iif([90%ers] / TotOrders > 0.90, CustNo, NULL) > 0


As for having to repeat "alias" ? Yes, this is my #1 gribe wth T-SQL.

There are several ways to deal with this.
Most easy?
Create the base query as a t-SQL view - link to it from Access. You can the add addtional expressions.
The other is the very old Access trick:
create a query (t-SQL) and save it as a view - and then create another query. But that is a pain.

There is a new feature called CTE - (common table Expressions) and I think it is the most clean soluion.

What you do is REMOVE your where clause from above. Then put this around:

with myQ as (

)
SELECT * from myQ Where xxxx

Now, paste your previous query inside of above. (you don't even have to read it.
The advantage of above is you can now use any "alias" column like you could in JET.

So, above becomes with a cut + paste this:

CODE
with myQ as (
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
)
SELECT * FROM myQ WHERE        [90%Orderer] > 0


And, you can use the Alias like this
CODE
with myQ as (
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
)
SELECT *, ([90%Order] * 2) as 90X2 FROM myQ WHERE [90%Orderer] > 0


So the above is what is called a CTE. It is in effect a query on query, but the whole query is NOT executed two times and it is efficient.

So the quick pattern is:

with myQ as (
<----- paste in your mess of a query - (but without the where clause)
)
select * from myQ WHERE <my conditions - and can use alias>

You find that this means no sub-query or anything else. Just shove in the basic query between the ( and ) in above. The result is you can now use the Alais as you wish, and that includes the where clause.

Anyway, something is messed up here, since your first query should work just fine. I would (if not already) download + install a matching and newer version of SSMS - that seems to be the issue here. While SSMS 2018 can connect and use "older" databases, it can have issues connecting to NEWER versions.

If you already are using a newer version of SSMS, then something else is "very" wrong here - that first query looks just fine.

R
Albert
Go to the top of the page
 
firlandsfarm
post Jan 19 2020, 06:52 AM
Post#7



Posts: 393
Joined: 28-April 02
From: Heathfield, England


Hi, wow, lots for me to respond to here but thanks for all the assistance. smile.gif Here goes in order …

nvogel: Yep I know 'iif' is not compatible with 2008R2 but all code is in 2019Ex with it using data from 2008R2. What I don't know is if 2019Ex just asks 2008R2 for the data or if it passes the whole 'iif' filter function code to 2008R2 saying "you sort it". However the problem I am raising is with the '<' operator not 'iif' and once OK'ing that '<' is not recognised the code is processed. From my Internet searches it seems many have hit upon this and not one of the suggested solutions has worked for me. I'm wondering if it is a bug!

I updated my version of SSMS to the latest (18.4) when trying to find a solution (previously 17.9). The code would not run at all in 17.9 but in 18.4 it runs as described. 17.9 is post 2012 so should not have been the problem but clearly there was something it didn't like but then maybe it's as Albert suggested later in that SSMS can have difficulties connecting to 'newer' versions of SQL Server.

Being an Access/Excel type of guy I find 'iif' much more user friendly and natural (to my mind a case is something I use to take my clothes on holiday with me! smile.gif ). But seriously I reverted to CASE when I was trying to run it in SSMS 17.9 and it ran as expected so there seems to be a problem with 'iif' and SSMS17.9.

I can see your use of a sub-query in your suggested code but it only saves one element of repetition and as this is only an example of the code in the full query (these are the 90% columns, there is similar code for 10%, 20% etc. up to the 90% illustrated) and the results of the full query will be re-queried and the table that produces this data set is over 2 million records I'm thinking of trying the temporary table route or CTE as possibly more efficient than to keep querying and re-querying such a large table. (I only found out about temporary tables yesterday while trying to find solutions to my troubles and CTE was a complete unknown until introduced by Albert in his later submission)

Yeah, I know, "% symbols in column names"! SQL/SSMS insists such column names are always contained in […] and if it works (and it does) then I see it as a matter of personal preference but thanks for the warning.

GroverParkGeorge: Hi George smile.gif … I fully appreciate your thoughts on learning the language but frankly I don't have the time because of other commitments to spend what would be months learning SQL before using it, I have an immediate need. I have no aspirations to be more than a 'competent hobbyist' who can use the language to do a job. So I'm taking the 'jump in and get it to work approach' by setting up my queries in Access Design, then taking the Access SQL and translating it to T-SQL.

I don't mind "that pesky "new stuff"" but it's more than "pesky" when it simply doesn't work and the Internet websites can't give you a solution! To me SQL is a necessary evil to do a job, I would never voluntarily submit myself to use a tool that seems designed to make it's use as difficult as possible! A case in point is it's inability to recognise a world wide universally used and accepted symbol such as "<" beggars belief.

And I appreciate the Naming Conventions in the main, I tend to use it except I always put the 'qry', 'tbl' as a suffix and not a prefix because I want related objects to group alphabetically by name and not by type of object. The article you refer to makes it clear that special characters should not be used but doesn't say why! To use a special character just for the sake of it is understandably undesirable but I can't see the problem if it actually adds to the readability of the code as I would claim it does here. To have [..%..] as a column name tells me immediately that it is a % number and [90%Orderers] is much more readable than [90PercentOrderers] and especially so than [0.90Orderers]. I see it in the same way I look at storing calculated values or not, without a reason "why not" it is more a matter for personal preference and common sense than strict imposition of fixed rules.

Jeff B/cheekybuddha: Jeff, I'm not much of a normalisation perfectionist. I'm in cheeky's camp. Yes this is a database of historical data so except for input errors the values once entered will not change. Also Jeff you are correct in your assumption re other 'N%ers' columns (please see the additional information in my response to nvogel above). there are two types of filters … one type is applied to customers' totals data and the other type is applied to customers' calculated '%' categories. I couldn't see how I could apply both filter sets at the same time so I created a query that applied the 'totals' filters and calculated the '%' columns. I then have a second Union query that filters the % columns and appends them into a list. Is that the normalise issue you were referring to Jeff?

Albert: As you may have read above I started with SSMS17.9 and then installed SSMS18.4 (which is the most recent I found) as part of my initial research which resulted in a slight improvement. So my OP was based on version 18.4.

Because I often have to layer queries to get the results I want I am thinking it's more efficient to have all queries in 2019Ex rather than for Access to get the results of a first query and then go back to and ask for the results of a second query based on the results of the first. So yes, I am wanting to have all the queries in 2019Ex with Access linked to the top layer query.

I have not come across CTE before. It's seems to be similar in a way to using temporary table which is something I came across in my research yesterday and am thinking of switching to. Anyway CTE (and temporary tables) seem to be worth further research.

As I said above the non-recognition of the '<' operator (and sometimes the '>' operator) is covered on Internet pages but I couldn't get any of the proposed solutions to work for me. frown.gif
This post has been edited by firlandsfarm: Jan 19 2020, 06:53 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
cheekybuddha
post Jan 19 2020, 07:04 AM
Post#8


UtterAccess Moderator
Posts: 12,343
Joined: 6-December 03
From: Telegraph Hill


Just out of curiosity, what happens if you change to:
CODE
-- ...
WHERE        iif(([90%ers] / TotOrders) > 0.90, CustNo, NULL) > 0

and:
CODE
SUM(iif(([IRPrices].[IR_Lowest] / ([HIR].[HIR_BSP] + 1))<0.9, 0.11 * 0.98, - 1) AS [90%Return],

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jan 19 2020, 08:50 AM
Post#9



Posts: 658
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


If you replace the % symbol in your column and table names with p100, then your query will work.

SQL, love it or hate it cheers.gif
This post has been edited by FrankRuperto: Jan 19 2020, 09:10 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
firlandsfarm
post Jan 19 2020, 04:02 PM
Post#10



Posts: 393
Joined: 28-April 02
From: Heathfield, England


I'll give it a try Frank but if I may repeat my query does work … it's just that it throws a "'>' not recognized" error before it works! I was curious why it says ">" is not recognised and then works regardless! If by changing "%" to "p100" it works it will be a lesson learned. smile.gif

Cheeky, I'll also give your suggestion a try tomorrow but I have done so much rewriting of the code in trying to resolve things that I think I may have lost the original code and will need to resurrect it!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jan 19 2020, 04:53 PM
Post#11


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


Field names wrapped in Square Brackets DO work with non-standard characters. It's not recommended, of course, but that's why the use of square brackets is part of the standard. It delimits field names.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 12:37 AM
Post#12



Posts: 658
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Despite George's assertion that wrapping object names in brackets works with non-standard characters, it may or may not have anything to do with firlands issue with the greater-than sign, but anyway its best to not use these symbols in object names.

BTW, how are the values being stored in the percentage columns? If they're stored as 0 to 1, (e.g. 0.5 = 50%), then there is no coversion required when using the number in a calculation.

Although Firlands said he wrote the query in T-SQL, What would happen if it was written in AccessSQL with field and table names starting with numbers and percent symbols wrapped in brackets, would ODBC translate it to T-SQL that works? Aside from MSSQL EXPLAIN (SHOWPLAN) showing the query plan, does it also show the T-SQL query to see how ODBC translates AccessSQL? If naming is not the issue then so be it, but it's certainly not a good naming convention to use. If David's suggestion of re-writting the query doesn't solve, then trapping the greater-than sign error since firlands said it completed the query execution is a clean solution?
This post has been edited by FrankRuperto: Jan 20 2020, 01:28 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 02:05 AM
Post#13



Posts: 658
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


I just found this in MS. It says if you enclose the greater-than sign query expression in brackets,(e.g. [>]), then that error will go away. It's listed as a workaround at the bottom of the web page iconfused.gif

https://support.microsoft.com/en-us/help/82...ccess-databases
This post has been edited by FrankRuperto: Jan 20 2020, 02:08 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 03:09 AM
Post#14


UtterAccess Moderator
Posts: 12,343
Joined: 6-December 03
From: Telegraph Hill


@Frank, if you read the page you linked to you will see that it suggests exactly what George was explaining about using square brackets to escape/allow special characters in Object Names, and which, if you read the SQL in firlandsfarm's original post, you will see that s/he was already employing.

The problem is that the > is failing to be recognised as an arithmetic operator in a field definition expression where one would usually expect it to be, so escaping it here is not going to help.

@firlandsfarm, re-looking at your first query, you actually fon't need the IIf() expression in the WHERE clause:
CODE
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
WHERE        iif([90%ers] / TotOrders > 0.90, CustNo, NULL) > 0

You should get the same[/desired] result (and with better performance by using:
CODE
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders > 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
WHERE        ([90%ers] / TotOrders) > 0.90;

unless you have any records in [%ers] that do not have a CustNo, or you have fractional CustNo's (unlikely!)

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 06:33 AM
Post#15



Posts: 658
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi David,

I was already aware of George's explanation of wrapping within brackets object names with special characters, what I am now referring to is that the MS link I provided is ironically saying to also wrap the > relop with brackets, thus your example query would now look like this:

CODE
SELECT        HIR_HNo, [90%ers] / TotOrders AS [orders90%], iif([90%ers] / TotOrders [>] 0.90, CustNo, NULL) AS [90%Orderer]
FROM            dbo.[%ers]
WHERE        ([90%ers] / TotOrders) [>] 0.90;

This post has been edited by FrankRuperto: Jan 20 2020, 06:39 AM
Attached File(s)
Attached File  GreaterThanSignInQrys.PNG ( 8.34K )Number of downloads: 2
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 07:04 AM
Post#16


UtterAccess Moderator
Posts: 12,343
Joined: 6-December 03
From: Telegraph Hill


Yes, but you have to read the Problems described in the Symptoms section:

Problem 1
You use one of the following special characters in the name of a table field:

Problem 2
You create a query expression. The query expression includes fields that contains special characters.

Problem 3
You have a query that contains query expressions. The query expressions include fields that contain special characters.


It doesn't mention a problem with operators not being interpreted properly.

The workaround addresses the problems described above, ie wrap field/object names with invalid characters in square brackets; which is what George suggested, and the OP was already doing.

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 07:18 AM
Post#17



Posts: 658
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Firlands said the following is happening with the query, as currently redacted by Firlands:

QUOTE
I'll give it a try Frank but if I may repeat my query does work … it's just that it throws a "'>' not recognized" error before it works! I was curious why it says ">" is not recognised and then works regardless!


I feel like I am drowning in a glass of water, so let's see which results Firlands obtains after all that has been said.
This post has been edited by FrankRuperto: Jan 20 2020, 07:22 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 07:20 AM
Post#18


UtterAccess Moderator
Posts: 12,343
Joined: 6-December 03
From: Telegraph Hill


Why don't you try it, Frank?

--------------------


Regards,

David Marten
Go to the top of the page
 
firlandsfarm
post Jan 21 2020, 03:22 AM
Post#19



Posts: 393
Joined: 28-April 02
From: Heathfield, England


Hi Frank, Cheeky/David ... OK, the need is for me to try out your suggestions. I sorry but I was busy all day yesterday and will try this morning. Thanks for your continued interest guys. As I've said I can 'get it to work' but I the sort of guy who likes to know why it's not working and if there is a solution/workaround. Be back later.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 06:39 PM