Filtering a Many-to-Many browse with PROP:SQL issue

[rhorn]

I have this SQL code that works perfect in SQL Management Studio:

select * from orders
join isassigned on (Orders.Orders_SysID = isassigned.ORDERS_ID)
join Assignment_ on (isassigned.ASSIGNMENT_ID = Assignment_.Assignment_SysID)
where Assignment_.AssignedTo = ‘Rick’

I place it into this embed point: Reset Procedure(BYTE Locate), Virtual as:

SELF.Open()

SELF.View{ PROP:SQL } = '(select o.catalognumber, o.compound_name, o.purchaseordernumber, ’ & |
'o.customercode, o.institution, o.datepromised, o.group_, o.quotenumber, o.attention, ’ & |
'o.invoicenumber, o.dateshipped from orders o ’ & |
'join isassigned on (o.Orders_SysID = isassigned.ORDERS_ID) ’ & |
'join Assignment_ on (isassigned.ASSIGNMENT_ID = Assignment_.Assignment_SysID) ’ & |
‘where Assignment_.AssignedTo = ‘‘Rick’’)’

IF ERRORCODE()
SELF.Primary.Me.Throw(Msg:ViewOpenFailed)
END

RETURN

PARENT.Reset!(Locate)

The format of this code is from this thread:

temp_REGIONS{PROP:SQL}= 'Select * from

regionslookup{PROP:SQL}= ‘CREATE TEMPORARY TABLE IF NOT EXISTS temp_REGIONS LIKE REGIONS’ This does create the table, I can see it in Navicat . The next line causes compile error Unknown identifier: TEMP_REGIONS temp_REGIONS{PROP:SQL}= ‘Select * from regions where Printable=1 order by sortorder’ Solution?

I have ordered the fields in the SQL syntax according to the order in the database.

I’m still getting one error: “Error: ( (01S01)) attempting to access a record from the dbo.Orders file. Returning to previous window.” Followed by: “Error ( (0) attempting to open curent VIEW. Filter and Range Limits ignored”. Followed by the same error as the first, then closes the browse.

I’m still searching, but any help would be appreciated.

Thanks.

[seanh]

I’d use Clarion driver trace to look at what’s actually sent. It usually helps me.

[francisco_viviers]

@rhorn

‘where Assignment_.AssignedTo = ‘‘Rick’’)’

Are you using simple quote o double?

[rhorn]

I’m using single quotes.

[rhorn]

I did a quick look for this tool. Are you referring to the one in the driver settings within the dictionary? or is there a separate app?

[seanh]

There’s a tool that sets info in an INI file, or you can set properties on an individual file.
So a couple of different methids.
See: http://clarion.help/doku.php?id=driver_trace.htm

[totalhip]

Okay, going back to basics. From the snippet posted, it seems you are using ABC templates.
Unless the SQL is extremely simplified for what you actually want, it looks to me as if this query can easily be achieved using a standard ABC browse. Why complicate with PROP:Sql?

[rhorn]

Thanks for the info. I’ve attached the trace file generated.Shipping Feb 24 2021 - 0001 - Notepad.pdf (144.4 KB)

[rhorn]

I am using an ABC browse. I have tried to achieve this using the templates, but have been unable to get it to give the desired output. The result set I’m looking for is to filter the browse so that only projects assigned to the logged in user show up. There are two tabs, one showing current orders and the other is order history, so I have conditional filtering on the tabs also. There are over 100,000 records and it seems to run a bit faster when I use SQL:prop for other queries. There are also other filter options on the browse from radio button choices. I honestly have not been very successful getting many-to-many relationships to work properly in my applications. I’ve read many articles on the subject, most of them in Dave Harms, Tips and Tricks books. Anyway, any articles that would help to explain the implementation of using many-to-many relationships would be appreciated, along with explanations of embed points.

[totalhip]

I have exactly the same requirement in my Apps. By far, the easiest solution (in code and especially maintenance) is to create an SQL view in the backend and define that view as a table in Clarion. This is especially true if you need to show summary totals in your browse.

My suggestion would be to work up a quick browse test after creating one example SQL view and that view as one table in the DCT.

[rhorn]

Ok. I’ve never created a view in the backend, but I will give it a go and try the test. I’m always interested in learning new things. Thanks!

[totalhip]

Careful, you might become addicted. It will open a whole new world as to what you can provide using Clarion.

[seanh]

I agree a VIEW on the backend can be a wonderful thing. Clarion treats them the same as a normal table.

On the trace you provided (Which I haven’t been too far into) I Noticed this line

08908H(1)05:30:27.980ErrorOccurred:28000[Microsoft][SQLServerNativeClient11.0][SQLServer]Loginfailedforuser’'.

You might want to investigate

[JonW]

For this purpose, from the link that Sean posted, you would be much better off doing on-demand logging. So just before your PROP:SQL you should have:

yoursqlfile{PROP:Profile} = ‘somefilename’
yoursqlfile{PROP:Details} = true
yoursqlfile{PROP:LogSQL} = true

you can turn it off afterwards

yoursqlfile{PROP:Profile} = ‘’

That way you would have a one page log file to look at rather than an 88 page log file.

You have a lot of “File not open” errors in your log. Like Sean says, you need to check those out.

Also, the query that is being sent to the backend is not the one that you started off the thread with. Rather than looking for items that belong to Rick, you seem to be using an outer join to find things that are not assigned to anyone. It is possible that the ( IsAs.ORDERS_ID = 0 ) restriction is causing you a problem. Maybe it should be IS NULL? Clarion often treats NULLS as zero and vice versa but SQL is fussier.

[rhorn]

Thank you everyone for the replies.

I finally have a functioning test browse using a view, very cool. I’m still using a string, “Rick” in the View filter on the backend though. Is there a way to send a variable to the View? Can it only be done using stored procedures?

Thanks again for all of the feedback. I still need to look into the “File not open” errors too.

[totalhip]

@rhorn

Is there a way to send a variable to the View?

Might you explain further?

[totalhip]

@rhorn

I’m still using a string, “Rick” in the View filter on the backend though. Is there a way to send a variable to the View?

Okay, from reading back a little further, it seems you want to filter by the user to whom the projects are assigned. As long as this is just another column in the view, you simply include the user condition in your Clarion browse. If your expected return of data is still a very large set of records (especially everyday use), add other conditions that can easily be specified (for instance, by year).

[rahorn]

The user name and projects are in a many to many relationship. I’m trying filter the user view based on their windows login name so they only see the projects their assigned to. I can obtain the username, but am having trouble sending it to the SQL View. I need a variable in the View filter that received the users login name. Does that help to clarify what I’m trying to do and\or thinking of the solution correctly?
Thanks

[totalhip]

The username simply needs to be included in the SQL View:
SELECT
p.projectid,
p.project,
u.usernameid,
u.username
FROM
project p
JOIN assigned a ON a.projectid = p.projectid
JOIN username u ON u.usernameid = p.projectid
WHERE
u.username = ‘Rick’

[rhorn]

I don’t know if I’m missing something, but it’s still not clicking for me. I need a variable where ‘Rick’ is, in the line u.username = ‘Rick’. Otherwise other people will not be able to see what is assigned to them. They’ll only be able to see what’s assigned to ‘Rick’. I apologize if I’m making this more difficult than it needs to be.
Thanks again.