Hi All,
For a project, I need to get the difference between current depth and the next depth in SQL. I've tried the following code at it fails. I also tried SQL LEAD and it seems it's not supported by gINT. Any idea how to get both current and next depth in SQL? Thanks.
<<Sql(_ SELECT ([SAMPLE].[Depth]) _ From [SAMPLE] _ Where [SAMPLE].[PointID] = '<<PointID>>' _AND NEXTDEPTH ([SAMPLE].[DEPTH]) - [SAMPLE].[DEPTH] < 2 _)>>
NextDepth is a gINT function, so it needs to be in the syntax <<NextDepth(<<SAMPLE.Depth>>)>>
Phil WadeDatgelBentley Channel Partner and Developer PartnerE: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780
Get the most out of gINT with Datgel Tools.
Using gINT syntax (<<>>) makes it a constant inside sql. Is there a way I to get it as a variable function?
For example,
I've the following depths in a column = (1, 3, 4, 6, 7, 10)
The output should be (difference between the adjacent depths) = (2, 1, 2, 1, 3)
Thanks
The following seems to work. It's using a lot of functions though.
<<LET(LISTKEY = <<SQLLIST(",", _ Select ([SAMPLE].[DEPTH]) _ From [SAMPLE] _ Where ([SAMPLE].[PointID] = '<<PointID>>')_ AND NOT [SAMPLE].[DEPTH] IS NULL _)>>)>>_<<LET(NUM = <<CALC(<<LISTCOUNT(<<Get(LISTKEY)>>,",")>> - 1_)>>)>>_<<Iterate(INDEX,1,<<Get(NUM)>>,,",",_<<LET(IND2 = _<<CALC(<<Get(INDEX)>> + 1)>>_)>>_<<CALC(_<<GetListItem(<<Get(LISTKEY)>>,",",<<Get(IND2)>>)>> - _<<GetListItem(<<Get(LISTKEY)>>,",",<<Get(INDEX)>>)>>_)>>)>>
Answer Verified By: Ratna Yesuraj