Cursor or Temp Files?

Question

Post reply

Cursor or Temp Files?

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 1

Thank you very much for you help and I understand. Sorry for my ignorance.

DECLARE @TempCOGGP TABLE

(conum varchar(10),

coline varchar(10),

itemnum varchar(35),

custnum varchar(10),

custseq int,

qtyorder decimal (10,8),

qtyready decimal (10,8),

qtyship decimal (10,8),

promise date,

ship date,

orderdate date,

coitemstat varchar(3),

allocate date,

pattern varchar(3),

room varchar(1),

Tag varchar(1)

)

/********** */

insert into @TempCOGGP

SELECT coitem.co_num,

coitem.co_line,

coitem.item,

coitem.cust_num,

coitem.cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

coitem.ship_date,

co.order_date,

coitem.stat,

coitem.Uf_PJ_AllocatedDate as AvailableDate,

@Pattern as pattern,

@Room as room,

@tag

FROM coitem

inner join co on co.co_num = coitem.co_num

--inner join customer on customer.cust_num = coitem.cust_num and customer.cust_seq = coitem.cust_seq

Where (coitem.promise_date >= @StartInvoiceDate and coitem.promise_date <=

@EndInvoiceDate) and (substring (coitem.item,1,3) = @Pattern and

SUBSTRING (coitem.item, 6,1) = @Room)

ORDER BY coitem.co_num, coitem.co_line

select * from @TempCOGGP

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 2

Thank you for your explanation and link. I will will be reading this tonight and hope I can get it to work sometime tomorrow.

I really appreciate you help and patience.

Sincerely.

Gillian

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 3

Hi Kurt.

I hope you had a nice weekend. I read up on the cte and tried to apply your suggestions over the weekend. I keep getting compile errors on the ';with', and the ending, 'O_Orders_CTE (co_num,' Any suggestions?

/***************** START CTE *********/

;with C_ORDERS_CTE (

co_num,

co_line,

item,

cust_num,

cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

ship_date,

order_date,

stat,

Uf_PJ_AllocatedDate, ---as AvailableDate,

@Pattern, ---as pattern,

@Room, ---as room,

@tag)

AS

(

SELECT coitem.co_num,

coitem.co_line,

coitem.item,

coitem.cust_num,

coitem.cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

coitem.ship_date,

co.order_date,

coitem.stat,

coitem.Uf_PJ_AllocatedDate as AvailableDate,

@Pattern as pattern,

@Room as room,

@tag

FROM coitem

inner join co on co.co_num = coitem.co_num

--inner join customer on customer.cust_num = coitem.cust_num and customer.cust_seq = coitem.cust_seq

Where (coitem.promise_date >= @StartInvoiceDate and coitem.promise_date <=

@EndInvoiceDate) and (substring (coitem.item,1,3) = @Pattern and

SUBSTRING (coitem.item, 6,1) = @Room) and coitem.stat = 'C'

), O_Orders_CTE (co_num,

co_line,

coitem.item,

coitem.cust_num,

coitem.cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

coitem.ship_date,

co.order_date,

coitem.stat,

coitem.Uf_PJ_AllocatedDate as AvailableDate,

@Pattern as pattern,

@Room as room,

@tag

)

AS

(

SELECT coitem.co_num,

coitem.co_line,

coitem.item,

coitem.cust_num,

coitem.cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

coitem.ship_date,

co.order_date,

coitem.stat,

coitem.Uf_PJ_AllocatedDate as AvailableDate,

@Pattern as pattern,

@Room as room,

@tag

FROM coitem

inner join co on co.co_num = coitem.co_num

--inner join customer on customer.cust_num = coitem.cust_num and customer.cust_seq = coitem.cust_seq

Where (coitem.promise_date >= @StartInvoiceDate and coitem.promise_date <=

@EndInvoiceDate) and (substring (coitem.item,1,3) = @Pattern and

SUBSTRING (coitem.item, 6,1) = @Room) and coitem.stat = 'O'

)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

On your CTE declaration you have @Pattern, @Room, & @tag. I'd change them to Pattern, Room, & Tag.

;with C_ORDERS_CTE (

co_num,

co_line,

item,

cust_num,

cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

ship_date,

order_date,

stat,

Uf_PJ_AllocatedDate, ---as AvailableDate,

@Pattern, ---as pattern,

@Room, ---as room,

@tag)

change to

;with C_ORDERS_CTE (

co_num,

co_line,

item,

cust_num,

cust_seq,

qty_ordered,

qty_ready,

qty_shipped,

promise_date,

ship_date,

order_date,

stat,

Uf_PJ_AllocatedDate, ---as AvailableDate,

Pattern, ---as pattern,

Room, ---as room,

Tag)

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 5

Gillian_Pappas2002

SSCommitted

Points: 1714

December 16, 2013 at 12:07 pm

#1673835

Thank you Kurt. I appreciate you help very much.

Gillian

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

Status = 'O' means "Open"???

Status = 'C' means "Closed"???

Are there any other statuses than just 'O' or 'C'????

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 7

Hi There.

Yes, there can be other statuses, but I am just looking for each customer order that may have 1 to n customer order lines, and that the line statuses ALL must be = C. I think I got it to work just now by using aliases. It tests correctly now. I never did get that cte code to work right.

Thanks.

Gillian

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 8

Gillian_Pappas2002 (12/16/2013)
Hi There.
Yes, there can be other statuses, but I am just looking for each customer order that may have 1 to n customer order lines, and that the line statuses ALL must be = C. I think I got it to work just now by using aliases. It tests correctly now. I never did get that cte code to work right.
Thanks.
Gillian

So we're not actually looking to disqualify a customer if they have an "O" status... just to replay what you said above, we're actually looking for only those customers that have all "C" status. ANY OTHER status will act as a disqualifier for the customer, not just those with an "O" status, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 9

Pending a reply, if the answer to my question is "Yes", then the code can be greatly simplified (change the * to the column names you desire and add any joins you desire to the outer query).

--===== Create and populate a test table on-the-fly.

-- This is NOT a part of the solution. We're just building a test table here.

SELECT *

INTO #TestTable

FROM (

SELECT '905055' , 1,'222-63-05' ,'C' UNION ALL

SELECT '905055' , 2,'222-63-47' ,'C' UNION ALL

SELECT '905055' , 3,'222-63-80' ,'C' UNION ALL

SELECT '905055' , 4,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000253', 1,'222-63-147','C' UNION ALL

SELECT 'AW00000253', 2,'222-63-247','C' UNION ALL

SELECT 'AW00000306', 2,'222-23-31' ,'O' UNION ALL

SELECT 'AW00000306',10,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000306',12,'222-23-47' ,'O' UNION ALL

SELECT 'SF00000389', 1,'222-23-11' ,'C' UNION ALL

SELECT 'SF00000390', 2,'222-23-11' ,'C'

) tt (CoNum, CoLine, ItemNum, CoItemStat)

;

--===== Create an index to boost performance

CREATE INDEX IX_TestTable_CoNum_CoItemStat

ON #TestTable (CoNum, CoItemStat)

;

--===== Return only those lines where all the CoItemStat's = 'C' for each CoNum

SELECT *

FROM #TestTable

WHERE CoNum NOT IN (SELECT CoNum FROM #TestTable WHERE CoItemStat <> 'C')

;

Results...

CoNum CoLine ItemNum CoItemStat

---------- ----------- ---------- ----------

AW00000253 1 222-63-147 C

AW00000253 2 222-63-247 C

SF00000389 1 222-23-11 C

SF00000390 2 222-23-11 C

(4 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 10

Gillian_Pappas2002

SSCommitted

Points: 1714

December 16, 2013 at 4:50 pm

#1673912

That is spot on Sir.

Thanks.

Gillian

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 11

Perfect. That also makes the next logical permutation of adding another status to the acceptable statuses super easy. For example, if someone later decides that the status of 'C' and or 'T' is acceptable, then a very small change is all that is needed.

DROP TABLE #TestTable

--===== Create and populate a test table on-the-fly.

-- This is NOT a part of the solution. We're just building a test table here.

SELECT *

INTO #TestTable

FROM (

SELECT '905055' , 1,'222-63-05' ,'C' UNION ALL

SELECT '905055' , 2,'222-63-47' ,'C' UNION ALL

SELECT '905055' , 3,'222-63-80' ,'C' UNION ALL

SELECT '905055' , 4,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000253', 1,'222-63-147','C' UNION ALL

SELECT 'AW00000253', 2,'222-63-247','C' UNION ALL

SELECT 'AW00000306', 2,'222-23-31' ,'O' UNION ALL

SELECT 'AW00000306',10,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000306',12,'222-23-47' ,'O' UNION ALL

SELECT 'SF00000389', 1,'222-23-11' ,'C' UNION ALL

SELECT 'SF00000390', 2,'222-23-11' ,'C' UNION ALL

SELECT 'MI12345678', 1,'AAA-BB-CC' ,'C' UNION ALL --Added data here down

SELECT 'MI12345678', 2,'AAA-BB-CC' ,'T' UNION ALL

SELECT 'MI12345678', 3,'AAA-BB-CC' ,'A' UNION ALL

SELECT 'RI87654321', 1,'AAA-BB-DD' ,'C' UNION ALL

SELECT 'RI87654321', 2,'AAA-BB-DD' ,'T' UNION ALL

SELECT 'CA99999999', 2,'AAA-BB-EE' ,'T'

) tt (CoNum, CoLine, ItemNum, CoItemStat)

;

--===== Create an index to boost performance

CREATE INDEX IX_TestTable_CoNum_CoItemStat

ON #TestTable (CoNum, CoItemStat)

;

--===== Return only those lines where all the CoItemStat's = 'C' or 'T' for each CoNum

-- Notice the second "NOT IN"

SELECT *

FROM #TestTable

WHERE CoNum NOT IN (SELECT CoNum FROM #TestTable WHERE CoItemStat NOT IN ('C','T'))

;

Results...

CoNum CoLine ItemNum CoItemStat

---------- ----------- ---------- ----------

AW00000253 1 222-63-147 C

AW00000253 2 222-63-247 C

SF00000389 1 222-23-11 C

SF00000390 2 222-23-11 C

RI87654321 1 AAA-BB-DD C

RI87654321 2 AAA-BB-DD T

CA99999999 2 AAA-BB-EE T

(7 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 12

Thanks very much Jeff. I appreciate all of your help.

Gillian

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 13

You're welcome. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)