-2

I am doing a migration from mssql to postgresql and I am facing issue in resolving one of the queries.

My Query:

SELECT 1, 
       CASE 
          WHEN cast((case 
                       when split_part(mt_outward_qty,'/',1) > '0' then  
                         substring(mt_outward_qty,0,split_part(mt_outward_qty,'/',1)::int) 
                       when split_part(mt_outward_qty,'.',1)>'0' then  
                         substring(mt_outward_qty,0,split_part(mt_outward_qty,'.',1)::int) 
                      else  
                       mt_outward_qty 
                   end) as int) > 0  
             THEN 'Y' 
             ELSE 'N' 
        END 
from STL_CS_Tra_requestdetails 
  LEFT JOIN STL_CS_Tra_storeopening ON req_id =  so_requestid 
  LEFT JOIN STL_CS_Tra_multiple_timinig ON req_id = mt_reqid

Upon executing this, I am getting:

invalid input syntax for integer: ""

Kindly help me in resolving this.

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Rlaks
  • 7
  • 1
  • 5
  • Look at this other question, maybe it will help you: [PG COPY error: invalid input syntax for integer](https://stackoverflow.com/questions/18297980/pg-copy-error-invalid-input-syntax-for-integer) – Thierry P. Oliveira Apr 09 '21 at 12:32
  • You need to convert empty strings to a `null` value, e.g. using `nullif(.., '')` –  Apr 09 '21 at 12:45
  • 1
    It would be easier to help you, if you can post some sample data of that column `mt_outward_qty` (which seems to violate the most basic principles of good database design to begin with) –  Apr 09 '21 at 12:53
  • What does mssql do when you cast an empty string (produced by your `case` expression) `as int`, what is the expected behavior when `mt_outward_qty` is empty? – Bergi Apr 09 '21 at 13:09

1 Answers1

0

sql server automatically cast empty string to 0 when you cast to int

you can do it in postgresql like so :

( 0 || '')::int
eshirvana
  • 23,227
  • 3
  • 22
  • 38