Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 6 Monaten
Letzter Beitrag:
vor 5 Monaten, 2 Wochen
Beteiligte Autoren:
Curtis, Al, Tor-Bjarne, Peter Holemans, 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
I gave up on the above query because it was low priority. Now I have a similar SQL update I need to run, and I still can't figure this out. I even tried using the query builder, but I don't think it's possible with the query builder.

I have 2 tables linked by the primary key of table 1. I need to join on that key and update a field in table 2 with a field in table 1.

I'm about to lose my cool. Help!

von Curtis - am 23.06.2017 15:22
Hello Curtis

A couple of things puzzle me with your problem.
I appreciate that I don't know the circumstances involved, but a receipt is the same as a payment so why duplicate the data from the receipt file into a payments file ?
For instance if the receipt represents a payment for an invoice, then all you need do is to write the primary key of the invoice record as a foreign key in the receipts file.

Going back to the original issue why not hreadseekfirst() in the payments file and then hModify()

Regards
Al

von Al - am 23.06.2017 16:08
Good point on the duplicate data. I don't remember off the top of my head if it was really necessary to have this, but it's pretty low priority right now.

My priority issue is I want to update a bunch of old data. The issue I have now is that I'm switching to a GUID primary key instead of auto increment for Location. I need to add this foreign key to Employee. So Employee.LocationGUID needs to equal Location.GUID.

I'm just baffled I can't come up with an update query that works.

von Curtis - am 23.06.2017 16:21
Hello Curtis

The path of least resistance to get the job done is to construct a filtered loop using hCommands to step through the data and hModify() to make the changes.
This is especially relevant as it seems to be a one off requirement so it doesn't have to be pretty just functional :)

Regards
Al

von Al - am 23.06.2017 23:13
That would definitely work. It is disturbing that HFSQL can't run an update command with 2 tables like normal SQL.

von Curtis - am 26.06.2017 17:03
Hi Curtis,

I use this sort of queries daily against MS SQLServer mostly and I'm also flabbergasted that HyperFileSQL apparently can't do an update to a file that is part of a join...

Although the name of HyperFile has been changed into HyperFileSQL it still is not fully SQL standard compliant I'm afraid.
It works great with its own embedded h-commands and query editor but lacks anything that surpasses the most ordinary SQL statement.

As a side note: I remember working with Progress 4GL in the nineties for many many years (aside of VB and pascal at that time)...
God, how I loved their mega intuitive query language (e.g.: For each customer where name contains 'Peter', each order of customer, each orderline of order)... But even how intuitive, lightning fast, easy to develop it was, they moved over to SQL completely during the first half of the new decade (although there is of course still backward compatibility for the die hard fans but against a Progress db only) because standardisation and adapting to it is the only way forward to 'progress'.

And they did the same with their front-end graphical development layer which is completely in .Net nowadays called Telerik (and Kendo UI for the full html/javascript clients).

It's an amazing example of how a company can re-invent itself completely within a decade...

The SQL support layer for HF is according to me developed way too slow by PCS.
Just a few additional SQL commands with each version... It's at the pace of a snail...

Cheers,

Peter Holemans

von Peter Holemans - am 26.06.2017 21:07
Hi Peter,

Quote
Peter
I remember working with Progress 4GL in the nineties for many many years


Quote
Peter
God, how I loved their mega intuitive query language


So did I, was using the MS-DOS version of progress, but when Progress 4GL came for windows, I felt progress became slow & sluggish and made my switch to another product that impressed me with their windows version, at that time Clarion 2 for windows.

And since CW did not require a quite an high run-time fee (as progress did) the switch was easy. :)

Cheers
Tor-Bjarne

von Tor-Bjarne - am 29.06.2017 09:33
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.