October 31, 2005 at 7:02 am
I have the following query:
select linecost, issuetype, gldebitacct, glcreditacct
from matusetrans
where it1 <> 'Y' or it1 is null
and issuetype in ('ISSUE', 'RETURN')
and storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP'
For each record returned I would Like to add a column named RECTYPE with a default value of "L". In addition, I would also like to add a column named JEENTRYNO with values that start at 1 and increase by one for each row returned. I don't want to keep the information in a table but output this information to a csv file for use somewhere else. Can someone help me with the stored procedure syntax necessary to perform this action?
Thanks,
Dave
October 31, 2005 at 7:13 am
You can use a temp table or a table variable to get the running number. The default value of L can be obtained by just having a static string. Then, you can select out of that temp table or the table variable and put it into the csv file using bcp/DTS etc., example:
select identity(int, 1, 1) as JEENTRYNO, linecost, issuetype, gldebitacct, glcreditacct, 'L' as RECTYPE
into #temp
from matusetrans
where it1 <> 'Y' or it1 is null
and issuetype in ('ISSUE', 'RETURN')
and storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
order by issuetype, storeloc
go
select * from #temp
If you are going to have a lot of records and are going to run this very frequently, then you can create the temp table up-front rather than using the select...into...clause since this clause has some locking issues.
October 31, 2005 at 7:22 am
That did the trick. Thanks for the help.
Dave
October 31, 2005 at 10:56 pm
Be careful with WHERE clauses that combine OR and AND, I suggest always adding () around the ORed clause, like:
WHERE (it1 <> 'Y' OR it1 is null)
AND issuetype IN ('ISSUE', 'RETURN')
AND storeloc IN ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
Unless you expect this to work like:
WHERE it1 <> 'Y' OR (it1 is null
AND issuetype IN ('ISSUE', 'RETURN')
AND storeloc IN ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP'))
Also SELECT INTO is very slow, especially with temp tables, better to do a CREATE TABLE / INSERT INTO SELECT.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply