I am currently working on migrating an application from a ASP/Microsoft SQL DB on Win2k,
into a PHP/Postgres 7.4.2 on Linux environment.
My experience with Postgres is not extensive, and I have not been able to translate the following MSSQL Query
into the equivalent Postgres form. The essence of the query is as follows:
Find "ProdCat" (return only one (1) row = longest match),
from a subset of rows ("C") of table ("B") for the longest string match
at string position 1 (beginning of string) for "ProdPattern" given a specific "ProductID" (A.ProdID)
Notes: A.ProductID is the result of a subquery
B.Catalogs is the result of a subquery
C.ProdPattern = C.ProdCat + '%'
Examples: C.ProdPattern = '582%', A.ProdId = '582125678765','583452430987', D.Catalogs = '12354'
Expected Result:
Original MSSQL Query:
select top 1 C.ProdCat from (select * from B where B.CatalogID = D.Catalogs) as C
where patindex(c.ProdPattern,A.ProdID) = 1
order by C.ProdCat desc
Appreciating any help in advance,