October 29, 2009 at 5:36 pm
I have a query where I hard code account numbers in the where clause, see below...
Select 'Sept'as mnth,td.tdes_m,tran_x,t.tran_a
from focis.tdes td
join focis.trans t on td.tdes_c = t.tdes_c
where t.pitm_c = '123107004999100'
and tran_a <> 0
or (tran_x like '%12-3107-0049991-00%'
and pitm_c <> '123107004999100')
I have create a table called FoodStuffsAccounts with 2 columns (FS_pitm and FS_pitmlike), it has 218 records.
The columns contain the following, here is the top 5:
FS_pitm FS_pitmlike
123097013403251 '%12-3097-0134032-51%'
123097013403258 '%12-3097-0134032-58%'
123046018749200 '%12-3046-0187492-00%'
123046018749250 '%12-3046-0187492-50%'
123046018749266 '%12-3046-0187492-66%'
What I want to do is use this table in the query above to insert into a new table. So instead of hard coding into the where clause I want to loop through FoodStuffsAccounts and use each record in the query.
I think a Cursor could be the technique but maybe I can do it differently, any suggestions welcome.
October 29, 2009 at 5:48 pm
this is just a guess, but i think this would return a result set for all of your numbers, so you can avoid a cursor.
Select
'Sept'as mnth,
td.tdes_m,
tran_x,
t.tran_a
from focis.tdes td
inner join focis.trans t
on td.tdes_c = t.tdes_c
left outer join FoodStuffsAccounts
on t.pitm_c = FoodStuffsAccounts.FS_pitm
-- and tran_x = FoodStuffsAccounts.FS_pitmlike
WHERE t.tran_a <> 0
your WHERE statement is really confusing...based on your data, wouldn't this ALWAYS be true?
where t.pitm_c = '123107004999100' --it either has a value in FoodStuffAccounts or it doesnt, right?
and tran_a <> 0 --don't know what this one means...a status of some kind?
why would your t.pitm_c not be exactly the same as the version formatted with dashes? whyy the OR
or (tran_x like '%12-3107-0049991-00%'
and pitm_c <> '123107004999100')
Lowell
October 29, 2009 at 6:49 pm
I agree, the where clause can be confusing.
The first part t.pitm_c = '123107004999100' is selecting all tranasctions where the account number is '123107004999100'. This will be transactions that are directly deposited or withdrawen from this account.
The second part looks for transactions that are channelled through another account but relate to '123107004999100'. The like clause is looking at the transaction description and extracting the account the transaction relates to. So this might be a holding or suspence account for example.
Does that make sense?
October 29, 2009 at 7:08 pm
I am trying to get this cursor to work for the second part of the where clause....
or (tran_x like '%12-3107-0049991-00%'
and pitm_c <> '123107004999100')
It isn't inserting any rows....
DECLARE FST_C CURSOR FOR
SELECTtop 5
FS_pitm
,FS_pitmLike
FROM FoodStuffsAccounts
------------------------------------------------------------------------------
DECLARE @Pitmlike varchar(50),
@Pitm varchar(20)
OPEN FST_C
while 0 = 0
BEGIN
FETCH NEXT FROM FST_C INTO
@Pitm,@Pitmlike
IF @@fetch_status <> 0 break
BEGIN
Insert into FSTransactions
Select
t.pitm_c
,td.tdes_m
,t.tran_x
,t.tran_a
from focis.tdes td
join focis.trans t on td.tdes_c = t.tdes_c
where tran_a <> 0
and tran_x like @Pitmlike
and pitm_c <> @Pitm
END
CLOSE FST_C
DEALLOCATE FST_C
October 29, 2009 at 9:54 pm
Unfortunately, since you haven't provided the DDL for the tables, sample data in a readily consummbale format, or expected results based on the sample data, this is all I could come up with and it is untested:
Select
'Sept'as mnth,
td.tdes_m,
tran_x,
t.tran_a
from
focis.tdes td
inner join focis.trans t
on td.tdes_c = t.tdes_c
inner join FoodStuffsAccounts fsa
on (t.pitm_c = fsa.FS_pitm
or tran_x like fsa.FS_pitmlike )
WHERE
t.tran_a <> 0
October 30, 2009 at 5:31 am
ok, if things run under a default account like you said, this is my next guess: does it return the correct results?
Select
'Sept'as mnth,
td.tdes_m,
tran_x,
t.tran_a
from focis.tdes td
inner join focis.trans t
on td.tdes_c = t.tdes_c
left outer join FoodStuffsAccounts
on (t.pitm_c = FoodStuffsAccounts.FS_pitm
and tran_a <> 0)
OR (tran_x = FoodStuffsAccounts.FS_pitmlike
and pitm_c <> '123107004999100')
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply