Skip to Content
author's profile photo Former Member
0
Former Member

Query For Partticular Screen

Posted on Nov 29, 2007 at 09:39 AM 20 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 10:30 AM
    3

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Nov 30, 2007 at 05:19 AM

      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

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 09:59 AM
    0

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Nov 29, 2007 at 10:29 AM

      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

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.