In the following lines there will be listed some complex queries used for a more detailed data retrieval, these queries can be used by creating a new query (as you did in the previous examples) and add the query text using 'String\Wizard' button, after this, a window where you can insert the query text will be available.
Asset of the day searching by typology and network
Select F.Cod_Progra, F.ID_Filmati, F.Descrizio, S.Cod_Supp as CodiceSupporto, S.ID_Supporti, S.BarCode, R.Inf_Cass as MarkIn, R.Fin_Cass as MarkOut, R.Parte, R.ID_Registrazioni, F.SupportoD, F.SupportoDBkp, F.FileLQ, F.NewType, P.Data, 0 Cod_User, 0 Ora
from FILMATI F with (nolock)
left join FSERIE FS with (nolock) on FS.ID_Serie = F.ID_Serie
left join TPALINSE P with (nolock) on P.ID_Filmati = F.ID_Filmati
left join FREGISTRAZIONI R with (nolock) on R.ID_Filmati = F.ID_Filmati
left join FSUPPORTI S with (nolock) on S.ID_Supporti = R.ID_Supporti
Where f.NEWTYPE = ?f.NEWTYPE,Tipologia ,C?
and f.supportod = ''
and p.data = ?p.data,Giorno,C?
and p.livello=0
and p.cod_progra<>''
group by F.ID_Filmati, F.Cod_Progra, F.Descrizio, S.cod_Supp, S.ID_Supporti, S.Barcode,
R.Inf_Cass, R.Fin_Cass, R.Parte, R.ID_Registrazioni, F.SupportoD,
F.SupportoDBkp, F.FileLQ, F.NewType
, P.Data
order by p.ora
Asset loaded today ordered by operator
Select filmati.COD_PROGRA, filmati.DESCRIZIO, filmati.DURATA, filmati.SUPPORTOD, filmati.SUPPORTODBKP, filmati.POS_INI, filmati.POS_FIN,
filmati.NEWTYPE, filmati.DATA_ACQUI, filmati.DATA_INIZI, filmati.DATA_TERMI
From filmati
Where filmati.DATA_DIGIT = GETDATE ( )
order by OPERATORE_REC
Asset on scheduling with quality to be checked
Select F.Cod_Progra, F.ID_Filmati, F.Descrizio, S.Cod_Supp as CodiceSupporto, S.ID_Supporti, S.BarCode, R.Inf_Cass as MarkIn, R.Fin_Cass as MarkOut, R.Parte, R.ID_Registrazioni, P.Cod_User, P.Data, P.Ora, F.SupportoD, F.SupportoDBkp, F.FileLQ, F.NewType, FU.Cod_User UserNotPermit
from FILMATI F with (nolock)
join FSERIE FS with (nolock) on FS.ID_Serie = F.ID_Serie
left join FUSERS FU with (nolock) on FU.ID_Serie = FS.ID_Serie
left join TPALINSE P with (nolock) on P.ID_Filmati = F.ID_Filmati
left join FREGISTRAZIONI R with (nolock) on R.ID_Filmati = F.ID_Filmati
left join FSUPPORTI S with (nolock) on S.ID_Supporti = R.ID_Supporti
where f.qualita = 0 and f.supportod not like '0live%' and f.supportod != ' '
and (F.Data_Scad >= (GetDate()) or F.Data_Scad is null)
group by F.ID_Filmati, F.Cod_Progra, F.Descrizio, S.cod_Supp, S.ID_Supporti, S.Barcode,
R.Inf_Cass, R.Fin_Cass, R.Parte, R.ID_Registrazioni, P.Cod_User, P.Data, P.Ora, F.SupportoD,
F.SupportoDBkp, F.FileLQ, F.NewType, FU.Cod_User
Asset on scheduling with proxy file preview
Select F.Cod_Progra, F.ID_Filmati, F.Descrizio, S.Cod_Supp as CodiceSupporto, S.ID_Supporti, S.BarCode, R.Inf_Cass as MarkIn, R.Fin_Cass as MarkOut, R.Parte, R.ID_Registrazioni, P.Cod_User, P.Data, P.Ora, F.SupportoD, F.SupportoDBkp, F.FileLQ, F.NewType, FU.Cod_User UserNotPermit
from FILMATI F with (nolock)
join FSERIE FS with (nolock) on FS.ID_Serie = F.ID_Serie
left join FUSERS FU with (nolock) on FU.ID_Serie = FS.ID_Serie
left join TPALINSE P with (nolock) on P.ID_Filmati = F.ID_Filmati
left join FREGISTRAZIONI R with (nolock) on R.ID_Filmati = F.ID_Filmati
left join FSUPPORTI S with (nolock) on S.ID_Supporti = R.ID_Supporti
where f.filelq<>'' and f.supportod not like '0live%' and f.supportod != ' '
and (F.Data_Scad >= (GetDate()) or F.Data_Scad is null)
group by F.ID_Filmati, F.Cod_Progra, F.Descrizio, S.cod_Supp, S.ID_Supporti, S.Barcode,
R.Inf_Cass, R.Fin_Cass, R.Parte, R.ID_Registrazioni, P.Cod_User, P.Data, P.Ora, F.SupportoD,
F.SupportoDBkp, F.FileLQ, F.NewType, FU.Cod_User
Syntax for manual query creation
The syntax to be used in Query Manager for a manual creation of queries is very simple, it performs the SQL instructions, written using parameters (interpretable only from Query Manager) only on request by the user.
Executing a Query without parameters
For example:
Select * from Assets (Filmati) where Cod_Progra like 'A%' order by Cod_Progra
Executing a Query with parameters
For example:
INSTRUCTION SQL... ?<Name_Field>, <Message>, <Type of field>?...
Select * from Assets (Filmati) where Cod_Progra like ? Cod_Progra,insert a code value,L? order by Cod_Progra
Please note that:
•'?' : this character identifies the beginning and the end of the substring of parameter
•<Name_field>: Identifies the field used by the condition
•<Message> : Identifies the message to be viewed from MessageBox of Windows for the input of the value of the parameter
•<Type of field>: Identifies the type of field to search
• N: Numerical type
• C: Character type (The value of the parameter is automatically contained between two apexes)
• L: Character type (Like the Type 'C' but at the end of the parameter the jolly character '%' is added)