Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
3
Erster Beitrag:
vor 1 Woche, 3 Tagen
Letzter Beitrag:
vor 1 Woche, 3 Tagen
Beteiligte Autoren:
Curtis, kingdr

[WD20] HFSQL Update with 2 Tables

Startbeitrag von Curtis am 12.06.2017 20:41

I am using HFSQL C/S files. I have a Payment file and a Receipt file. Payment is linked to Receipt by ReceiptID. How can I write a valid query that matches Payments with Receipts and sets Payment.Amount to the matching Receipt.Total?


UPDATE Payment
SET Payment.Amount = (SELECT Receipt.Total FROM Receipt WHERE Payment.ReceiptID = Receipt.ReceiptID)
//ERROR : No analysis opened: Receipt file not described.

UPDATE Payment
SET Payment.Amount= Receipt.Total
FROM Payment
INNER JOIN Receipt ON Payment.ReceiptID = Receipt.ReceiptID
//ERROR : Unexpected word: FROM.

UPDATE Payment
INNER JOIN Receipt ON Payment.ReceiptID = Receipt.ReceiptID
SET Payment.Amount= Receipt.Total
//ERROR: Unable to cast data to type of JOIN.AppliedAmount item.

UPDATE Payment
SET Payment.Amount = (SELECT Receipt.Total FROM Receipt WHERE Receipt.ReceiptID = Payment.ReceiptID)
WHERE EXISTS (SELECT * FROM Receipt WHERE Receipt.ReceiptID = Payment.ReceiptID)
//ERROR : No analysis opened: Receipt file not described.

UPDATE Payment,Receipt SET Payment.Amount = Receipt.Total
WHERE Payment.ReceiptID = Receipt.ReceiptID
//ERROR: Unable to cast data to type of JOIN.AppliedAmount item.

Antworten:

Re: [WD20] HFSQL Update

You should do this as below:

UPDATE Payment JOIN Receipt ON Payment.ReceiptID = Receipt.ReceiptID SET Payment.Amount= Receipt.Total

and it should work with analysis file.

HTH

King

von kingdr - am 12.06.2017 21:15

Re: [WD20] HFSQL Update

Oops. Payment.Amount was set to text instead of currency. Now it works.

Thanks kingdr.

EDIT: I was wrong. It's still not working. I'm still getting "Unable to cast data to type of JOIN.AppliedAmount item. The only time the query was successful was when I queried an empty Db on accident.

von Curtis - am 12.06.2017 21:58
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.