Solved

First keywork conversion from Access SQL to T-SQL

Posted on 2012-03-29
14
268 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
Comment Utility
can't You use max/min or lag/lead for this?
0
 
LVL 21

Expert Comment

by:oleggold
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
0
 

Author Comment

by:Kiske01
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Kiske01
Comment Utility
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
Comment Utility
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
Comment Utility
Shouldn't it be

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

Expert Comment

by:deighton
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now