Query For Partticular Screen

[Former Member]

Hai To EveryOne,

I wrote query to display all fields in particular screen. Iam getting screen name as input.This is the query

SELECT T2.[Name] AS 'TableName' , T1.[Name] AS 'Name', T0.[AliasID] AS 'Title', T0.[Descr] AS 'Description', T0.[EditSize] AS 'Edit Size', T0.[EditType] AS 'Edit Type', T0.[FieldID] AS 'Field'

FROM [dbo].[CUFD] T0 , [dbo].[OUDO] T1 ,

(Select A.[Name],substring(A.[Name],2,len(A.[Name])) as tbl from [dbo].[sysobjects] A Where name like '@%') T2

WHERE T0.[TableID] like '@PUR%' and

T2.[tbl] IN (Select OUDO.[tablename] as tbl from OUDO UNION ALL Select UDO1.[tablename] as tbl from UDO1)

and T2.[tbl]=T1.[TableName]

and T2.[Name]=T0.[TableID]

order by T1.[Name] asc,T2.[Name] desc

In this @pur% is the table name.

Now my problem is if i execute this query only head table is displayed not the detail table i also need detail table.

Does anyone tell me whts the wrong in this query....

Regards,

Anitha

[Former Member · Accepted solution]

Hi Anitha,

Try and simplify the query a bit. I have modified your query to the query below. I hope it serves the same purpose.

SELECT T0.[TableID] AS 'TableName', 
	T1.[Name] AS 'ObjectName', 
	T0.[AliasID] AS 'Title', 
	T0.[Descr] AS 'Description', 
	T0.[EditSize] AS 'Edit Size', 
	T0.[EditType] AS 'Edit Type', 
	T0.[FieldID] AS 'Field' 
FROM [dbo].[CUFD] T0 
LEFT OUTER JOIN OUDO T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)
WHERE T0.[TableID] like '@PUR%'

Hope it helps,

Adele

[Former Member · Accepted solution]

Adele Thanks a lot...............

[Former Member · Accepted solution]

Adele,

Thanks but there is one more doubt ur query display the field name when i give the table name but if my input is UDO name it should show all the header and detail part how i can???

Regards,

Anitha

[Former Member · Accepted solution]

Hi Anitha,

Try this:

SELECT T0.[TableID] AS 'TableName', 
	T1.[Name] AS 'ObjectName', 
	T0.[AliasID] AS 'Title', 
	T0.[Descr] AS 'Description', 
	T0.[EditSize] AS 'Edit Size', 
	T0.[EditType] AS 'Edit Type', 
	T0.[FieldID] AS 'Field' 
FROM [dbo].[CUFD] T0 
LEFT OUTER JOIN OUDO T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)
WHERE T1.[Code] like 'ObjName%' 

UNION

SELECT T0.[TableID] AS 'TableName', 
	T2.[Name] AS 'ObjectName', 
	T0.[AliasID] AS 'Title', 
	T0.[Descr] AS 'Description', 
	T0.[EditSize] AS 'Edit Size', 
	T0.[EditType] AS 'Edit Type', 
	T0.[FieldID] AS 'Field' 
FROM [dbo].[CUFD] T0 
LEFT OUTER JOIN UDO1 T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)
LEFT OUTER JOIN OUDO T2 on T2.Code = T1.Code
WHERE T1.[Code] like 'ObjName%'

Hope it helps,

Adele

[Former Member · Accepted solution]

Ssssssssssss Thanks Adele.......If we execute this query in Query Generator we cant get the input box if u using this query in Query Wizard there is error....Wht would be the solution for this???

Regards,

Anitha

[Former Member · Accepted solution]

Not sure Anitha, the query is working perfectly in SQL Query Analyzer... will play around a bit more and see what we can do

[Former Member · Accepted solution]

Hi Anitha,

I added a parameter to get the Code of the User-Defined Object as well as removed the [dbo]. as it seemed to cause the error. I really hope this works as I can't spend much more time on this - gotta get some work done too

DECLARE @ObjName VARCHAR(100)
SELECT @ObjName =T4.Code FROM OUDO T4 WHERE T4.Code = '[%0]'
SELECT T0.[TableID] AS 'TableName', 
	T1.[Name] AS 'ObjectName', 
	T0.[AliasID] AS 'Title', 
	T0.[Descr] AS 'Description', 
	T0.[EditSize] AS 'Edit Size', 
	T0.[EditType] AS 'Edit Type', 
	T0.[FieldID] AS 'Field' 
FROM [CUFD] T0 
LEFT OUTER JOIN OUDO T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)
WHERE T1.[Code] = @ObjName
UNION
SELECT T0.[TableID] AS 'TableName', 
	T2.[Name] AS 'ObjectName', 
	T0.[AliasID] AS 'Title', 
	T0.[Descr] AS 'Description', 
	T0.[EditSize] AS 'Edit Size', 
	T0.[EditType] AS 'Edit Type', 
	T0.[FieldID] AS 'Field' 
FROM [CUFD] T0 
LEFT OUTER JOIN UDO1 T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)
LEFT OUTER JOIN OUDO T2 on T2.Code = T1.Code
WHERE T1.[Code] = @ObjName

Hope it helps,

Adele

[Former Member · Accepted solution]

Chance less Adele thanks for ur kind help...........

[Former Member · Accepted solution]

Adele,

Is this possible to get the Predefined Screen also by adding some more condition in the query by....this

DECLARE @ObjName VARCHAR(100)

DECLARE @tb VARCHAR(100)

SELECT @ObjName =T4.Code FROM OUDO T4 WHERE T4.Code = 'CAN_UDO'

SELECT @tb=T.TableID FROM CUFD T WHERE T.TableID='OITM'

SELECT T0.[TableID] AS 'TableName',

T1.[Name] AS 'ObjectName',

T0.[AliasID] AS 'Title',

T0.[Descr] AS 'Description',

T0.[EditSize] AS 'Edit Size',

T0.[EditType] AS 'Edit Type',

T0.[FieldID] AS 'Field'

FROM [CUFD] T0

LEFT OUTER JOIN OUDO T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)

WHERE T1.[Code]=@ObjName AND T0.[TableID]=@tb

UNION

SELECT T0.[TableID] AS 'TableName',

T2.[Name] AS 'ObjectName',

T0.[AliasID] AS 'Title',

T0.[Descr] AS 'Description',

T0.[EditSize] AS 'Edit Size',

T0.[EditType] AS 'Edit Type',

T0.[FieldID] AS 'Field'

FROM [CUFD] T0

LEFT OUTER JOIN UDO1 T1 on T1.TableName = substring(T0.TableID, 2, Len(T0.TableID)-1)

LEFT OUTER JOIN OUDO T2 on T2.Code = T1.Code

WHERE T1.[Code]=@ObjName AND T0.[TableID]=@tb

Thanks & Regards,

Anitha

[Former Member]

Hi Anitha,

Your "and T2.[tbl]=T1.[TableName]" condition do this because there is only head table of user-defined object at T1 table (OUDO) not the detail table.

Try to delete this condition as below:

SELECT T2.[Name] AS 'TableName' , T1.[Name] AS 'Name', T0.[AliasID] AS 'Title', T0.[Descr] AS 'Description', T0.[EditSize] AS 'Edit Size', T0.[EditType] AS 'Edit Type', T0.[FieldID] AS 'Field'

FROM [dbo].[CUFD] T0 , [dbo].[OUDO] T1 ,

(Select A.[Name],substring(A.[Name],2,len(A.[Name])) as tbl from [dbo].[sysobjects] A Where name like '@%') T2

WHERE T0.[TableID] like '@PUR%' and

T2.[tbl] IN (Select OUDO.[tablename] as tbl from OUDO UNION ALL Select UDO1.[tablename] as tbl from UDO1)

and T2.[Name]=T0.[TableID]

order by T1.[Name] asc,T2.[Name] desc

This working fine on my side.

HIH,

Anton

[Former Member]

Anton,

Thanks for ur immediate response.If i delete that particular condition i cant get the Tablename and UDO name as equal.............

Regards,

Anitha

[Former Member]

Anitha,

What you exactly want to select? Can you describe your needs more precisely?

Regards,

Anton.

[Former Member]

S Sure,

Iam in Purchase Module I have more than 10 screens in that module.. My aim is that if i give any screen name as input it wants to display all the field names in that particular screen.

For Example,

If i create Purchase Indent with one header part and one detail part with more than 25 fields with two tables named as @PUR_PI_HEAD and @PUR_PI_DETAIL. I have the UDO name for this screen named PI_UDO

If i give the input as PI_UDO all the fields in two tables have to display.....

Regards,

Anitha