June 19, 2014 at 6:18 am
I have tested you code, it returns more than one row and then the B_L_SEQ_Number doesn't increment. That's why I wrote mine to return only one at a time, so I can be sure that the B_L_SEQ_Number increments
June 19, 2014 at 6:28 am
crazy_new (6/19/2014)
I'll try this code now thanks...If I may ask a stupid question that would make future queries easier for everyone, how do I post a block with the code in like you just did?
while posting a reply/question, on the left side there is a section with many option
so if you want to post like the one i did you need to use Code="SQL".
just click on it, you will find a tag on the editor copy and paste all your sql code in it.
June 19, 2014 at 6:31 am
crazy_new (6/19/2014)
I have tested you code, it returns more than one row and then the B_L_SEQ_Number doesn't increment. That's why I wrote mine to return only one at a time, so I can be sure that the B_L_SEQ_Number increments
just put a Case_ID where clause on the end of the select statement, comment the insert into line
and check what result you get, i have just given you the query for all the possible Case_ID, which Cat_Transactio.ID is not been inserted in to the Budget_Line.
if all you want to test it put a where in the end you can, as per the increment logic , if a case never been entered to budget_Line table, then the line number will be default 1.
hope it helps
June 19, 2014 at 6:45 am
Yeah but it needs to increment according to case number, so It wont start at one, it will start at the max seq number in there and add one. That's what makes this difficult
June 19, 2014 at 7:01 am
i used the same query which you originally shared like this
SET @LineNumber = (
SELECT
ISNULL(MAX(B_L_SEQ_NUMBER),0)
FROM
Patricia.dbo.BUDGET_LINE
WHERE
CASE_ID = 121
)
SET @LineNumber = @LineNumber + 1
i have used the same logic, if CASE_ID is not available than start with '1'.
Select MAX(B_L_SEQ_NUMBER) + 1 AS B_L_SEQ_NUMBER
from Patricia.dbo.BUDGET_LINE bl
where ct.CASE_ID = bl.CASE_ID
in case this return NULL, in the select statement i have use ISNULL(B_L_SEQ_NUMBER,1).
case logic nothing changed.
June 19, 2014 at 7:35 am
Yeah that's exactly how I have it. That's why I only use one record at a time. If I select more, it gives all of them the same increment, for example. If there are 10 records that haven't been moved to the Budget line table(and has the same case id) and the last SEQ number is 20, then all ten records that haven't been inserted yet will have a SEQ number of 21, that's why I'm moving them one by one. That way it ensures the right increment. If you have any other suggestions please share, because i'm all out. The only problem doing it the way i'm doing it now, is that i'm hard coding the case id, but that's not going to work.
June 19, 2014 at 7:44 am
Ok, 6 steps back...
What is the actual problem you are trying to solve here, what are you trying to do?
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
June 19, 2014 at 7:52 am
We hired people to develop a billing system for us. They can't get our Case id imported that's why I'm using a random one to test with. When a user does a transaction it writes into their DB. Im trying to get it back from their db into our db(Budget Line table). As soon as a transaction comes in, it needs to insert it back into the budget line table and increment the seq number per case id, so that every case has all its transactions together. This is a stored proc I wrote that I want to run and move the data back. Im doing it one by one so that the increments work out right.
June 19, 2014 at 7:55 am
Post the table defs, post the stored proc, post some sample data and describe exactly what you want to be inserted into the other table
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
June 19, 2014 at 8:00 am
crazy_new (6/19/2014)
Yeah that's exactly how I have it. That's why I only use one record at a time. If I select more, it gives all of them the same increment, for example. If there are 10 records that haven't been moved to the Budget line table(and has the same case id) and the last SEQ number is 20, then all ten records that haven't been inserted yet will have a SEQ number of 21, that's why I'm moving them one by one. That way it ensures the right increment. If you have any other suggestions please share, because i'm all out. The only problem doing it the way i'm doing it now, is that i'm hard coding the case id, but that's not going to work.
if you have stated this issue before, your problem might have been resolved now. this should have been your actual problem as i see it.
This will solve your above mentioned problem.
DECLARE
@ChargeingID SMALLINT
--@LineNumber SMALLINT,
--@CaseID SMALLINT,
INSERT INTO Patricia.dbo.BUDGET_LINE
SELECT
ISNULL(xSeqNo.B_L_SEQ_NUMBER,0)
+ Row_Number Over (Partition by ct.CASE_ID Order by ct.CASE_ID) AS B_L_SEQ_NUMBER,
ctt.WorkCodeID,
ctde.[pagecount],
null, --unit price
cv.name,
null, --offer number
null, --amount (need a price)
null, --invoice number
ct.Case_ID,
@ChargeingID, --need id to pass into variable
null, --b_l_is_external
null, --external_invoice_id
null, --external_invoice_date
null, --bl_org_quantity
null, --bl_org_unit_price
null, --bl_org_amount
null, --bl_org_journal_no_time_reg
null, --bl_advance_number
null, --bl_advance_date
0, --show time comment
null, --discount_prec
null, --discount_amount
null, --bl_unit_price_no_discount
0, --invoice preview
null, --currency_id
null, --exchange_rate
null, --collective_inv_log_id
null, --bl_comment
cv.name,
null, --earliest_invoice_date
null, --pl_org_unit_price
null, --pl_org_unit_price_no_discount
null, --pl_org_amount
null, --plorg_currency_id
'CH', --indicator
null, --purchase_order_number
null, --is_purchase
ct.creation, --recorded_date
ct.id --ext_reference
FROM cat_transaction ct
JOIN cat_validation cv ON cv.id = ct.chargeid
JOIN cat_trxtype ctt ON ctt.trxtype = ct.trxtype
JOIN cas_trx_doc_ext ctde ON ctde.x_id = ct.id
Cross Apply
(
Select MAX(B_L_SEQ_NUMBER) AS B_L_SEQ_NUMBER
from Patricia.dbo.BUDGET_LINE bl
where ct.CASE_ID = bl.CASE_ID
) xSeqNo
WHERE NOT EXISTS ( Select 1
from Patricia.dbo.BUDGET_LINE bl
where bl.EXT_REFERENCE = ct.id
and ct.CASE_ID = bl.CASE_ID
)
hope it helps
June 27, 2014 at 3:36 am
Hey guys, sorry I've been quiet but I'm in hospital. As soon as I'm better I can start testing again. Thanks a lot
June 27, 2014 at 4:09 am
get well soon.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply