Syntax error on nested SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Syntax error on nested SQL query

Author

Topic

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 09:26:37

Hi everyone,

I'm brand new to this forum as well as new to this level of SQL query complexity, so I hope I am posting in the correct place.

I have this query that I have been working on for a while, with the help of others, and we are all stuck. I am using MS-SQL as my backend database. The issue is a syntax error on a right parenthesis.

I thank you in advance for any help or feedback offered! :-)

Here is the query:

 
Select date_friday, BG_USER_05, Count (BG_BUG_ID) From
 (Select date_friday, BG_BUG_ID, BG_USER_05, Max(AU_TIME) as Latest_Change From
  (Select date_friday, BG_USER_05, BG_BUG_ID, AU_TIME FROM
   (select distinct d_friday as date_friday,  BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
     (Select au_time, DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday FROM audit_log where DATEPART(YEAR, au_time) = '2010') results_1
     left join
     (Select AU_TIME, BG_BUG_ID, BG_USER_05 from AUDIT_LOG, AUDIT_PROPERTIES, BUG
                      Where AU_ENTITY_TYPE = 'BUG'
                      And AU_ENTITY_ID = BG_BUG_ID
                      And AU_ACTION_ID =  AP_ACTION_ID
                      AND AP_FIELD_NAME = 'BG_USER_05') results_2
                On results_2.AU_TIME <= d_friday)
              ) results, AUDIT_LOG, AUDIT_PROPERTIES
 AND Where AU_ENTITY_TYPE = 'BUG'
 AND AP_FIELD_NAME = 'BG_USER_05'
 AND AU_ENTITY_ID = results.BG_BUG_ID
 AND AU_TIME = results.Latest_Change
 AND AU_ACTION_ID = AP_ACTION_ID
 AND AP_NEW_VALUE in  ('Open', 'Fixed', 'Retest', 'Deferred')
 Group By date_friday, BG_BUG_ID, BG_USER_05)
group by date_friday, BG_USER_05

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-25 : 09:44:01

[code]Select date_friday, BG_USER_05, Count (BG_BUG_ID) From
(Select date_friday, BG_BUG_ID, BG_USER_05, Max(AU_TIME) as Latest_Change From
(Select date_friday, BG_USER_05, BG_BUG_ID, AU_TIME FROM
(select distinct d_friday as date_friday, BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
(Select au_time, DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday FROM audit_log where DATEPART(YEAR, au_time) = '2010') results_1
left join
(Select AU_TIME, BG_BUG_ID, BG_USER_05 from AUDIT_LOG, AUDIT_PROPERTIES, BUG
Where AU_ENTITY_TYPE = 'BUG'
And AU_ENTITY_ID = BG_BUG_ID
And AU_ACTION_ID = AP_ACTION_ID
AND AP_FIELD_NAME = 'BG_USER_05') results_2
On results_2.AU_TIME <= d_friday)
) results, AUDIT_LOG, AUDIT_PROPERTIES
AND Where AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_USER_05'
AND AU_ENTITY_ID = results.BG_BUG_ID
AND AU_TIME = results.Latest_Change
AND AU_ACTION_ID = AP_ACTION_ID
AND AP_NEW_VALUE in ('Open', 'Fixed', 'Retest', 'Deferred')
Group By date_friday, BG_BUG_ID, BG_USER_05) as t
group by date_friday, BG_USER_05
[/code]

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-25 : 09:48:38

quote:

Originally posted by madhivanan

Select date_friday, BG_USER_05, Count (BG_BUG_ID) From
 (Select date_friday, BG_BUG_ID, BG_USER_05, Max(AU_TIME) as Latest_Change From
  (Select date_friday, BG_USER_05, BG_BUG_ID, AU_TIME FROM
   (select distinct d_friday as date_friday,  BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
     (Select au_time, DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday FROM audit_log where DATEPART(YEAR, au_time) = '2010') results_1
     left join
     (Select AU_TIME, BG_BUG_ID, BG_USER_05 from AUDIT_LOG, AUDIT_PROPERTIES, BUG
                      Where AU_ENTITY_TYPE = 'BUG'
                      And AU_ENTITY_ID = BG_BUG_ID
                      And AU_ACTION_ID =  AP_ACTION_ID
                      AND AP_FIELD_NAME = 'BG_USER_05') results_2
                On results_2.AU_TIME <= d_friday)
              ) results, AUDIT_LOG, AUDIT_PROPERTIES -- this makes no sense
 AND Where AU_ENTITY_TYPE = 'BUG'
 AND AP_FIELD_NAME = 'BG_USER_05'
 AND AU_ENTITY_ID = results.BG_BUG_ID
 AND AU_TIME = results.Latest_Change
 AND AU_ACTION_ID = AP_ACTION_ID
 AND AP_NEW_VALUE in  ('Open', 'Fixed', 'Retest', 'Deferred')
 Group By date_friday, BG_BUG_ID, BG_USER_05) as t
group by date_friday, BG_USER_05

Madhivanan

Failing to plan is Planning to fail

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 09:53:39

quote:

Originally posted by webfred

quote:

Originally posted by madhivanan

Select date_friday, BG_USER_05, Count (BG_BUG_ID) From
 (Select date_friday, BG_BUG_ID, BG_USER_05, Max(AU_TIME) as Latest_Change From
  (Select date_friday, BG_USER_05, BG_BUG_ID, AU_TIME FROM
   (select distinct d_friday as date_friday,  BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
     (Select au_time, DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday FROM audit_log where DATEPART(YEAR, au_time) = '2010') results_1
     left join
     (Select AU_TIME, BG_BUG_ID, BG_USER_05 from AUDIT_LOG, AUDIT_PROPERTIES, BUG --Also This makes no sense
                      Where AU_ENTITY_TYPE = 'BUG'
                      And AU_ENTITY_ID = BG_BUG_ID
                      And AU_ACTION_ID =  AP_ACTION_ID
                      AND AP_FIELD_NAME = 'BG_USER_05') results_2
                On results_2.AU_TIME <= d_friday)
              ) results, AUDIT_LOG, AUDIT_PROPERTIES -- this makes no sense
 AND Where AU_ENTITY_TYPE = 'BUG'
 AND AP_FIELD_NAME = 'BG_USER_05'
 AND AU_ENTITY_ID = results.BG_BUG_ID
 AND AU_TIME = results.Latest_Change
 AND AU_ACTION_ID = AP_ACTION_ID
 AND AP_NEW_VALUE in  ('Open', 'Fixed', 'Retest', 'Deferred')
 Group By date_friday, BG_BUG_ID, BG_USER_05) as t
group by date_friday, BG_USER_05

Madhivanan

Failing to plan is Planning to fail

No, you're never too old to Yak'n'Roll if you're too young to die.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 09:55:42

If you really know what you are doing then you can simplify your query to this.





Select date_friday, Count (BG_BUG_ID), BG_USER_05, Max(AU_TIME)as  Latest_Change FROM

(

						select distinct d_friday as date_friday, BG_USER_05, BG_BUG_ID, results_2.AU_TIME from

						(
									Select au_time,
									DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday 
									FROM audit_log 
									where DATEPART(YEAR, au_time) = '2010'
						) 

			results_1


			left join

						(

										Select AU_TIME, BG_BUG_ID, BG_USER_05 from 
										AUDIT_LOG, 
										AUDIT_PROPERTIES, 
										BUG
										Where AU_ENTITY_TYPE = 'BUG'
										And AU_ENTITY_ID = BG_BUG_ID
										And AU_ACTION_ID = AP_ACTION_ID
										AND AP_FIELD_NAME = 'BG_USER_05'
						) 


			results_2  On results_2.AU_TIME <= d_friday
				

) results, 

			AUDIT_LOG, AUDIT_PROPERTIES
			AND Where AU_ENTITY_TYPE = 'BUG'
			AND AP_FIELD_NAME = 'BG_USER_05'
			AND AU_ENTITY_ID = results.BG_BUG_ID
			AND AU_TIME = results.Latest_Change
			AND AU_ACTION_ID = AP_ACTION_ID
			AND AP_NEW_VALUE in ('Open', 'Fixed', 'Retest', 'Deferred')
)T

Group By date_friday,BG_USER_05

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 09:56:17

Thanks for the quick replies.

@ madhivanan - I tried your revised query, and I still get the same error. The exact error message is "Incorrect syntax near ")".

@ everyone - I sort of inherited this query and it has evolved from other's work. Its complexity is way beyond my current skill set, so if you have specific suggestions for me, I will try anything. :)
However, what would be most helpful is for me to have a complete example for me to copy and paste, to ensure I don't introduce more syntax errors through my efforts.

Thanks for everyone's understanding.

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 10:07:42

@ Idera

Thanks for the quick reply! I pasted your query into my application and executed it, and I got an error -- but the query was so long that I couldn't see the error message in the application's pop-up window. So, what I did was try to collapse some of the lines together like this:


Select date_friday, Count (BG_BUG_ID), BG_USER_05, Max(AU_TIME)as  Latest_Change FROM
(select distinct d_friday as date_friday, BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
                                                (Select au_time,
                                                DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday
                                                FROM audit_log
                                                where DATEPART(YEAR, au_time) = '2010'
                                                )
                        results_1
                        left join
                                                (Select AU_TIME, BG_BUG_ID, BG_USER_05 from
                                                AUDIT_LOG,
                                                AUDIT_PROPERTIES,
                                                BUG
                                                Where AU_ENTITY_TYPE = 'BUG'
                                                And AU_ENTITY_ID = BG_BUG_ID
                                                And AU_ACTION_ID = AP_ACTION_ID
                                                AnD AP_FIELD_NAME = 'BG_USER_05'
                                                )
                        results_2  On results_2.AU_TIME <= d_friday
) results,
                        AUDIT_LOG, AUDIT_PROPERTIES
                        anD Where AU_ENTITY_TYPE = 'BUG'
                        AND AP_FIELD_NAME = 'BG_USER_05'
                        AND AU_ENTITY_ID = results.BG_BUG_ID
                        AND AU_TIME = results.Latest_Change
                        AND AU_ACTION_ID = AP_ACTION_ID
                        AND AP_NEW_VALUE in ('Open', 'Fixed', 'Retest', 'Deferred')
)T
Group By date_friday,BG_USER_05

Then, I re-executed the query and was able to see the error - a syntax error on "AND". What I do when I get an error on a keyword that is used multiple times, is I start changing its case, so I can see which one is picked up in the error message. That is how I was able to find the line of the syntax error (which I have highlighted red).

What's next?

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 10:10:25

Just remove the AND clause in the text you highlited in yellow.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-25 : 10:18:46

EDIT:

I need to hit F5 first

quote:
Originally posted by Harmonygirl

Hi everyone,

I'm brand new to this forum as well as new to this level of SQL query complexity

Gotta ask...did you write this?

Do you have any sample data and what the results are suppose to look like, and have a brief business description (requirement) for it? In zero technical terms?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 10:40:09

Thanks for the reply again

When I removed that line, I got the same syntax error on the next line.

quote:
Originally posted by Idera

Just remove the AND clause in the text you highlited in yellow.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 10:43:40

quote:
Originally posted by Harmonygirl

Thanks for the reply again

When I removed that line, I got the same syntax error on the next line.

quote:
Originally posted by Idera

Just remove the AND clause in the text you highlited in yellow.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I didnt say remove the whole line.I just said remove the "AND" clause.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 10:46:47

Hi Brett,

I think I explained in my original post that I had a lot of help with writing this query, when I said, "I have this query that I have been working on for a while, with the help of others, and we are all stuck."

In any case, I'd be happy to explain the business case and expected results I am trying to achieve.

The high level explanation is that I am trying to do a query on a defect database, to extract all the defects and group by status, that exist as of a certain date interval (in this case, every Friday), for a period of history.

What I would hope to extract would be something like this:

date_friday-------Open-------Fixed-------Retest-------Deferred
5/28/2010----------2------------0-----------2------------0
6/4/2010----------10------------0-----------3------------0
6/11/2010---------10------------0-----------8------------0
6/18/2010---------10------------0-----------5------------0
6/25/2010---------38------------2-----------5------------0

Please let me know if you have any other questions. I really appreciate everyone's help and interest.

quote:
Originally posted by X002548

EDIT:

I need to hit F5 first

quote:
Originally posted by Harmonygirl

Hi everyone,

I'm brand new to this forum as well as new to this level of SQL query complexity

Gotta ask...did you write this?

Do you have any sample data and what the results are suppose to look like, and have a brief business description (requirement) for it? In zero technical terms?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 10:52:03

OK, thanks for explaining. When I did that, I got the original error message - syntax error on ")".

Here is the revised query that I tested (edited line in boldface):


Select date_friday, Count (BG_BUG_ID), BG_USER_05, Max(AU_TIME)as  Latest_Change FROM
(select distinct d_friday as date_friday, BG_USER_05, BG_BUG_ID, results_2.AU_TIME from
                                                (Select au_time,
                                                DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday
                                                FROM audit_log
                                                where DATEPART(YEAR, au_time) = '2010'
                                                )
                        results_1
                        left join
                                                (Select AU_TIME, BG_BUG_ID, BG_USER_05 from
                                                AUDIT_LOG,
                                                AUDIT_PROPERTIES,
                                                BUG
                                                Where AU_ENTITY_TYPE = 'BUG'
                                                And AU_ENTITY_ID = BG_BUG_ID
                                                And AU_ACTION_ID = AP_ACTION_ID
                                                AND AP_FIELD_NAME = 'BG_USER_05'
                                                )
                        results_2  On results_2.AU_TIME <= d_friday
) results,
                        AUDIT_LOG, AUDIT_PROPERTIES
                        Where AU_ENTITY_TYPE = 'BUG'                        
                        AND AP_FIELD_NAME = 'BG_USER_05'
                        AND AU_ENTITY_ID = results.BG_BUG_ID
                        AND AU_TIME = results.Latest_Change
                        AND AU_ACTION_ID = AP_ACTION_ID
                        AND AP_NEW_VALUE in ('Open', 'Fixed', 'Retest', 'Deferred')
)T
Group By date_friday,BG_USER_05

quote:
Originally posted by Idera

quote:
Originally posted by Harmonygirl

Thanks for the reply again

When I removed that line, I got the same syntax error on the next line.

quote:
Originally posted by Idera

Just remove the AND clause in the text you highlited in yellow.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I didnt say remove the whole line.I just said remove the "AND" clause.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 10:57:16

Can you please tell on which line number are you getting the error?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 11:03:02

I sure wish I knew

-- the error only references the ")", and I don't know how to figure out where the error is occurring.

Is there some other tool I can use to help figure that out?

quote:
Originally posted by Idera

Can you please tell on which line number are you getting the error?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-25 : 11:03:49

OK..this is good...what does the sample data look like?

These are the only tables in this..right?

AUDIT_LOG, AUDIT_PROPERTIES, BUG

Can you post the DDL of these tables and what some sample data in the looks like?

quote:
Originally posted by Harmonygirl

Hi Brett,

I think I explained in my original post that I had a lot of help with writing this query, when I said, "I have this query that I have been working on for a while, with the help of others, and we are all stuck."

In any case, I'd be happy to explain the business case and expected results I am trying to achieve.

The high level explanation is that I am trying to do a query on a defect database, to extract all the defects and group by status, that exist as of a certain date interval (in this case, every Friday), for a period of history.

What I would hope to extract would be something like this:

date_friday-------Open-------Fixed-------Retest-------Deferred
5/28/2010----------2------------0-----------2------------0
6/4/2010----------10------------0-----------3------------0
6/11/2010---------10------------0-----------8------------0
6/18/2010---------10------------0-----------5------------0
6/25/2010---------38------------2-----------5------------0

Please let me know if you have any other questions. I really appreciate everyone's help and interest.

quote:
Originally posted by X002548

EDIT:

I need to hit F5 first

quote:
Originally posted by Harmonygirl

Hi everyone,

I'm brand new to this forum as well as new to this level of SQL query complexity

Gotta ask...did you write this?

Do you have any sample data and what the results are suppose to look like, and have a brief business description (requirement) for it? In zero technical terms?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-25 : 11:04:49

Oh, and everyone...we are trying to Force a solution on an existing query...

I think this should be waaaaaaaaaay simpler

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-25 : 11:10:44

Yes, these are the only tables involved. Also, I'm not sure how to give you the DDL information - is that like the fields and table definitions?

I have some of that information in the application's help file, but it is not exportable to my knowledge.

If you let me know what information you need, I will gather and present it for you.

Oh, and I am completely open to a whole new query approach -- as long as it meets the need I have stated.

quote:
Originally posted by X002548

OK..this is good...what does the sample data look like?

These are the only tables in this..right?

AUDIT_LOG, AUDIT_PROPERTIES, BUG

Can you post the DDL of these tables and what some sample data in the looks like?

quote:
Originally posted by Harmonygirl

Hi Brett,

I think I explained in my original post that I had a lot of help with writing this query, when I said, "I have this query that I have been working on for a while, with the help of others, and we are all stuck."

In any case, I'd be happy to explain the business case and expected results I am trying to achieve.

The high level explanation is that I am trying to do a query on a defect database, to extract all the defects and group by status, that exist as of a certain date interval (in this case, every Friday), for a period of history.

What I would hope to extract would be something like this:

date_friday-------Open-------Fixed-------Retest-------Deferred
5/28/2010----------2------------0-----------2------------0
6/4/2010----------10------------0-----------3------------0
6/11/2010---------10------------0-----------8------------0
6/18/2010---------10------------0-----------5------------0
6/25/2010---------38------------2-----------5------------0

Please let me know if you have any other questions. I really appreciate everyone's help and interest.

quote:
Originally posted by X002548

EDIT:

I need to hit F5 first

quote:
Originally posted by Harmonygirl

Hi everyone,

I'm brand new to this forum as well as new to this level of SQL query complexity

Gotta ask...did you write this?

Do you have any sample data and what the results are suppose to look like, and have a brief business description (requirement) for it? In zero technical terms?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 12:58:37

quote:
Originally posted by Harmonygirl

Also, I'm not sure how to give you the DDL information - is that like the fields and table definitions?

Just follow this link

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Harmonygirl
Starting Member

30 Posts

Posted - 2010-06-28 : 09:40:36

I'm not sure I can get you all the DDL information you are requesting, especially since I cannot attach any files to my post.

I am going to paste each of the table's information below. If there is a better way to present this data, please let me know and I will repost.

AUDIT_LOG

Column Name Description Datatype Length Allow Nulls Default
AU_ACTION_ID The record ID. Integer 4
AU_FATHER_ID For backward compatibility. Field may be removed in future version. Integer 4
AU_USER The user name of the user who performed the action. VarChar 2000
AU_SESSION_ID The Quality Center session ID of the session in which the action was performed. Integer 4
AU_TIME The timestamp of the audit record according to the database server clock. DBTimeStamp 4
AU_ACTION The type of event. For example: INSERT, UPDATE, DELETE, and so on. VarChar 200
AU_ENTITY_TYPE The name of the table in which the change occurred. VarChar 200
AU_ENTITY_ID The record ID of the changed record in the table specified by AU_ENTITY_TYPE. VarChar 200 YES
AU_DESCRIPTION The audit record description. LongVarChar 16 YES

AUDIT_PROPERTIES

Column Name Description Datatype Length Allow Nulls Default
AP_PROPERTY_ID The record ID. Integer 4
AP_ACTION_ID The audit log record: AUDIT_LOG.AU_ACTION_ID Integer 4
AP_TABLE_NAME If the property change was saved in the database, the name of the table in which the change occurred. VarChar 40 YES
AP_FIELD_NAME If the property change was saved in the database, the field (column) that was changed. VarChar 40 YES
AP_PROPERTY_NAME If the property change was saved in the database, the user-defined label for the field that was changed (SYSTEM_FIELD.SF_USER_LABEL). VarChar 50 YES
If not a database field, the name of the property.
AP_OLD_VALUE The value before the change for types other than timestamp or large object fields. VarChar 2000 YES
If there is no previous value, or if timestamp or large object, NULL.
AP_NEW_VALUE The value resulting from the change for types other than timestamp or large object fields. VarChar 2000 YES
NULL for timestamp and large object.
AP_OLD_LONG_VALUE The value before the change for large object fields. LongVarChar 16 YES
If there is no previous value or if not a large object, NULL.
AP_NEW_LONG_VALUE The value resulting from the change for large object fields, such as memo fields. LongVarChar 16 YES
If not a large object, NULL.
AP_OLD_DATE_VALUE The value before the change for timestamp fields. DBTimeStamp 4 YES
If there is no previous value or if not a timestamp , NULL.
AP_NEW_DATE_VALUE The value resulting from the change for timestamp fields. DBTimeStamp 4 YES
If not a timestamp , NULL.

BUG

Column Name Description Datatype Length Allow Nulls Default
BG_BUG_ID The defect record ID. Integer 4
BG_STATUS The current status of the defect. VarChar 70 YES
The values are from the Bug Status custom list. Typical values are: Open, Fixed, Closed, New, Rejected, and Reopen.
BG_RESPONSIBLE The name of the developer responsible for fixing the defect. VarChar 60 YES
BG_PROJECT The name of the project in which the defect was found. VarChar 70 YES
The values are from the All Projects custom list.
BG_SUBJECT The subject in the test plan tree to which the bug is related: ALL_LISTS.AL_ITEM_ID. Integer 4 YES
BG_SUMMARY A summary of the defect. VarChar 255 YES
BG_DESCRIPTION A description of the defect. LongVarChar 16 YES
BG_DEV_COMMENTS Comments about the defect, usually by the developer responsible for the defect. LongVarChar 16 YES
BG_REPRODUCIBLE Indicates whether the tester was able to reproduce the defect. VarChar 1 YES
BG_SEVERITY The severity level of the defect. VarChar 70 YES
The values are from the Severity custom list. YES
Typical values are: 1-Low, 2-Medium, 3-High, 4-Very High, 5-Urgent. YES
BG_PRIORITY The priority level of the defect. VarChar 70 YES
The values are from the Priority custom list. YES
Typical values are: 1-Low, 2-Medium, 3-High, 4-Very High, 5-Urgent. YES
BG_DETECTED_BY The user name of the tester who found the defect. VarChar 60 YES
BG_DETECTION_DATE The date the defect was found. DBTimeStamp 4 YES
BG_DETECTION_VERSION The version in which the defect was detected. VarChar 70 YES
BG_PLANNED_CLOSING_VER The version in which the developer estimates the defect will be closed. VarChar 70 YES
BG_ESTIMATED_FIX_TIME The number of days the developer estimates will be required to fix the defect. Integer 4 YES
BG_ACTUAL_FIX_TIME The number of days worked to fix the defect. Integer 4 YES
BG_CLOSING_DATE The date the defect record was closed. DBTimeStamp 4 YES
BG_CLOSING_VERSION The version in which the defect record was closed. VarChar 70 YES
BG_TO_MAIL Indicates whether a defect report should be mailed to users registered to receive such reports. VarChar 1 YES
BG_ATTACHMENT Indicates whether the defect record has any attachments. VarChar 1 YES
The value of this field can be either Y or N.
BG_USER_01 User-defined field. VarChar 40 YES
BG_USER_02 User-defined field. VarChar 40 YES
BG_USER_03 User-defined field. VarChar 40 YES
BG_USER_04 User-defined field. VarChar 40 YES
BG_USER_05 User-defined field. VarChar 40 YES
BG_USER_06 User-defined field. VarChar 40 YES
BG_USER_07 User-defined field. VarChar 40 YES
BG_USER_08 User-defined field. VarChar 40 YES
BG_USER_09 User-defined field. VarChar 40 YES
BG_USER_10 User-defined field. VarChar 40 YES
BG_USER_11 User-defined field. VarChar 40 YES
BG_USER_12 User-defined field. VarChar 40 YES
BG_USER_13 User-defined field. VarChar 40 YES
BG_USER_14 User-defined field. VarChar 40 YES
BG_USER_15 User-defined field. VarChar 40 YES
BG_USER_16 User-defined field. VarChar 40 YES
BG_USER_17 User-defined field. VarChar 40 YES
BG_USER_18 User-defined field. VarChar 40 YES
BG_USER_19 User-defined field. VarChar 40 YES
BG_USER_20 User-defined field. VarChar 40 YES
BG_USER_21 User-defined field. VarChar 40 YES
BG_USER_22 User-defined field. VarChar 40 YES
BG_USER_23 User-defined field. VarChar 40 YES
BG_USER_24 User-defined field. VarChar 40 YES
BG_USER_HR_01 BG_USER_HR_01 to BG_USER_HR_06 are for backward compatibility. Fields may be removed in future version. Integer 4 YES
BG_BUG_VER_STAMP A number indicating the revision number of this record. Increments each time a change is made. Integer 4 YES
BG_HAS_CHANGE For backward compatibility. VarChar 50 YES
BG_VTS The version time stamp. Indicates the time this record was last changed according to the database server. VarChar 20 YES
BG_EXTENDED_REFERENCE For internal use. Do not change data. VarChar 50 YES
BG_REQUEST_ID The ITG Request ID. This field is used internally by ITG to link ITG requests to defects. Integer 4 YES
BG_TEXT_SYNC For internal use. Do not change data. VarChar 1 YES
BG_REQUEST_SERVER The URL of the ITG server. This field is used internally by ITG. VarChar 120 YES
BG_REQUEST_TYPE The ITG request type. This field is used internally by ITG. VarChar 120 YES
BG_REQUEST_NOTE The notes added to the linked request in ITG. LongVarChar 16 YES
BG_CYCLE_ID For backward compatibility. Integer 4 YES
BG_TEST_REFERENCE For backward compatibility. Integer 4 YES
BG_CYCLE_REFERENCE For backward compatibility. VarChar 255 YES
BG_RUN_REFERENCE For backward compatibility. Integer 4 YES
BG_STEP_REFERENCE For backward compatibility. Integer 4 YES
BG_DETECTED_IN_REL The RELEASES.REL_ID of the release in which the defect was detected. Integer 4 YES
BG_DETECTED_IN_RCYC The RELEASE_CYCLES.RCYC_ID of the release cycle in which the defect was detected. Integer 4 YES
BG_TARGET_REL The RELEASES.REL_ID of the release in which the defect is planned to be fixed. Integer 4 YES
BG_TARGET_RCYC The RELEASE_CYCLES.RCYC_ID of the release cycle in which the defect is planned to be fixed. Integer 4 YES

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 10:15:51

What version is this?

Do you use Management Studio to do SQL Development, or something else?

The GUI would allow you to script the DDL

In any case, you gave us something like DDL, and what you wantt the output to look like, now give us sample data that would be in thos table I mentioned...just use the fields need in your query

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources