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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Running Out Of Expletives For T-SQL, SQL Server 2008 R2    
 
   
firlandsfarm
post Jul 4 2019, 09:44 AM
Post#21



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


can't agree with your summation George ... all examples you have given are either different versions by way of competing manufacturers (MYSQL etc.) or different products (Sugar drinks or diet drinks). T_SQL and Access SQL are the same products (both SQL) by the same manufacturer!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 4 2019, 09:54 AM
Post#22


UA Admin
Posts: 35,324
Joined: 20-June 02
From: Newcastle, WA


And that's where you are wrong, of course.

Access SQL is a SQL version based on Access.

TSQL is a SQL version based on SQL Server.

Just because Ford produces F150s and Focuses doesn't mean they should look, operate and function exactly alike.
This post has been edited by GroverParkGeorge: Jul 4 2019, 09:54 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jleach
post Jul 4 2019, 10:00 AM
Post#23


UtterAccess Editor
Posts: 10,021
Joined: 7-December 09
From: St Augustine, FL


Once you start exploring outside of Access' dialect of SQL, you realize that there's a very solid subset shared among all the major players, and a few quirks to each. Most general CRUD-based SQL statements work near-identically across all the main RDMS dialects.

Access, in fact, tends to be the outlier on this (mostly, I suspect, to make certain things easier for the non-SQL developer).

All in all, once you get a few under your belt it's like driving a different model car: on this one, the windshield wiper control is here, and the blind spot is small. On another, the windshield wiper controls are there, and the seat's more comfy. But they all basically operate the same way.

Cheers,

--------------------
Go to the top of the page
 
nvogel
post Jul 4 2019, 10:03 AM
Post#24



Posts: 966
Joined: 26-January 14
From: London, UK


To be exact, they are not truly the same manufacturer because the SQL Server engine was developed by Sybase before Access existed. Microsoft inherited and developed an existing product.

More importantly they are not remotely the same kind of product. ACE (the SQL-like engine in Access) is a desktop tool for file-based data access whereas SQL Server is a client-server DBMS. ACE has been overtaken by newer, cheap and powerful DBMS technologies. Even where technically feasible to add more SQL features to ACE there would be no commercial reason for Microsoft to do so because customers who want those features will just use a SQL DBMS instead.
Go to the top of the page
 
firlandsfarm
post Jul 5 2019, 04:30 AM
Post#25



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


I'm sorry George but to use your words ... And that's where you are wrong, of course. smile.gif It's only an esoteric argument so the rights and wrongs are of no real value. It's a subject of personal opinion and our personal opinions are at variance. hat_tip.gif

Thanks for your continued input jleach I see the car driving analogy but I just find some of the commands in SQL to be so illogical ... CAST ... I cast a fly when fishing, my mum casts off when knitting, a foundry worker casts a mould, a sailor casts-off when leaving moorings but I've never known anyone cast a number ... they format a number. What can be simpler, it just makes me think they cynically chose cast thinking now what's the most user unfriendly word we can use, can't be something as obvious as format! sarcasm.gif

And thanks for your attempts to convert me nvogel but I'm afraid you never get a second chance to make a first impression and SQL has made it's first impression. I think it is in some areas a poorly thought out set of controls/commands that made a product as difficult as possible rather than as usable as possible. It is to me strangely illogical in part for something that is all about being logical.

But let's try and move on from my views on SQL, they are just me venting my personal opinion, they are not in anyway intended as personal comments directed at contributors here. You have admirably defended SQL, it would be proud of you smile.gif ... I'm more interested in any thoughts on design structuring for a large table database.



--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 5 2019, 08:58 AM
Post#26


UA Admin
Posts: 35,324
Joined: 20-June 02
From: Newcastle, WA


Unfortunately for your argument, there is a commonly accepted set of terms and definitions within EVERY discipline. The fact that the same word is used in multiple ways in different environments doesn't invalidate ANY of those uses. If it did, we'd have to start inventing thousands of new words to clean up everything from nursery rhymes to religious hymns.

CAST means one thing in the context of fishing.

It means another thing in the context of a TSQL statement.

And still another in the context of knitting.

If that is a stumbling block for you in learning to work with a new area of knowlege, then perhaps you might be in over your head in the case of SQL languages to begin with. The English language, in particular, and all languages in general are rife with such cases (oh, there's another one. Case can mean one of two forms of letters in most western languages or it can mean a criminal prosecution or it can mean a subject of study, and probably other things I can't think of just now. )

There are at least two ways to approach new learning opportunities. Kick and scream and complain every step along the way or attempt to master every step as you reach it. I suggest the latter is the more productive use of your time.

There are plenty of things to complain about with Access and we all do from time to time. I just think this isn't one of them and it is a waste of time to harp on it.

To sum up the immediate situation and address your question, I would say that you do need to consider a SQL Server (or MySQL or some other Server based RDBMS) given the environment in which you are working. Splitting one Access Back End into multiple Access Back Ends is possible, but at the loss of Referential Integrity across tables in those separate Back Ends.
This post has been edited by GroverParkGeorge: Jul 5 2019, 09:34 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Daniel_Stokley
post Jul 8 2019, 07:29 AM
Post#27



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


@George, hat_tip.gif
Go to the top of the page
 
cheekybuddha
post Jul 8 2019, 04:07 PM
Post#28


UtterAccess VIP
Posts: 11,424
Joined: 6-December 03
From: Telegraph Hill


>> but I've never known anyone cast a number ... they format a number. What can be simpler <<

There is a *really* fundamental difference between CAST and Format. CAST converts the datatype, whereas Format just changes what is displayed to the user on screen. This is almost universally true of all programming languages, not solely a feature of T-SQL.

It has specific functions in Access for this as well - see CInt(), CDbl(), CLng(), CDate(), etc ...

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


Regards,

David Marten
Go to the top of the page
 
jleach
post Jul 9 2019, 02:35 AM
Post#29


UtterAccess Editor
Posts: 10,021
Joined: 7-December 09
From: St Augustine, FL


If it makes you feel any better, there's also a CONVERT() function, as well as CAST smile.gif


The concept of casting a value from one type to another isn't language-specific at all: it's an industry-wide term used across all languages, maybe not as common as "variable", but still present everywhere. https://en.wikibooks.org/wiki/Computer_Prog...type_conversion

They didn't exactly pick it out of a hat to confuse you.

--------------------
Go to the top of the page
 
AlbertKallal
post Jul 12 2019, 01:20 PM
Post#30


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


Ok, LOTS of issues here.

First up:

Yes, the lack of the ability to use an alias in an expression is certainly one of my HUGE pet peeves with T-SQL.

However, THERE IS a feature in SQL server designed to attack and deal with this issue.

That feature?

It called a CTE (Common Table expressions). Despite the poor and confusing name? (which is why we don’t’ use this feature!!!).

It lets you re-use a table column in additional expressions.

And this feature was introduced in SQL server 2005.

It is VERY unfortunate that any book, article or anything in regards to migrating from Access to SQL seems to leave this issue out. And EVERYONE who ever worked with Access + SQL server will instantly get bitten by this issue.

(This too long already thread and post of mine is proof of this shortcoming!!!).

If only we had been taught about CTE’s!!!

But, that’s why I am here today!!!


Anyway, just keep in mind that CTE’s are essentially a query on query – but you don’t have to create two views as separate.

And OFTEN (before I started using CTE’s), then one would often simply build the base view, and then create a new view against that view.

This query on query (or view on view) thus allows re-use of the columns just like in Access SQL.

And I will fully admit in the past I often done this trick in Access. So the idea of query on query is a decent solution.

However, as noted, CTE’s are designed for this purpose, and you thus don’t need to create a view on view anymore.

I am not sure WHY CTE’s are not more suggested. I mean, if you come from a SQL background – then you tend to ignore them. That is because such folks NEVER enjoyed what we could do with Access SQL.

But from an Access SQL background to T-SQL?

Well, like flashing lights and when the submarine is diving?

Awooogha, Awoogha, lights flashing, DIVE DIVE DIVE!!!

In other words, from an Access point of view, then CTE’s are a welcome feature, and one that we will appreciate.

Ok, simple example time.

So, say in Access we have this nice gem:

CODE
SELECT ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate,
(Purchased - Payments) as Balance,
(Balance * TaxRate) as BalanceWithTax
FROM Customers


Now, I am keeping this clean – since a massive huge SQL query example is VERY LIKELY WHY we have xx posts in this thread, and no suggesting to use CTE’s.

Ok, as noted, in T-SQL, we can’t re-use expressions. Thus, normally in T-SQL, the above NICE and EASY to read SQL becomes this smelly pile of camel dung:


(do not really read this next query – PLEASE save the world, and your pain – just a quick glance).

CODE
SELECT ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate,
((SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) -
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id)) as Balance,
( (SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) -
    (SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id)) *
    (SELECT TaxRate from TaxRates where State = Customers.State) as BAlanceWithTax
FROM Customers



I feel the pain of my UA and Access brethren all around the world!!!!

Ok, let’s now re-write this using CTE’s.

You get this:

CODE
WITH MyCTE (ID, Company, State, Purchases, Payments, TaxRate)

AS
(SELECT ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate
FROM Customers)

SELECT ID, Company, State, Purchases, Payments, TaxRate,
           (Purchases - Payments) as Balance,
           ( (Purchases - Payments) * TaxRate) as BalanceWithTax
             from mycte



Not bad, not bad at all!!!

I can only point out that while CTE’s gets us MOST of the way down this road to some sanity, and keeping us out from rooms requiring padded walls?

You STILL can’t in the CTE re-use expressions. So in above, I could not re-use “balance”, but in most cases, you have at least tamed the beast, and the repeated expression (for Balance + tax) was quite easy to swallow here.

But, for now, I hope the Access community benefits from the idea and use of CTE’s.

So, in most cases, what I will do is build up the ugly pile of camel dug T-SQL, but NOT YET introduce the re-used, or expressions such as

(Purchases – Payments) as Balance.

Once the messy “base” query is working, then add the CTE columns at the top,

and then have fun with re-using the columns in the query you write below the mess at the bottom.

I hope the above will save some pain and suffering for the Access community at large.

The above works fine in SQL views, and even PT query from Access (a HUGE deal, since doing a view on view in a PT query is rotten and all these introduction of selects , or self joins to the table get a "C" or "D" from me if I was marking your SQL test paper!!

Good luck - group hugs!!!


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada




Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 11:35 AM