Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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