Solved

First keywork conversion from Access SQL to T-SQL

Posted on 2012-03-29
14
283 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 39

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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