Discussion :

[Chris1982]

Bonjour à tous,

J'aurais besoin d'aide à propos d'une requête SQL (MSSQL 2005) qui me pose problème depuis 2 jours. Voici quelques informations.

J'ai 3 tables (je simplifie les noms et nombre de champs pour une meilleure lisibilité):

PRODUITS: contient les champs prdID (numeric) et prdName (varchar)

VILLES: contient les champs postcodeID (numeric), postcode (numeric), city (varchar), latitude (numeric) et longitude (numeric)

OTM: contient les champs _prdID (numeric) et _postcodeID

et une fonction SQL (scalar): getDistance qui accepte 3 paramètres: postcodeID, latitude et longitude qui renvoie la distance calculée entre la ville dont le postcodeID est spécifié (et qui est connu dès le départ) et les autres villes inclues dans la requête.

Par exemple, prenons un client qui habite VilleX dont le postcodeID est 555.
La requête suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT city, getDistance(555, latitude, longitude) AS [distance]
FROM VILLES ORDER BY distance

retourne toutes les villes présentes dans la table VILLES classées en fonction de leur distance pa rapport à VilleX. cette fonction marche à merveille mais clairement, je dois l'utiliser dans une requête qui utilise la table VILLES (pour disposer des deux champs latitude et longitude).

Maintenant, mon problème.

Chaque produit contenu dans la table PRODUITS peut être retiré par le client à différents endroits dans le pays. Pour chacun de ces endroits existe un enregistrement dans la table OTM avec chaque fois le prdID du produit et le postcodeID de la ville.

Ce que j'attends de la requête est qu'elle me renvoie chaque produit avec UNIQUEMENT l'endroit le plus proche de celui où se trouve le client. Les champs retournés doivent être prdID, prdName, postcode, city et distance.
Et c'est là que je sèche.

J'ai essayé avec une sous-requête telle que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT _prdID, MIN(getDistance(555, POSTCODES.latitude, POSTCODES.longitude)) AS [Distance] 
FROM OTM LEFT JOIN POSTCODES 
ON OTM._postcodeID = POSTCODES.postcodeID 
GROUP BY _prdID

Fonctionne très bien mais à cause du GROUP BY, je suis limité à un seul champ: _prdID ou _postcodeID (autrement je me retrouve avec plus d'un enregistrement pour le même produit, ce qui n'est pas le but). Or, j'ai besoin de ces deux champs pour pouvoir retrouver les autres informations dont j'ai besoin.

J'espère que mon explication est un tant soit peu claire et que quelqu'un peut me donner un coup de main parce que vraiment je bloque.

Merci d'avance à tous,
Chris

[yal001]

Bonjour,

je ne suis pas sur d'avoir tout compris mais peut être pourrais tu inclure dans ce genre dans une clause WHERE:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
WHERE getDistance(555, POSTCODES.latitude, POSTCODES.longitude) 
=(SELECT (MIN(getDistance(555, POSTCODES.latitude, POSTCODES.longitude)) 
FROM OTM LEFT JOIN POSTCODES ON OTM.postcodeID = POSTCODES.postcodeID ))

Bon je sais c'est moche, mais je pense que ça pourrait marcher (en faisant attention aux parenthèses )

[Chris1982]

Bonjour,
Merci pour la réponse mais je ne crois pas que j'ai été assez précis.
Je vais essayer de spécifier.

Disons que PRODUITS contient les enregistrements suivants:

prdID prdName
54 Gsm
56 GPS

et que la table OTM contient

_prdID _postcodeID
54 192
54 296
56 145

Avec une requête "simple", je pourrais retourner les résultats suivants:
prdID prdName postcode ville distance
54 Gsm 1800 Vilvoorde 21,5
54 Gsm 1160 Auderghem 15,2
56 GPS 1000 Bruxelles 18,5

Mais ce qu'il me faudrait, c'est un seul résultat par produit avec la distance la plus petite, donc ceci:
prdID prdName postcode ville distance
54 Gsm 1160 Auderghem 15,2
56 GPS 1000 Bruxelles 18,5

J'espère que ceci clarifie.
Merci à tous,
Chris

[yal001]

Tu pourrais stocker les résultats de ta requête "simple" dans un curseur et traiter par la suite. Il y a surement plus simple et performant, mais j'ai pas d'idée intelligente pour le moment

[Chris1982]

Je suis pratiquement certain qu'il y a un moyen plus "propre" de résoudre ça... mais merci pour tes réponses, yal001

quelqu'un a une idée pour y arriver avec une seule requête?

merci,
Chris

[Waldar]

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH Post_Dist AS
(
SELECT
    postcodeID,
    getDistance(postcodeID, latitude, longitude)) AS [Distance] 
FROM POSTCODES 
)
SELECT
    OTM._prdID,
    min(Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN Post_Dist ON OTM._postcodeID = Post_Dist.postcodeID 
GROUP BY OTM._prdID