|
<< Click to Display Table of Contents >> Navigation: User Interface > The Browser > The Query Page > Query macros |
The query files .SQL can contain some predefined macros that are expanded by MechworksPDM at load time.
The macros available are:
$(SYSUSERNAME) |
Depending on the DBWorks Option → Advanced → [X] Use full user name, expands the current log in user name or the full user name as seen by the Primary Domain Controller of the network |
|---|---|
$(CURRENTPROJECTUNIQUEID) |
Expands the value of the unique id of the current project |
$(ASSIGNEDPROJECTSUNIQUEIDS) |
Under the DBWARM control, expands the list of the unique ids of the projects assigned to the current user |
$(?<dictionary item>) |
Localizes the item assigned in accordance with the definitions located in the messages file DBWORKS\DBWORKS.MSG |
$(DBWORKS_DSN) |
Expands in the parameter DBWORKS_DSN as defined in DBWCONN.PAR |
$(DBWEXCEL_DSN) |
Expands in the parameter DBWEXCEL_DSN as defined in DBWCONN.PAR |
$(DBWBOM_DSN) |
Expands in the parameter DBWBOM_DSN as defined in DBWCONN.PAR |
$(DBWORKS_UID) |
Expands in the parameter DBWORKS_UID as defined in DBWCONN.PAR |
$(DBWORKS_PWD) |
Expands in the parameter DBWORKS_PWD as defined in DBWCONN.PAR |
1.List all the documents checked out by the current user
SELECT ID,DESCRIPTION FROM DOCUMENT WHERE CHECK_OUT_BY='$(SYSUSERNAME)'
2.List all the non 3D CAD documents of type .DWG or .DXF of the current project
SELECT DISTINCT T,ID,DESCRIPTION,FILE_NAME,FILE_DIRECTORY,STATE,REVISION,ACTIVE_REVISION FROM DOCUMENT INNER JOIN PARENT_CHILD ON ( DOCUMENT.UNIQUE_ID=PARENT_CHILD.CHILD_UNIQUE_ID ) WHERE PARENT_UNIQUE_ID=$(CURRENTPROJECTUNIQUEID) AND T='G' AND ( FILE_NAME LIKE '%.DWG' OR FILE_NAME LIKE '%.DXF' ) ORDER BY ID
3.List all the non 3D CAD documents of type .DWG or .DXF of the DBWARM assigned projects
SELECT DISTINCT T,ID,DESCRIPTION,FILE_NAME,FILE_DIRECTORY,STATE,REVISION,ACTIVE_REVISION FROM DOCUMENT INNER JOIN PARENT_CHILD ON ( DOCUMENT.UNIQUE_ID=PARENT_CHILD.CHILD_UNIQUE_ID ) WHERE PARENT_UNIQUE_ID IN $(ASSIGNEDPROJECTSUNIQUEIDS) AND T='G' ORDER BY ID
4.Write a query for all the released .DWG and .DXF documents in a language-independent way:
SELECT $(?NAME_FIELD_T),$(?NAME_FIELD_ID),$(?NAME_FIELD_FILE_NAME),$(?NAME_FIELD_FILE_DIRECTORY) FROM $(?NAME_DOCUMENT_TABLE) WHERE $(?NAME_FIELD_T)='G' AND ( $(?NAME_FIELD_FILE_NAME) LIKE '%.DWG' OR $(?NAME_FIELD_FILE_NAME) LIKE '%.DXF' ) AND $(?NAME_FIELD_STATE)='$(?NAME_STATE_RELEASED_STRING)'
5.Query that gets a connection to another data source, then switches back to the main one. As you can see, using the macro you don't sepcify excpicitely the name of the MechworksPDM data source and you let the macro find the name for you.
.SQL
.KEY 0
STOP;
SET DB DBWCUST;
START;
SELECT SUPPLIER_ID,SUPPLIER_NAME,ADDRESS,CITY FROM SUPPLIER;
STOP;
SET DB $(DBWORKS_DSN);
START;
6.Query that finds the document that don't belong to any project
SELECT $(?NAME_FIELD_UNIQUE_ID) AS UNIQUE_ID,$(?NAME_FIELD_ID) AS ID FROM $(?NAME_DOCUMENT_TABLE) WHERE
$(?NAME_FIELD_T)<>'0' AND
NOT $(?NAME_FIELD_UNIQUE_ID) IN (
SELECT $(?NAME_FIELD_UNIQUE_ID) FROM $(?NAME_DOCUMENT_TABLE),$(?NAME_PARENT_CHILD_TABLE)
WHERE $(?NAME_DOCUMENT_TABLE).$(?NAME_FIELD_UNIQUE_ID)=$(?NAME_PARENT_CHILD_TABLE).$(?NAME_FIELD_CHILD_UNIQUE_ID)
AND $(?NAME_PARENT_CHILD_TABLE).$(?NAME_FIELD_PARENT_UNIQUE_ID) IN (
SELECT $(?NAME_FIELD_UNIQUE_ID) FROM $(?NAME_DOCUMENT_TABLE) WHERE $(?NAME_FIELD_T)='0'
)
)
;