Searching lookup items on a view list

[Manos Pappas]

Hello,

I think that the search function on the item view form might not work as I thought it would, when the fields being searched are lookup items. Let me give you a real example.

I have an item called users with the definition, as shown in the users.jpg attachment.

There is also another item called regs with the following definition (regs.jpg attachment).

Suppose we want to search the field regs.afm in the regs table view, which is in fact a lookup field to table users.afm (which is TEXT). When I use the search field in the view for the field regs.afm, the search field treats the field as INTEGER instead of TEXT which is the lookup field I want. The same happens also on e.g regs.srvusername which points to the user.name field which is again TEXT (this time an error appears on the view that it cannot convert NVARCHAR to int). In my case, I would like these fields to be treated on search as TEXT, instead of INTEGER.

How can I perform the search the way I would like?

Many thanks in advance.

Best regards,

Manos Pappas

[Dean D. Babic]

Hi, 

it might be related to a DB though. Which DB u using?

[Manos Pappas]

Hi,

I am using SQL Server 2019.

[Dean D. Babic]

Yep, searching a lookup:

ERROR - (8114, b'Error converting data type nvarchar to numeric.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')
Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 447, in pymssql._pymssql.Cursor.execute
  File "src/pymssql/_mssql.pyx", line 1125, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1157, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1379, in pymssql._mssql.MSSQLConnection.get_result
  File "src/pymssql/_mssql.pyx", line 1854, in pymssql._mssql.check_cancel_and_raise
  File "src/pymssql/_mssql.pyx", line 1881, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
  File "src/pymssql/_mssql.pyx", line 1898, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (8114, b'Error converting data type nvarchar to numeric.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
.

.

[Dean D. Babic]

This is something inside MSSQL. If I create a script sq.sql, with:

use jam;

go
select b.id, b.deleted, b.customer, b.invoice_date, b.subtotal, b.taxrate, b.tax, b.total, customer_lookup, firstname_lookup, billing_address_lookup, billing_city_lookup, billing_country_lookup, billing_postal_code_lookup, billing_state_lookup from (select demo_invoices.id, demo_invoices.deleted, demo_invoices.customer, demo_invoices.invoice_date, demo_invoices.subtotal, demo_invoices.taxrate, demo_invoices.tax, demo_invoices.total, demo_customers_34.lastname as customer_lookup, demo_customers_34.firstname as firstname_lookup, demo_customers_34.address as billing_address_lookup, demo_customers_34.city as billing_city_lookup, demo_customers_34.country as billing_country_lookup, demo_customers_34.postalcode as billing_postal_code_lookup, demo_customers_34.state as billing_state_lookup, row_number() over ( order by demo_invoices.invoice_date desc) as rownum from demo_invoices as demo_invoices left outer join demo_customers as demo_customers_34 on demo_invoices.customer = demo_customers_34.id where cast(cast(demo_customers_34.lastname as decimal(20, 10)) as varchar(20)) like '%leo%' and demo_invoices.deleted=0) as b where rownum >= 1 and rownum < 4 order by rownum;
go

and run it:
sqlcmd -S localhost -U sa -P 'password' -i sq.sql
Changed database context to 'jam'.
Msg 8114, Level 16, State 5, Server dbabic-VirtualBox, Line 1

Error converting data type nvarchar to numeric.

[Dean D. Babic]

The problem is a CAST. This simple SQL does not work (from Tracks, search "bla" for Album):

SELECT b."ID" FROM (SELECT "DEMO_TRACKS"."ID" FROM "DEMO_TRACKS" AS "DEMO_TRACKS" LEFT OUTER JOIN "DEMO_ALBUMS" AS DEMO_ALBUMS_27 ON "DEMO_TRACKS"."ALBUM" = DEMO_ALBUMS_27."ID" LEFT OUTER JOIN "DEMO_GENRES" AS DEMO_GENRES_29 ON "DEMO_TRACKS"."GENRE" = DEMO_GENRES_29."ID" LEFT OUTER JOIN "DEMO_ARTISTS" AS DEMO_ALBUMS_27_ARTIST ON DEMO_ALBUMS_27."ARTIST" = DEMO_ALBUMS_27_ARTIST."ID" WHERE CAST(CAST(DEMO_ALBUMS_27."TITLE" AS DECIMAL(20, 10)) AS VARCHAR(20)) LIKE '%bla%' AND "DEMO_TRACKS"."DELETED"=0) AS b

This is a scaled down sql which is presented in a Traceback. Removing CAST, and sql will work:

7:43:16Started executing query at Line 1
(48 rows affected)
Total execution time: 00:00:00.057

Btw, I'm using azuredatastudio-linux-1.48.0. 

I think the problem is somewhere in mssql.py and mssql_db.py for v7.

Tried my best to debug this...

[Manos Pappas]

Hello,

Sorry for not replying earlier, I am still trying to recover from a recent illness.

Your reasoning Drazen is correct, the produced SQL query is not correct, there is no need for CAST.

[Danijel Kaurin]

Hi.

V7 had a similar problem with Postgres and it was solved. Try to see commits for V7 and changes on db.py files.

Regards

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/f11feac8-5a1a-4791-b861-876a44d12376n%40googlegroups.com.