Toad World® Forums

format sql not working for this query


#1

SELECT JobList.ValidFrom AS “ValidFrom”, JobList.ValidThru AS “ValidThru”,
JobList.BpaMainPKey AS “BpaMainPKey” , JobList.Done AS “ListDone”, rd.pkey AS
“RDPKey”, rd.name AS “RD”, vd.pkey AS “VDPKey”, vd.name AS “VD”, UsrMain.PKey AS
“ADPKey”, UsrMain.Name AS “AD”, BpaMain.Id AS “BpaMainId”, BpaMain.Name AS
“BpaMainName” , bpamanagement. ManagementType AS “ManagementType” ,
BpaMain.BpaType AS “BpaType”, BpaMain.BpaState AS “BpaState”, BpaCompanySegment.
name2 AS “Name2”, BpaAddress.ZipCode AS “ZipCode”, BpaAddress.City AS “City”,
BpaAddress.Street AS “Street”, BpaIndustry. text AS “GSTyp”, MetaClbText. Text
AS “MetaText”, MetaClbMeta. PKey AS “MetaPKey”, jobmeta.id AS “JobMetaId”,
jobdefinitionlist. pkey AS “JobMetaPKey” , Replace(JobListText .Text,‘Kopie von
‘,’’) AS “Job”, jobdefinition. pkey AS “JobPKey”, jobdefinitionlist. id AS
“JobId”, jobdefinitionlist. priority AS “Priority”, clbmain.note AS “Notiz”,
JobDefinitiontext. text AS “Frage”, jobdefinition. sort AS “Sort”, jobmain.value
AS “Antwort”, RpsDomain.id as “Domain”, RpsDomain.datatype as “Datatype”,
JobMain.Done AS “Done”, jobmain.visitdate AS “Datum”, jobmain.JobActionSu ccess
AS “Erfolg”, gastrogstyp. MyGSTypMarketing Crit1txt AS “GastroGST1” ,
gastrogstyp. MyGSTypMarketing Crit2txt AS “GastroGST2” , gastrogstyp.
MyGSTypMarketing Crit3txt AS “GastroGST3” FROM bpamanagement bpamanagement INNER
JOIN JOBLIST JobList with(index(JobList1 0)) ON bpamanagement. bpamainpkey =
joblist.bpamainpkey AND bpamanagement. active = 1 and bpamanagement. main = 1
AND bpamanagement. validfrom =
getdate () AND bpamanagement. status <> ‘d’ AND JobList.Status <> ‘d’ AND
JobList.Client = ‘005’ and JobList.Done in (‘0’, ‘1’) INNER JOIN
JobDefinitiontext JobDefinitiontext with(index(JobDefin itionText11) ) ON
JobDefinitiontext. jobdeflistpkey = joblist.jobdeflistp key INNER JOIN
jobdefinition jobdefinition with (index(JobDefinitio n13)) ON jobdefinition.
jobdeflistpkey = JobList.JobDefListP Key AND jobdefinition. PKey =
JobDefinitiontext. JobDefinitionPKe y AND jobdefinition. status <>‘d’ AND
jobdefinition. Client =‘005’ inner join jobdefinitionmeta jobdefinitionmeta with
(index(UK_JobDefini tionMeta) ) on jobdefinition. JobDefinitionMet aPKey =
jobdefinitionmeta. pkey inner join rpsdomain rpsdomain on rpsdomain.PKey =
jobdefinitionmeta. RpsDomainPKey INNER JOIN jobdefinitionlist JobDefinitionList
ON JobDefinition. JobDefListPKey = JobDefinitionList. PKey INNER JOIN jobmeta
jobmeta ON jobmeta.pkey = jobdefinition. jobmetapkey LEFT OUTER JOIN jobmain
jobmain ON JobMain.joblistpkey = joblist.PKey AND jobmain.jobdefiniti onpkey
=JobDefinitiontext. jobdefinitionpke y AND JobMain.PrdMainPKey = ’ ’ AND
JobMain.Status <> ‘d’ AND JobMain.Client = ‘005’ LEFT OUTER JOIN clbmain clbmain
ON clbmain.pkey = joblist.clbmainpkey AND clbmain.Status <> ‘d’ AND
clbmain.client = ‘005’ INNER JOIN BPAMAIN BpaMain ON BpaMain.PKey =
JobList.BpaMainPKey AND BpaMain.Status <> ‘d’ AND BpaMain.Client = ‘005’ INNER
JOIN BPAADDRESS BpaAddress ON BpaMain.PKey = BpaAddress.BpaMainP Key AND
BpaAddress.Main = ‘1’ AND BpaAddress.Status <> ‘d’ AND BpaAddress.Client = ‘005’
INNER JOIN BpaCompanySegment BpaCompanySegment ON BpaCompanySegment. bpamainpkey
= BpaMain.pkey AND BpaCompanySegment. status <> ‘d’ AND BpaCompanySegment.
client = BpaMain.client INNER JOIN USRMAIN UsrMain ON UsrMain.PKey =
bpamanagement. usrmainpkey AND UsrMain.Status <> ‘d’ AND UsrMain.Client = ‘005’
INNER JOIN systoggleitemtext bpaIndustry ON BpaCompanySegment. industry =
bpaIndustry. systoggleitemhke y AND bpaIndustry. status <> ‘d’ AND bpaIndustry.
salesorg = ‘0100’ AND bpaIndustry. systogglehkey = ‘Industry’ INNER JOIN CLBMETA
MetaClbMeta ON MetaClbMeta. PKey = JobList.ClbMetaPKey AND MetaClbMeta. Status
<> ‘d’ AND MetaClbMeta. Client = ‘005’ INNER JOIN CLBTEXT MetaClbText ON
MetaClbText. ReferencePKey = MetaClbMeta. PKey AND MetaClbText. Usage = ‘Meta’
AND MetaClbText. Status <> ‘d’ AND MetaClbText. Client = ‘005’ AND MetaClbText.
Language = ‘de’ AND MetaClbMeta. Status <> ‘d’ AND MetaClbMeta. Client = ‘005’
INNER JOIN CLBMETAMETA MetaMetaMetaClbMeta Meta ON MetaMetaMetaClbMeta Meta.PKey
= MetaClbMeta. ClbMetaMetaPKey AND MetaMetaMetaClbMeta Meta.Status <> ‘d’ AND
MetaMetaMetaClbMeta Meta.Client = ‘005’ INNER JOIN CLBTEXT MetaMetaMetaClbText
ON MetaMetaMetaClbText .ReferencePKey = MetaMetaMetaClbMeta Meta.PKey AND
MetaMetaMetaClbText .Usage = ‘MetaMeta’ AND MetaMetaMetaClbText .Status <> ‘d’
AND MetaMetaMetaClbText .Client = ‘005’ AND MetaMetaMetaClbText .Language = ‘de’
AND MetaMetaMetaClbMeta Meta.Status <> ‘d’ AND MetaMetaMetaClbMeta Meta.Client =
‘005’ AND MetaClbMeta. Status <> ‘d’ AND MetaClbMeta. Client = ‘005’ AND
JobList.Status <> ‘d’ AND JobList.Client = ‘005’ INNER JOIN JOBLISTTEXT
JobListText ON JobList.PKey = JobListText. JobListPKey AND JobListText. Status
<> ‘d’ AND JobListText. Client = ‘005’ AND JobListText. Language = ‘de’ INNER
JOIN CssAnaUsrRelRsl vdr ON vdr.parentnodelevel = 2 AND vdr.childnodepkey =
bpamanagement. usrmainpkey AND vdr.validfrom =
getdate () AND vdr.status <> ‘d’ INNER JOIN usrmain vd ON vd.pkey =
vdr.parentnodepkey INNER JOIN CssAnaUsrRelRsl rdr ON rdr.parentnodelevel = 1 AND
rdr.childnodepkey = bpamanagement. usrmainpkey AND rdr.validfrom
AND rdr.validthru >= getdate () AND rdr.status <> ‘d’ INNER JOIN usrmain rd ON
rd.pkey = rdr.parentnodepkey LEFT OUTER JOIN (SELECT BPADELIVERYREL. topkey
bpamainpkey, stxt1.text MyGSTypMarketingCri t1txt, stxt2.text
MyGSTypMarketingCri t2txt, stxt3.text MyGSTypMarketingCri t3txt FROM
BpaMyGastroKriterie n BpaMyGastroKriterie n INNER JOIN BPADELIVERYREL
BPADELIVERYREL ON BpaMyGastroKriterie n.BpaMainPKey = BPADELIVERYREL. fromPKey
AND BPADELIVERYREL. main = ‘1’ LEFT OUTER JOIN SysToggleItemText stxt1 ON
stxt1.systogglehkey = ‘BpaMyGSTypMarketin gCrit1’ AND stxt1.systoggleitem hkey =
BpaMyGastroKriterie n.MyGSTypMarketi ngCrit1 AND stxt1.status <> ‘d’ LEFT OUTER
JOIN SysToggleItemText stxt2 ON stxt2.systogglehkey = ‘BpaMyGSTypMarketin
gCrit2’ AND stxt2.systoggleitem hkey = BpaMyGastroKriterie n.MyGSTypMarketi
ngCrit2 AND stxt2.status <> ‘d’ LEFT OUTER JOIN SysToggleItemText stxt3 ON
stxt3.systogglehkey = ‘BpaMyGSTypMarketin gCrit3’ AND stxt3.systoggleitem hkey =
BpaMyGastroKriterie n.MyGSTypMarketi ngCrit3 AND stxt3.status <> ‘d’)
gastrogstyp ON joblist.bpamainpkey = gastrogstyp. bpamainpkey WHERE
JobList.Status <> ‘d’ AND JobList.Client = ‘005’ and JobList.ValidThru >=
‘2010-04-18’ and JobList.ValidFrom
UsrMainPKey in (select childnodepkey from CssAnaUsrRelRsl where parentnodepkey =
(select pkey from usrmain where name like 'Radeberger Gruppe, Sammelnutzer% '))
and jobdefinitionlist. pkey in (‘00100000007cr6g1’ ,‘00100000007cri
nm’,‘00100000007 cruy1’) ) order by jobdefinitionlist. pkey, joblist.validfrom,
joblist.validthru, bpamain.id, jobdefinition. sort