Data links: Displaying alternate value on the fly

Permanently deleted user

May 22, 2015 02:56

Hi,

Is it possible to have the data returned from a Data Link being different then what is store in the database?  For examples, I am using a MS Access database and try to use the SQL statement with the SWITCH function but the data is not returned in the viewer.  The following is the command of the data link:

SELECT MAT, Switch(GenreConstruction="1", "Home", GenreConstruction="5", "Condo") FROM rol_b00_tab WHERE MAT = ?

Obviously, the goal is to show Home when performing an identify in the viewer instead of 1 .

Please advise.

Thanks,

Eric

0

• 

  Permanently deleted user

  May 22, 2015 03:03

  Good morning Eric

  I guess the first question with the statement that you have included since I have not done any Datalinks to an MS-Access database is does the following work as a Datalink:

  SELECT MAT, GenreConstruction FROM rol_b00_tab WHERE MAT = ?

  Secondly does your statement work correctly when used in MS-Access ie take Geocortex  out of the picture.

  Regards

  Ralph

  0
• 

  Permanently deleted user

  May 22, 2015 20:02

  Hi Ralph,

  Yes, I am able to run the Data Link command without the SWITCH in Gecortex successfully.  I also am able to run the query in MS Access with the SWITCH successfully although I using a slightly different format, as per the example (http://www.techonthenet.com/access/functions/advanced/switch.php) here :

  Const_Type: Switch([GenreConstruction]="1", "Home", [GenreConstruction]="5", "Condo")

  One correction from the OP, the command should be:

  SELECT MAT, Switch(GenreConstruction="1", "Home", GenreConstruction="5", "Condo") AS Const_Type FROM rol_b00_tab WHERE MAT = ?

  Nonetheless, I am still having the same issue.

  Thanks,

  Eric

  0
• 

  Permanently deleted user

  May 23, 2015 00:59

  Hi Eric,

  It may be related to the use of double quotes (") vs. single quotes (').  You can try SELECT MAT, Switch(GenreConstruction='1', 'Home', GenreConstruction='5', 'Condo') FROM rol_b00_tab WHERE MAT = ?.  This may do the trick.  If it doesn't work, take a look in your Essentials log file ([ESSENTIALS_ROOT]\Latitude Geographics\Geocortex Essentials\Default\REST Elements\REST\App_Data\Logs) to see if there is an SQL error being logged when the data link tries to resolve.  It may be that the database driver you are using to connect to the MS Access database doesn't support the SWITCH statement.

  Colin

  0

Please sign in to leave a comment.