Solved

First keywork conversion from Access SQL to T-SQL

Posted on 2012-03-29
14
294 Views
Last Modified: 2012-08-30
Hello!

I've this query used in my VB app with ADO:

SELECT t_Cartellini.IDCartellino, t_Cartellini.tipo ,t_Cartellini.Idcorso ,t_Cartellini.Nominativo, t_Cartellini.Acconto1, t_Cartellini.Opzione, t_Cartellini.DataScadenza, t_Cartellini.Indirizzo, t_Cartellini.Citta, t_Cartellini.Nazione, t_Cartellini.Hotel, t_Cartellini.Cellulare1, t_Cartellini.Cellulare2, t_Cartellini.EMail, t_Cartellini.Note1, t_Cartellini.Note2, t_Cartellini.Note3, t_Cartellini.Note4, t_Cartellini.UserID, t_Cartellini.DataRegistrazione, t_Cartellini.OraRegistrazione, IIf([t_cartellini].[tipo]=8,[t_Corsi].[descrizione],[t_maestri].[nominativo]) AS tipocorso, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[Confermata],[t_Cartellini].[Confermata])) AS Conferma, t_Classi.Classe, Min(IIf([t_cartellini].[tipo]=8,[t_RigheCorso].[data],[t_RigheOccupazioni].[Data])) AS PrimoGiorno, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[NrPersone],[t_RigheOccupazioni].[NPersone])) AS NPX
FROM (t_Maestri RIGHT JOIN (((((t_Classi RIGHT JOIN t_Cartellini ON t_Classi.IDClasse = t_Cartellini.IDClasse) LEFT JOIN t_RigheCorsiCartellini ON t_Cartellini.IDCartellino = t_RigheCorsiCartellini.IDCartellino) LEFT JOIN t_RigheCorso ON t_RigheCorsiCartellini.IDRigaCorso = t_RigheCorso.IDRigaCorso) LEFT JOIN t_OCcupazioni ON t_Cartellini.IDCartellino = t_OCcupazioni.IDCartellino) LEFT JOIN t_RigheOccupazioni ON t_OCcupazioni.IDRigaOCcupazione = t_RigheOccupazioni.IDRigaOCcupazione) ON t_Maestri.IDMaestro = t_RigheOccupazioni.IDMaestro) LEFT JOIN t_Corsi ON t_RigheCorso.IDCorso = t_Corsi.IdCorso

Open in new window


It works as expected with an MS Access 2000 database. The problem that comes with SQL server is that it does not support the "FIRST" keyword, so I'm stuck in converting this parts:

First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[Confermata],[t_Cartellini].[Confermata])) AS Conferma

Open in new window


and

First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[NrPersone],[t_RigheOccupazioni].[NPersone])) AS NPX

Open in new window


For the IIF part, there's no problems converting it to CASE WHEN, but I don't know how to deal with the FIRST. I've tried using MIN, but sometimes it gives unexpected results.

Thank you in advance!
0
Comment
Question by:Kiske01
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 37782668
can't You use max/min or lag/lead for this?
0
 
LVL 21

Expert Comment

by:oleggold
ID: 37782682
if You need first minimum value You'd say:
Min(Case([t_cartellini].[tipo] when 8,[t_RigheCorsiCartellini].[NrPersone] else [t_RigheOccupazioni].[NPersone] end)) AS NPX
0
 

Author Comment

by:Kiske01
ID: 37782807
No, I can not use Min.

in this:

First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[Confermata],[t_Cartellini].[Confermata])) AS Conferma

[t_RigheCorsiCartellini].[Confermata] and [t_Cartellini].[Confermata] are boolean values, so if two rows are one set to "1" and the other is set to "0", it will always return 0.
I need to now if the "first row" has the "confermata" value set to "1" or "0"

PS. Sorry, I forgot to add the last part of the query, with the group by clause:


GROUP BY t_Cartellini.IDCartellino,t_Cartellini.tipo ,t_Cartellini.Idcorso, t_Cartellini.Nominativo, t_Cartellini.Acconto1, t_Cartellini.Opzione, t_Cartellini.DataScadenza, t_Cartellini.Indirizzo, t_Cartellini.Citta, t_Cartellini.Nazione, t_Cartellini.Hotel, t_Cartellini.Cellulare1, t_Cartellini.Cellulare2, t_Cartellini.EMail, t_Cartellini.Note1, t_Cartellini.Note2, t_Cartellini.Note3, t_Cartellini.Note4, t_Cartellini.UserID, t_Cartellini.DataRegistrazione, t_Cartellini.OraRegistrazione, IIf([t_cartellini].[tipo]=8,[t_Corsi].[descrizione],[t_maestri].[nominativo]), t_Classi.Classe, t_Cartellini.Tipo
HAVING Min(IIf([t_cartellini].[tipo]=8,[t_RigheCorso].[data],[t_RigheOccupazioni].[Data]))>= #MM/DD/YYYY# AND Min(IIf([t_cartellini].[tipo]=8,[t_RigheCorso].[data],[t_RigheOccupazioni].[Data]))<= #MM/DD/YYYY#
ORDER BY t_Cartellini.Nominativo"
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 40

Expert Comment

by:als315
ID: 37783158
0
 

Author Comment

by:Kiske01
ID: 37783553
For what I understand I should make a subquery with the ORDER BY on t_RigheCorsiCartellini.ID (wich is the order in wich they're inserted) and select the TOP 1 of that.

Tomorrow I'll try and let you know.
0
 
LVL 18

Expert Comment

by:deighton
ID: 37785972
you could use a CTE then query and subquery the CTE

you still need to change the IIF to CASE, which you already mentioned you have covered

To guarantee any particular order in SQL, you should really apply an 'Order BY', in the CTE definition I think

try starting with something like

WITH CTE AS
(
   SELECT t_Cartellini.IDCartellino, t_Cartellini.tipo ,t_Cartellini.Idcorso ,t_Cartellini.Nominativo, t_Cartellini.Acconto1,
       t_Cartellini.Opzione, t_Cartellini.DataScadenza, t_Cartellini.Indirizzo, t_Cartellini.Citta, t_Cartellini.Nazione,
       t_Cartellini.Hotel, t_Cartellini.Cellulare1, t_Cartellini.Cellulare2, t_Cartellini.EMail, t_Cartellini.Note1,
       t_Cartellini.Note2, t_Cartellini.Note3, t_Cartellini.Note4, t_Cartellini.UserID, t_Cartellini.DataRegistrazione,
       t_Cartellini.OraRegistrazione, IIf([t_cartellini].[tipo]=8,[t_Corsi].[descrizione],
      [t_maestri].[nominativo]) AS tipocorso, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[Confermata],
      [t_Cartellini].[Confermata])) AS Conferma, t_Classi.Classe, Min(IIf([t_cartellini].[tipo]=8,[t_RigheCorso].[data],
      [t_RigheOccupazioni].[Data])) AS PrimoGiorno, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[NrPersone],
      [t_RigheOccupazioni].[NPersone])) AS NPX
FROM (t_Maestri RIGHT JOIN (((((t_Classi RIGHT JOIN t_Cartellini ON t_Classi.IDClasse = t_Cartellini.IDClasse)
LEFT JOIN t_RigheCorsiCartellini ON t_Cartellini.IDCartellino = t_RigheCorsiCartellini.IDCartellino)
LEFT JOIN t_RigheCorso ON t_RigheCorsiCartellini.IDRigaCorso = t_RigheCorso.IDRigaCorso)
LEFT JOIN t_OCcupazioni ON t_Cartellini.IDCartellino = t_OCcupazioni.IDCartellino)
LEFT JOIN t_RigheOccupazioni ON t_OCcupazioni.IDRigaOCcupazione = t_RigheOccupazioni.IDRigaOCcupazione) ON t_Maestri.IDMaestro = t_RigheOccupazioni.IDMaestro) LEFT JOIN t_Corsi ON t_RigheCorso.IDCorso = t_Corsi.IdCorso
)
SELECT *, (SELECT TOP 1 TE2.Conferma  FROM CTE AS CTE2) AS FirstConferma FROM CTE
0
 
LVL 18

Expert Comment

by:deighton
ID: 37785975
spotted a typo

WITH CTE AS
(
   SELECT t_Cartellini.IDCartellino, t_Cartellini.tipo ,t_Cartellini.Idcorso ,t_Cartellini.Nominativo, t_Cartellini.Acconto1,
       t_Cartellini.Opzione, t_Cartellini.DataScadenza, t_Cartellini.Indirizzo, t_Cartellini.Citta, t_Cartellini.Nazione,
       t_Cartellini.Hotel, t_Cartellini.Cellulare1, t_Cartellini.Cellulare2, t_Cartellini.EMail, t_Cartellini.Note1,
       t_Cartellini.Note2, t_Cartellini.Note3, t_Cartellini.Note4, t_Cartellini.UserID, t_Cartellini.DataRegistrazione,
       t_Cartellini.OraRegistrazione, IIf([t_cartellini].[tipo]=8,[t_Corsi].[descrizione],
      [t_maestri].[nominativo]) AS tipocorso, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[Confermata],
      [t_Cartellini].[Confermata])) AS Conferma, t_Classi.Classe, Min(IIf([t_cartellini].[tipo]=8,[t_RigheCorso].[data],
      [t_RigheOccupazioni].[Data])) AS PrimoGiorno, First(IIf([t_cartellini].[tipo]=8,[t_RigheCorsiCartellini].[NrPersone],
      [t_RigheOccupazioni].[NPersone])) AS NPX
FROM (t_Maestri RIGHT JOIN (((((t_Classi RIGHT JOIN t_Cartellini ON t_Classi.IDClasse = t_Cartellini.IDClasse)
LEFT JOIN t_RigheCorsiCartellini ON t_Cartellini.IDCartellino = t_RigheCorsiCartellini.IDCartellino)
LEFT JOIN t_RigheCorso ON t_RigheCorsiCartellini.IDRigaCorso = t_RigheCorso.IDRigaCorso)
LEFT JOIN t_OCcupazioni ON t_Cartellini.IDCartellino = t_OCcupazioni.IDCartellino)
LEFT JOIN t_RigheOccupazioni ON t_OCcupazioni.IDRigaOCcupazione = t_RigheOccupazioni.IDRigaOCcupazione) ON t_Maestri.IDMaestro = t_RigheOccupazioni.IDMaestro) LEFT JOIN t_Corsi ON t_RigheCorso.IDCorso = t_Corsi.IdCorso
)
SELECT *, (SELECT TOP 1 CTE2.Conferma  FROM CTE AS CTE2) AS FirstConferma FROM CTE
0
 

Author Comment

by:Kiske01
ID: 37786444
Sorry, I've never used CTEs before... I can't get the logic behind:

SELECT *, (SELECT TOP 1 CTE2.Conferma  FROM CTE AS CTE2) AS FirstConferma FROM CTE

could you please explain it?
0
 
LVL 18

Expert Comment

by:deighton
ID: 37786500
the CTE is an initial query that you can then query as if it was a table

my theory behind (SELECT TOP 1 CTE2.Conferma  FROM CTE AS CTE2) is just to get the first value for Conferma   to replace the Access 'First' function
0
 

Author Comment

by:Kiske01
ID: 37786679
Shouldn't it be

SELECT TOP 1 CTE.Conferma  FROM CTE AS CTE2) ?
0
 
LVL 18

Expert Comment

by:deighton
ID: 37786994
I renamed the CTE table to use it in a subquery - have you had any luck with any of it?
0
 

Author Comment

by:Kiske01
ID: 37787144
I'm actually banging my head against the screen...:) As soon as I can get appreciable results I'll let you know...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37793606
What you need to understand is that there is no concept of "first" in SQL Server.  So you have to tell us what you mean by first.  Traditionally, this has been handled using MIN and a derived table, although nowadays it is more common to use a Windowing function such as ROW_NUMBER.  Yes, while it is syntactically correct to use a TOP without a an ORDER BY clause, it is meaningless.  The results are not guaranteed.  It is equivalent of saying:  "Give me a row, I don't care which one".

Having said that, I suspect your biggest problem is not the minor syntax changes, but rather that overly complex FROM clause and the sooner you break it down to a single OUTER JOIN (LEFT or RIGHT) instead of a mess of LEFT and RIGHT JOINs the better.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38350317
I have no idea why you have awarded me the points when deighton was the one that answered your question.  Feel free to "Request Attention" to get the question re-opened so that you can award points correctly.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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