0

I am trying to use the "Create NEW SQL view" function in Geoserver to create a new view, in which I wish to show a subset of records from a table in MSSQL based on a value in another table. I have two layers in the same MSSQL DATABASE one with some geometries ("LER_REQUEST_MAP") and another with some needed information ("LER_REQUEST_V2"). One of the informations in the later is an ESRI based timestamp field called "DIG_PERIOD_FROM".

I wish to use my view to:

a) join these two tables (INNER JOIN?), so that I get the information on DIG_Period_FROM to my geometry layer (LER_REQUEST_MAP) without compromising the original table.

b) SELECT * from "the new joined table" where the field DIG_PERIOD_FROM > CURRENT_DATE

Is this possible?

Improve this question

1 Answer 1

Reset to default
2

Yes - you can create a view based on anything table(s) you like assuming you have permission to see them. I expect you will end up with something like:

select * from table1,table2 where table1.id = table2.id and digperiod > GETDATE();
Improve this answer
5
  • Hi iant. Thanks that helps me some of tthe way. When I try a version of your code corrected to my tables I get an error message saying "The column 'OBJECTID' was specified multiple times for 'vtable'." And that is true. There is OBJECTID's in both tables (they are not corresponding). How can I get pass this, without altering the original tables?
    – Kristian Birchvald Jensen
    Jan 19, 2017 at 13:43
  • don't select OBJECTID from both or rename one of them if you actually need both
    – Ian Turton ♦
    Jan 19, 2017 at 13:51
  • How do I write into my SQL statement that I wish to only have OBJERCTID from one table. I would very much like not to make any changes (what so ever) to the original tables, but make all the adjustments in the SQL Query.
    – Kristian Birchvald Jensen
    Jan 19, 2017 at 13:54
  • 1
    select table1.objectid, table1.geom,....,table2.attribute1,table2.attribute2... from table1, table2 ...
    – Ian Turton ♦
    Jan 19, 2017 at 13:56
  • Thanks! That works like a charm. It always seems simple afterwards :)
    – Kristian Birchvald Jensen
    Jan 19, 2017 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.