Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Impossible query
Message
De
17/02/2003 09:10:38
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivie
 
 
À
17/02/2003 05:04:49
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00752198
Message ID:
00754057
Vues:
38
>Dear Hilmar,
>
>Do you have any code, and can you spare it, with regard to your solution which you had sent to me?
>
>I'm not very clear how to create the UDF and how to integrate it into the SELECT SQL.
>
>Thanks,
>
>Steve

I don't have the code with me right now, but I will try to improvise. The following code is untested, but should help you get started.

The function receives, as parameters, the PK of an article, and the date you want to search for. It will search the table for the closest match. If an exact match isn't found, it will go back to the previous date, and return the price for that date.

The table would have an index Art_Date, with the expression bintoc(Article) + dtos(Date).
FUNCTION ArticlePrice(tnArticleId, tdDate)
local lnSelect
lnSelect = select() && save work area
if not used("ArticlePrice_Search")
  use ArticlePrice order "Art_Date" alias ArticlePrice_Search in 0 again
endif
select ArticlePrice_Search
set exact off
if not seek(bintoc(tnArticleId) + dtos(tdDate)
  skip -1
endif
local lyReturnValue
if Article # tnArticleId
  lyReturnValue = $0
else
  lyReturnValue = ArticlePrice
endif

set exact on && this is the default for my application,
  && so I don't bother to save the status

select (lnSelect) && restore work area
return lyReturnValue
The UDF would be called directly in a SELECT statement, thusly:
lcDate = date()
select Field1, Field2, Field3, ArticlePrice(Article, lcDate) as ArticlePrice;
  from MyTable;
  into cursor Temp
HTH,

Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform