Community Forum     Helping YOU do what YOU want with YOUR data.
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Ashley  
#1 Posted : Tuesday, March 10, 2020 12:55:35 PM(UTC)
Ashley
Rank: Member

Reputation:

Groups: Registered
Posts: 14

Thanks: 1 times

Hi Guys,

I am try to edit the GEDTS files from ESdat to ESLog in order to get "N/A" to show is no coordinates are entered in.

Is there an IF statement that I can add to the below to show this?

**HeaderInfo: Locations x_coord:        Locations.x_coord y_coord:        Locations.y_coord Elevation:        Locations.Elevation

Regadrs

Ashley 

Warwick Wood  
#2 Posted : Tuesday, March 10, 2020 6:17:17 PM(UTC)
Warwick Wood
Rank: Administration

Reputation:

Groups: Registered, Administrators
Posts: 503
Man
Location: Byron Bay

Was thanked: 19 time(s) in 19 post(s)

Hi Ashley,

You can use this syntax.

CASE WHEN ____ THEN _____  ELSE ____ END.

Replace the underscores with your logic, for example

CASE WHEN x_coord is null THEN 'NA'  ELSE x_coord END   

This syntax is already used for other logic in the gedts file, which will also demonstrate it's use.

Another way of doing it (not already demonstrated in the gedts file) is to use the ISNULL function, which returns the second parameter if the first is null, for example.

ISNULL(x_coord,'NA').

Regards,

Warwick

Ashley  
#3 Posted : Thursday, March 12, 2020 5:04:33 PM(UTC)
Ashley
Rank: Member

Reputation:

Groups: Registered
Posts: 14

Thanks: 1 times
Hi Warwick, i thought that might have been your answer. So after writing that code I get an Error Message saying: ITHI: HeaderInfo [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to float. Is there something that I could code to fix this? I have noticed you have code in there for Termination_Depth_Comment: 'Termination Depth at:' + cast([total_depth] as varchar) + ' m.' ; ' ' + Termination_Comments Is there I can fix this for the Location.x_coord? Regards Ash
Warwick Wood  
#4 Posted : Friday, March 13, 2020 8:00:58 AM(UTC)
Warwick Wood
Rank: Administration

Reputation:

Groups: Registered, Administrators
Posts: 503
Man
Location: Byron Bay

Was thanked: 19 time(s) in 19 post(s)

Hi Ashley,

If you get an error message "[SQL Server]Error converting data type varchar to float" you can add this around the numeric field, so that they are both text (varchar means text):

Cast([YourNumericField] as varchar)

Regards,

Warwick

Ashley  
#5 Posted : Friday, March 13, 2020 11:48:30 AM(UTC)
Ashley
Rank: Member

Reputation:

Groups: Registered
Posts: 14

Thanks: 1 times
Thanks Warwick that's perfect
Ashley  
#6 Posted : Friday, March 13, 2020 2:40:38 PM(UTC)
Ashley
Rank: Member

Reputation:

Groups: Registered
Posts: 14

Thanks: 1 times

Hi Warwick,

With my drill dates on my log templates I am wondering if it is possible to add a ' - ' between the 2 dates but only when Date2 is shown. The Dates need to be formatted as DD-MMM-YYYY

i.e. Date1 - Date2

or

Date1

Below is the function a use and it doesn't work.

Case When Date_Finalised = Date_Commenced or Date_Finalised is null Then Null ELSE ' - ' + cast([Date_Finalised] as datetime) END

If I use cast([Date_Finalised] as varchar) it works but the date is in the wrong format.

Could you please help

Cheers

Ash

Warwick Wood  
#7 Posted : Friday, March 13, 2020 2:53:43 PM(UTC)
Warwick Wood
Rank: Administration

Reputation:

Groups: Registered, Administrators
Posts: 503
Man
Location: Byron Bay

Was thanked: 19 time(s) in 19 post(s)

Hi Ashley,

If you do a google search on "date formats tsql" you should find lots of resources for presenting date formats via T-SQL.  For example https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/  

(T-SQL) is the syntax supported in the gedts files.

Warwick

thanks 1 user thanked Warwick Wood for this useful post.
Ashley on 3/16/2020(UTC)
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.