October 16, 2009 at 9:12 am
Sorry, I didn't see the from sys.tables, let me try that!
October 16, 2009 at 9:15 am
jordon.shaw (10/16/2009)
Sorry, I didn't see the from sys.tables, let me try that!
No, no, no, no, no, no!!!!
That was just an example of how the syntax works. See how I did the row number in that query? Do the row number in your query the same way. I just used sys.tables as a convenient example. You aren't querying sys.tables. You're querying the same table you were before.
Like this. (and, btw, your query is way over-complex, there's no need whatsoever for subqueries. The errors were because columns in a subquery weren't aliased)
declare @unitid varchar(16),
@assetindex int,
@assetidsuf smallint,
@shrtname char(15),
@assetdesc varchar(41),
@masterassetid char(19),
@structureid char(31),
@assetclassid char(15),
@locatnid char(15),
@acqdate datetime,
@cost float,
@assettype smallint,
@assetstatus smallint,
@proptype smallint,
@assetqty int,
@assetbegqty int,
@assetcurrmaint numeric(19,5),
@assetytdmaint numeric(19,5),
@assetltdmaint numeric(19,5),
@lastmaintdate datetime,
@assessedvalue numeric,
@mfgrname char(25),
@serlnmbr char(21),
@modelnumber char(21),
@warrentydate datetime,
@custodian char(25),
@noteindex numeric(19,5),
@dateadded datetime,
@deletedate datetime,
@location_id char(15),
@assetlabel char(19),
@verifieddate datetime,
@pin char(15),
@lastpurchline int,
@lastmntdate datetime,
@lastmnttime datetime,
@lastnmtuser char(15)
SELECT @assetclassid = GPID
FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
SELECT
MaxAssetIndex + ROW_NUMBER() OVER(ORDER BY SELECT(1)) AS RowNum
,UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM INFOR.TRAIN.IMSV7.COMPVEH
LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
CROSS JOIN (
SELECT MAX(assetindex) AS MaxAssetIndex
FROM FTEST.dbo.FA00100 WITH (UPDLOCK)
) R2
WHERE FA00100.ASSETID IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 9:19 am
Ok, so when I do that, then I get all those no column was specified. I'm very sorry to be such trouble. I'm trying to learn and get this fixed at the same time, so please realize that I'm truly greatful to all of you that have helped me. This is my current code:
declare @unitid varchar(16),
@assetindex int,
@assetidsuf smallint,
@shrtname char(15),
@assetdesc varchar(41),
@masterassetid char(19),
@structureid char(31),
@assetclassid char(15),
@locatnid char(15),
@acqdate datetime,
@cost float,
@assettype smallint,
@assetstatus smallint,
@proptype smallint,
@assetqty int,
@assetbegqty int,
@assetcurrmaint numeric(19,5),
@assetytdmaint numeric(19,5),
@assetltdmaint numeric(19,5),
@lastmaintdate datetime,
@assessedvalue numeric,
@mfgrname char(25),
@serlnmbr char(21),
@modelnumber char(21),
@warrentydate datetime,
@custodian char(25),
@noteindex numeric(19,5),
@dateadded datetime,
@deletedate datetime,
@location_id char(15),
@assetlabel char(19),
@verifieddate datetime,
@pin char(15),
@lastpurchline int,
@lastmntdate datetime,
@lastmnttime datetime,
@lastnmtuser char(15)
SELECT @assetclassid = GPID
FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
SELECT
D2.assetindex + D1.RowNum,
UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
,UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM INFOR.TRAIN.IMSV7.COMPVEH
LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
) D1
CROSS JOIN
(
SELECT MAX(assetindex) AS assetindex
FROM FTEST.dbo.FA00100 WITH (UPDLOCK)
) D2
With this code, I'm getting the errors that I pointed out on the last page, saying no column was specified.
October 16, 2009 at 9:25 am
Sorry, I was selecting D1.RowNum at the top and then D1.RowNo at the bottom; however, when I fixed that, I still have the same results. Once again, I'm so sorry for being a pain, I'm just lost.
October 16, 2009 at 9:28 am
jordon.shaw (10/16/2009)
With this code, I'm getting the errors that I pointed out on the last page, saying no column was specified.
See my edited post above. I rewrote the query for you in a form that will work.
The errors are because you have columns in a subquery that aren't named. But then, there's no need for subqueries here at all, so easy to fix.
As a very simple example just to show what's happening.
-- This will give the "no column named" error
DECLARE @dt DATETIME
SET @dt = GetDate()
SELECT name, @dt
FROM
( SELECT NAME, @dt
FROM sys.tables
) Sub
-- This will not give the error, because I've aliased (named) the column inside the subquery and referenced that name in the outer query
DECLARE @dt DATETIME
SET @dt = GetDate()
SELECT name, CurrentDate
FROM
( SELECT NAME, @dt AS CurrentDate
FROM sys.tables
) Sub
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 9:28 am
Each of your @variables inside of the CTE need to be labeled.
so, for example:
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
,UNITID,
@assetidsuf,
@shrtname,
etc....
needs to change to
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
,UNITID,
@assetidsuf as assetidsuf ,
@shrtname as shrtname,
etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2009 at 9:33 am
GOD BLESS YOU ALL!!! It finally works!!! This is the code that I used, thanks to Gail!!!
declare @unitid varchar(16),
@assetindex int,
@assetidsuf smallint,
@shrtname char(15),
@assetdesc varchar(41),
@masterassetid char(19),
@structureid char(31),
@assetclassid char(15),
@locatnid char(15),
@acqdate datetime,
@cost float,
@assettype smallint,
@assetstatus smallint,
@proptype smallint,
@assetqty int,
@assetbegqty int,
@assetcurrmaint numeric(19,5),
@assetytdmaint numeric(19,5),
@assetltdmaint numeric(19,5),
@lastmaintdate datetime,
@assessedvalue numeric,
@mfgrname char(25),
@serlnmbr char(21),
@modelnumber char(21),
@warrentydate datetime,
@custodian char(25),
@noteindex numeric(19,5),
@dateadded datetime,
@deletedate datetime,
@location_id char(15),
@assetlabel char(19),
@verifieddate datetime,
@pin char(15),
@lastpurchline int,
@lastmntdate datetime,
@lastmnttime datetime,
@lastnmtuser char(15)
SELECT @assetclassid = GPID
FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
SELECT
R2.assetindex + ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum
,UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM INFOR.TRAIN.IMSV7.COMPVEH
LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
CROSS JOIN (
SELECT MAX(assetindex) AS assetindex
FROM FTEST.dbo.FA00100 WITH (UPDLOCK)
) R2
WHERE FA00100.ASSETID IS NULL
October 16, 2009 at 9:42 am
jordon.shaw (10/16/2009)
This project is almost becoming more trouble than its worth, but the boss wants it done.
May I politely suggest that you (politely) point out to your boss that this could have been done faster and with a lot less stress if he could organise some SQL training for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 9:46 am
Completely understand; however, with the economy, there is no training money this year!
October 16, 2009 at 10:05 am
jordon.shaw (10/15/2009)
I know that I said I would leave y'all alone, but I lied. Here is a issue, if you look, I'm setting a varible of assetindex to what is currently there and adding one, see this line: SELECT @assetindex = MAX(ASSETINDEX)+1 FROM FA00100. With that being said, it only works for one of these records, since I'm not looping, its not adding one for each record. Suggestion? How do I make this add 1 to ASSETINDEX for each record using the insert statement the way y'all have suggested?Thanks,
Jordon
What are you using this column for? Is it being used as the clustered index key - primary key?
If you can change that column to an identity, you could then leave it out of the insert and SQL Server will generate a number for you. If you cannot do that, then there are several other ways you can generate the number.
If you have to generate the number yourself, then what you really need to identify is what uniquely identifies that row outside of this column. Using those columns (I am making an assumption here), you could use either a tally table or row_number() to generate the number over the columns that uniquely identify the row.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 16, 2009 at 10:17 am
jordon.shaw (10/16/2009)
Completely understand; however, with the economy, there is no training money this year!
The year's almost over, and it's sure to generate an ROI in the form of productivity. Still worth a talk.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 17, 2009 at 11:47 pm
jordon.shaw (10/16/2009)
Completely understand; however, with the economy, there is no training money this year!
Is there money for recovering corrupt databases, application downtime and lost data?
I don't mean to impugn your skills jordan, but seriously, you are trying to juggle knives here, without having learned how to juggle oranges first, and there's only so much help that we can give you over the phone. (so to speak) And in my experience training is a lot less expensive than the consequences of insufficient training. At least get it in the budget for early next year, as others have suggested.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply