Kyndt, Davy
2015-03-01T09:35:00Z
Can I insert an sql statement like this, where I can replace one parameter with the name of another table?

I'd like to sync with a calender where I can plan and adjust permits. The location is linked to location in the calendar

I have a table1 called:
-permittoworkforcontractor
with fields:
-permitnumber
-location_id

table2:
-location
with fields:
-id
-name

SELECT DISTINCTROW permittoworkforcontractor.permitNumber, permittoworkforcontractor.location_id,  location.name	
FROM permittoworkforcontractor, location
WHERE location.id LIKE permittoworkforcontractor.location_id


Now it is showing following

permitNumber location_id
2-CPO-REP-PER-001 22
2-CPO-MEC-PER-001 55
2-CPO-CPO-PER-001 55
2-CPO-CPO-PER-002 55

And i like to see this:

permitNumber name
2-CPO-REP-PER-001 D0
2-CPO-MEC-PER-001 OTS
2-CPO-CPO-PER-001 OTS
2-CPO-CPO-PER-002 OTS
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2015-03-01T11:01:00Z
hello,

where do you want to insert the statement?
Kyndt, Davy
2015-03-01T11:07:00Z
somewhere inside GC, as I cannot alter the database structure.
It only has to read the locations, as I only use the Load all function on that connection.
Administrator
2015-03-01T11:25:00Z
No,

there is no such feature.

You can use custom SQL Statements / stored procedures for:
-insert, update, delete (Folder options->SQL Commands)
-Database change detection (Folder options->Database change detection)
-Filter rows (only where part)
-Primary / Mandatory keys (statements for key retrieval )

Why can't you create a View (based on your statement) in your DB, a View does not modify your DB structure. In most DB systems you can even create a View in another DB and use tables from other DB's,
example:
create view database1.dbo.viewname as
select ... from database2.dbo.MyTable....
Kyndt, Davy
2015-03-01T12:03:00Z
I have no rights to create a view.

Executing:
USE `****`;
CREATE OR REPLACE VIEW `new_view` AS
SELECT DISTINCT permittoworkforcontractor.permitNumber, permittoworkforcontractor.location_id, location.name
FROM permittoworkforcontractor, location
WHERE location.id LIKE permittoworkforcontractor.location_id;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1142: CREATE VIEW command denied to user 'user'@'ipadress' for table 'new_view'
SQL Statement:
CREATE OR REPLACE VIEW `new_view` AS
SELECT DISTINCT permittoworkforcontractor.permitNumber, permittoworkforcontractor.location_id, location.name
FROM permittoworkforcontractor, location
WHERE location.id LIKE permittoworkforcontractor.location_id

Administrator
2015-03-01T12:17:00Z
is there maybe a DB administrator that can give you permissions or create a new Database for you ? You can have all/admin permission in the new DB and only data read/write on the old/orig. DB
Kyndt, Davy
2015-03-02T21:01:00Z
I've had contact with the admin, and he gave me the needed permissions. The new view gives me everything I wanted. Thanks 🙂
Similar Topics