Error - Invalid length parameter passed to the LEFT or SUBSTRING function
1
I'm not a developer but I'm here to find help with great developers . I do very basic SQL but complex ones I've got no clue as below.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1,PATINDEX('% %',res_ad1)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1,CHARINDEX(' ',res_ad1),charindex(' ',res_ad1,charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
This query was working fine initially, but recently when triggered it was giving out an Error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
I've got no idea where to start looking. Hopefully someone can assist.
If anyone has an idea, you may need to copy and paste your solutions in the comments for me to try it out on my DB so see if that works. I'm running this is MSSQL using SQL Studio.
sql
sql-server tsql edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
add a comment |
1
I'm not a developer but I'm here to find help with great developers . I do very basic SQL but complex ones I've got no clue as below.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1,PATINDEX('% %',res_ad1)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1,CHARINDEX(' ',res_ad1),charindex(' ',res_ad1,charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
This query was working fine initially, but recently when triggered it was giving out an Error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
I've got no idea where to start looking. Hopefully someone can assist.
If anyone has an idea, you may need to copy and paste your solutions in the comments for me to try it out on my DB so see if that works. I'm running this is MSSQL using SQL Studio.
sql
sql-server tsql edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
Try with replacing it withREVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).
– Karan
Nov 21 '18 at 4:48
add a comment |
1
1
1
I'm not a developer but I'm here to find help with great developers . I do very basic SQL but complex ones I've got no clue as below.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1,PATINDEX('% %',res_ad1)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1,CHARINDEX(' ',res_ad1),charindex(' ',res_ad1,charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
This query was working fine initially, but recently when triggered it was giving out an Error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
I've got no idea where to start looking. Hopefully someone can assist.
If anyone has an idea, you may need to copy and paste your solutions in the comments for me to try it out on my DB so see if that works. I'm running this is MSSQL using SQL Studio.
sql
sql-server tsql edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
I'm not a developer but I'm here to find help with great developers . I do very basic SQL but complex ones I've got no clue as below.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1,PATINDEX('% %',res_ad1)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1,CHARINDEX(' ',res_ad1),charindex(' ',res_ad1,charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
This query was working fine initially, but recently when triggered it was giving out an Error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
I've got no idea where to start looking. Hopefully someone can assist.
If anyone has an idea, you may need to copy and paste your solutions in the comments for me to try it out on my DB so see if that works. I'm running this is MSSQL using SQL Studio.
sql
sql-server tsql sql
sql-server tsql edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
edited Nov 20 '18 at 9:59
Rahul Neekhra
6001627
6001627
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
asked Nov 20 '18 at 8:54
miltonyeemiltonyee
63
63
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
Try with replacing it withREVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).
– Karan
Nov 21 '18 at 4:48
add a comment |
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
Try with replacing it withREVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).
– Karan
Nov 21 '18 at 4:48
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
Try with replacing it with
REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).– Karan
Nov 21 '18 at 4:48
Try with replacing it with
REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).– Karan
Nov 21 '18 at 4:48
add a comment |
1 Answer
1
active
oldest
votes
1
Update LEFT & SUBSTRING functions in your query. Try like below. It seems that second parameter value in LEFT or third parameter value in SUBSTRING is negative value.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1, IIF(PATINDEX('% %',res_ad1) > 0, PATINDEX('% %',res_ad1), 0)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1, CHARINDEX(' ',res_ad1), IIF(charindex(' ',res_ad1, charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1) > 0, charindex(' ',res_ad1, charindex('',res_ad1)+1) - CHARINDEX(' ',res_ad1), 0)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389333%2ferror-invalid-length-parameter-passed-to-the-left-or-substring-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Update LEFT & SUBSTRING functions in your query. Try like below. It seems that second parameter value in LEFT or third parameter value in SUBSTRING is negative value.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1, IIF(PATINDEX('% %',res_ad1) > 0, PATINDEX('% %',res_ad1), 0)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1, CHARINDEX(' ',res_ad1), IIF(charindex(' ',res_ad1, charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1) > 0, charindex(' ',res_ad1, charindex('',res_ad1)+1) - CHARINDEX(' ',res_ad1), 0)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
add a comment |
1
Update LEFT & SUBSTRING functions in your query. Try like below. It seems that second parameter value in LEFT or third parameter value in SUBSTRING is negative value.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1, IIF(PATINDEX('% %',res_ad1) > 0, PATINDEX('% %',res_ad1), 0)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1, CHARINDEX(' ',res_ad1), IIF(charindex(' ',res_ad1, charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1) > 0, charindex(' ',res_ad1, charindex('',res_ad1)+1) - CHARINDEX(' ',res_ad1), 0)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
add a comment |
1
1
1
Update LEFT & SUBSTRING functions in your query. Try like below. It seems that second parameter value in LEFT or third parameter value in SUBSTRING is negative value.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1, IIF(PATINDEX('% %',res_ad1) > 0, PATINDEX('% %',res_ad1), 0)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1, CHARINDEX(' ',res_ad1), IIF(charindex(' ',res_ad1, charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1) > 0, charindex(' ',res_ad1, charindex('',res_ad1)+1) - CHARINDEX(' ',res_ad1), 0)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
Update LEFT & SUBSTRING functions in your query. Try like below. It seems that second parameter value in LEFT or third parameter value in SUBSTRING is negative value.
SELECT aualdocs.fmt_acc, aualdocs.ddn_cd1, dbo.aualrefn.dsc_no1, aunrmast.nam_fam, RTRIM(aunrmast.nam_gv1) + ' ' + RTRIM(COALESCE (aunrmast.nam_gv2, ''))
AS nam_gvn, aunrmast.dte_brt, aunrmast.sex, aunrmast.nam_ttl,
CASE WHEN res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %' THEN res_ad1 END AS pst_nme,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN LEFT(res_ad1, IIF(PATINDEX('% %',res_ad1) > 0, PATINDEX('% %',res_ad1), 0)) END as pst_num,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN SUBSTRING(res_ad1, CHARINDEX(' ',res_ad1), IIF(charindex(' ',res_ad1, charindex(' ',res_ad1)+1) - CHARINDEX(' ',res_ad1) > 0, charindex(' ',res_ad1, charindex('',res_ad1)+1) - CHARINDEX(' ',res_ad1), 0)) END AS pst_str,
CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))) END AS pst_typ,
REPLACE(res_ad2,aunrmast.pst_cde,'') AS pst_loc,
aunrmast.pst_cde, auprstad.bld_nme, auprstad.pcl_unt, auprstad.unt_alp, auprstad.hou_num, auprstad.hou_alp, auprstad.hou_end, auprstad.end_alp, ausrmast.str_nme,
ausrmast.str_typ, RTRIM(ausrsubr.sbr_nme) + ' ' + ausrsubr.sta_nme AS sub_sta, ausrsubr.pst_cde AS Expr1, aualdocs.ddf_cd2, aualdocs.dda_cd2, auallnks.lnk_typ,
aunrlink.nar_num, auprlink.pcl_num, aualdocs.det_dte, aualdocs.dte_no2, aualdocs.ddn_cd2
FROM dbo.aualdocs AS aualdocs INNER JOIN
dbo.auallnks AS auallnks ON aualdocs.fmt_acc = auallnks.src_acc INNER JOIN
dbo.aunrlink AS aunrlink ON auallnks.src_acc = aunrlink.mdu_fmt INNER JOIN
dbo.aunrmast AS aunrmast ON aunrlink.nar_num = aunrmast.nar_num INNER JOIN
dbo.auprlink AS auprlink ON aualdocs.fmt_acc = auprlink.mdu_fmt INNER JOIN
dbo.auprstad AS auprstad ON auprlink.pcl_num = auprstad.pcl_num INNER JOIN
dbo.ausrmast AS ausrmast ON auprstad.str_num = ausrmast.str_num AND auprstad.str_blk = ausrmast.str_blk INNER JOIN
dbo.ausrsubr AS ausrsubr ON ausrmast.sbr_num = ausrsubr.sbr_num INNER JOIN
dbo.aualrefn ON aualdocs.ddf_cd1 = dbo.aualrefn.ref_val
WHERE (aualdocs.doc_typ = 90) AND (auallnks.lnk_typ = 151) AND (aualdocs.det_cde = 'APPR') AND (dbo.aualrefn.ref_typ = 'wrd_num')
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
edited Nov 21 '18 at 4:49
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
answered Nov 20 '18 at 9:58
KaranKaran
3,3522424
3,3522424
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
add a comment |
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Hi Karan, thanks for the assist it's coming up with another issue saying incorrect syntax near '>'.
– miltonyee
Nov 21 '18 at 0:04
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
Updated my answer. Please check now.
– Karan
Nov 21 '18 at 4:54
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389333%2ferror-invalid-length-parameter-passed-to-the-left-or-substring-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Minimize... Which row is the problem row, and what data causes it.
– jarlh
Nov 20 '18 at 8:54
Do a binary search! Remove half of the query, did the error disappear or not? Loop.
– jarlh
Nov 20 '18 at 8:57
Who wrote this code? You or someone else? If someone else, why don't you ask "someone else"? There are obvious assumptions on which the code is based. You will either need to correct those assumptions or correct the data. Pick a path first. Are there rows where res_ad1 does not have any spaces?
– SMor
Nov 20 '18 at 13:16
I tried to troubleshoot by removing the top pars of the query and putting in one by one. Found out this section is the issue which is causing the Error. Any idea how to fix this? CASE WHEN NOT(res_ad1 LIKE 'C/%' OR res_ad1 LIKE 'PO %') THEN REVERSE(LEFT(REVERSE(RTRIM(res_ad1)),PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1)) END AS pst_typ,
– miltonyee
Nov 21 '18 at 1:55
Try with replacing it with
REVERSE(LEFT(REVERSE(RTRIM(res_ad1)), IIF((PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1) > 0, (PATINDEX('% %',REVERSE(RTRIM(res_ad1)))-1), 0))).– Karan
Nov 21 '18 at 4:48