Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

First keywork conversion from Access SQL to T-SQL

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
Kiske01
Asked:
Kiske01
  • 5
  • 4
  • 2
  • +2
1 Solution
 
oleggoldCommented:
can't You use max/min or lag/lead for this?
0
 
oleggoldCommented:
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
 
Kiske01Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Kiske01Author Commented:
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
 
deightonCommented:
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
 
deightonCommented:
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
 
Kiske01Author Commented:
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
 
deightonCommented:
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
 
Kiske01Author Commented:
Shouldn't it be

SELECT TOP 1 CTE.Conferma  FROM CTE AS CTE2) ?
0
 
deightonCommented:
I renamed the CTE table to use it in a subquery - have you had any luck with any of it?
0
 
Kiske01Author Commented:
I'm actually banging my head against the screen...:) As soon as I can get appreciable results I'll let you know...
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now