{"action":"created","issue":{"url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/107","repository_url":"https://api.github.com/repos/dalibo/sqlserver2pgsql","labels_url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/107/labels{/name}","comments_url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/107/comments","events_url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/107/events","html_url":"https://github.com/dalibo/sqlserver2pgsql/issues/107","id":467953588,"node_id":"MDU6SXNzdWU0Njc5NTM1ODg=","number":107,"title":"unsure.sql script need many correction","user":{"login":"manishdwivedi02","id":52192402,"node_id":"MDQ6VXNlcjUyMTkyNDAy","avatar_url":"https://avatars2.githubusercontent.com/u/52192402?v=4","gravatar_id":"","url":"https://api.github.com/users/manishdwivedi02","html_url":"https://github.com/manishdwivedi02","followers_url":"https://api.github.com/users/manishdwivedi02/followers","following_url":"https://api.github.com/users/manishdwivedi02/following{/other_user}","gists_url":"https://api.github.com/users/manishdwivedi02/gists{/gist_id}","starred_url":"https://api.github.com/users/manishdwivedi02/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/manishdwivedi02/subscriptions","organizations_url":"https://api.github.com/users/manishdwivedi02/orgs","repos_url":"https://api.github.com/users/manishdwivedi02/repos","events_url":"https://api.github.com/users/manishdwivedi02/events{/privacy}","received_events_url":"https://api.github.com/users/manishdwivedi02/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2019-07-15T05:59:51Z","updated_at":"2019-07-18T13:49:54Z","closed_at":null,"author_association":"NONE","body":"In my case I can see queries in unsure.sql (mostly view in my case) are almost same as it is SQL Dump file. Due to this unsure.sql script has many issues and giving error. Do I need to validate each and every query in this script and correct it manually. \r\n\r\nSample SQL Dump file  is attached \"sql_server_dump_TestDB.zip\". After converting this, I had to find and replace following 4 strings, then unsure.sql could work. This is just a sample dump SQL file\r\n\r\nString: b1.C1 +  '|'  + b2.C1\r\nReplaced With: concat(concat(b1.C1, '|'), b2.C1) AS C1\r\n\r\nString: b1.C1 +  '|'  + ISNULL(b2.C1, '')\r\nReplaced With: concat(concat(b1.c1, '|'), COALESCE(b2.c1::text, ''::text)) AS c1\r\n\r\nString: CAST(T97531Id AS VARCHAR(256))+ '-' +C1\r\nReplaced With: concat(concat(T97531.T97531Id, '-'), T97531.C1) AS c1\r\n\r\nString: convert(NVARCHAR(15),CO11011) + ';' + convert(NVARCHAR(15),CC11011) + ';' + C11011\r\nReplaced With: concat(concat(concat(concat(\"substring\"(B4102.co11011::text, 1, 15), ';'), \"substring\"(B4102.cc11011::text, 1, 15)), ';'), B4102.c11011)\r\n\r\n[sql_server_dump_TestDB.zip](https://github.com/dalibo/sqlserver2pgsql/files/3390973/sql_server_dump_TestDB.zip)\r\n"},"comment":{"url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/comments/512824024","html_url":"https://github.com/dalibo/sqlserver2pgsql/issues/107#issuecomment-512824024","issue_url":"https://api.github.com/repos/dalibo/sqlserver2pgsql/issues/107","id":512824024,"node_id":"MDEyOklzc3VlQ29tbWVudDUxMjgyNDAyNA==","user":{"login":"manishdwivedi02","id":52192402,"node_id":"MDQ6VXNlcjUyMTkyNDAy","avatar_url":"https://avatars2.githubusercontent.com/u/52192402?v=4","gravatar_id":"","url":"https://api.github.com/users/manishdwivedi02","html_url":"https://github.com/manishdwivedi02","followers_url":"https://api.github.com/users/manishdwivedi02/followers","following_url":"https://api.github.com/users/manishdwivedi02/following{/other_user}","gists_url":"https://api.github.com/users/manishdwivedi02/gists{/gist_id}","starred_url":"https://api.github.com/users/manishdwivedi02/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/manishdwivedi02/subscriptions","organizations_url":"https://api.github.com/users/manishdwivedi02/orgs","repos_url":"https://api.github.com/users/manishdwivedi02/repos","events_url":"https://api.github.com/users/manishdwivedi02/events{/privacy}","received_events_url":"https://api.github.com/users/manishdwivedi02/received_events","type":"User","site_admin":false},"created_at":"2019-07-18T13:49:54Z","updated_at":"2019-07-18T13:49:54Z","author_association":"NONE","body":"Many thanks for the fix. This had worked for sample SQL dump which I had provided. I tried on actual SQL dump, many errors are fixed but it could not work completely for that dump. First error which I got is given below\r\n\r\n**From SQL Dump -** b1.C1 +  '|'  + CAST(ISNULL(b2.C1, '') AS VARCHAR(256))\r\n**From iteration #108 Fix -** b1.C1||'|'||CAST(COALESCE(b2.C1, '') AS VARCHAR(256))\r\n**Expected (from other PostgreSQL DB) -** concat(concat(b1.c1, '|'), COALESCE(b2.c1::text, ''::text)) AS c1\r\n\r\nAfter correcting few errors manually, getting other errors. So I need to look into more for various new errors. Out of new error, two errors are given below.\r\n\r\n**Error1** - \"(b1.C1 + N'|') + b2\" - I think for this kind of aggregation + should be replaced with '||' and char N should be removed. Not sure why char  N had been given in SQL\r\n\r\n**Error2** - \"AND ISNULL(T4501.C1000000082,N'NA') NOT IN (SELECT ISNULL(C1000000089,N'NA') FROM T4501\" - I think should be converted to \"AND COALESCE(t4501.c1000000082::text, 'NA'::text) NOT IN (SELECT COALESCE(c1000000089::text, 'NA'::text) FROM T4501\r\n"}}
{"actor":{"display_login":"manishdwivedi02"}}