GUID and SQL Views

[Ubaidullah]

Hi Bruce,

I have started using GUIDs in all my tables and find the concept to be very useful especially when needing a "unique non-changing key".

I have a question about SQL views. I have NetWebBrowse procs on SQL views and they consist of aggregate data and hence there is no GUID available. For example a view defined as follows:

CREATE VIEW vwCustomerItemSales as
SELECT cust.GUID, item.GUID, min(cust.CustomerName), min(item.ItemSKU), min(item.Description), sum(inv.Qty), sum(inv.Amount)
FROM Customers as cust join Invoices as inv on inv.CustomerGUID=cust.GUID
                       join Items as item on inv.ItemGUID=item.GUID
GROUP BY cust.GUID, item.GUID


for tables Customers, Invoices and Items defined as below:

Customers:
GUID            varchar(16),
CustomerName    varchar(100)

Invoices:
GUID      varchar(16),
Qty       decimal(10,2),
Amount    decimal(10,2)

Items:
GUID           varchar(16),
ItemSKU        varchar(20),
Description    varchar(100)


What should I use for the unique key in this case?
I was thinking of combining cust.GUID and item.GUID which will give a STRING of length 32 characters.
But in some cases, there could be three GUIDs.

Appreciate your input on this.

Thanks
&
Regards,
Ubaidullah Nubar.

[Bruce]

I'm definitely not the SQL expert, but sure, combining the guids sounds like a reasonable idea.

This doesn't sound like a "guid" specific question though. What would you do in a case like this where the rows were just auto-numbered? If anything that sounds like an even harder problem. If anything it sounds simpler with combining guids - it's at least easier to do that than combining auto-numbered instances...

So perhaps this is more of a SQL-Server side view question? And if so you might have more joy asking on a SQL orientated Clarion forum or skype chat.

Oh - and I don't _think_ the Unique Key in NetTalk has to be a single component key. So your dict definition of this view could just create a primary key including all the various guids. I'm _reasonably_ sure that would be ok, although it's been ages and ages since I played with multi-component-primary keys.

cheers
Bruce

[Ubaidullah]

Thanks for the reply, Bruce.

Agree that this is more of an SQL question. But since I took the idea of using GUIDs from you, I wanted to ask you to see what you think of this.

Regarding multi-component keys, I remember sometime in the past, I faced some issues due to using Primary Keys with multiple fields but not sure exactly what it was.

So, I could make a composite GUID field which could be 32 or 48 characters in length. Although this would take up quite some space, it would work.

Regards,
Ubaidullah Nubar.

[Sibuya]

Just for information on SQL composite keys. I had to add GUID unique key to the last component of the composite key turning into a unique key to avoid browser showing duplicated entries and other issues. So I had to create all keys as unique.

Regards,

Marcos

[Bruce]

>> Regarding multi-component keys, I remember sometime in the past, I faced some issues due to using Primary Keys with multiple fields but not sure exactly what it was.

yes, there were limitations in the past, but as with most things limitations can be lifted.
I think multi-component are ok in most places now (with the possible exception of lookups)

cheers
Bruce