sql query

[rds747]

if the query shows results like:
Customer 1 1234 ABC
Customer 2 4567 DEF
Customer 1 1234 GHI
Customer 1 1234 JKL
Customer 1 1234 MNO

The query above could be Select CustomerName, CustomerNo, PurchaseOrd from Table1

If 1234 gets repeated more than once I want to display it as blank. So the data should instead look:

Customer 1 1234 ABC
Customer 2 4567 DEF
Customer 1 1234 GHI
Customer 1 JKL
Customer 1 MNO

How would I do that?

Thanks

 

[rds747]

What I want to do is compare the last CustomerNo with the current CustomerNo and if it's the same replace that CustomerNo with a blank character.

So I thought of saving all the CustomerNos into a temp table and then while reading the current CustomerNos compare that with the CustomerNos from the temp table. But is that only possible by using a cursor?

Sorry I'm thinking in programming logic (VB).

 

[manmaria]

Third record in your expected result has 1234 again. Is that right?

 

[rds747]

Yes, I need to add a blank only if the result is repeated one row after another.

 

[rds747]

Apparently, the logic of performing the changes in the result data was a lot different then I had in mind.

How do I delete Temp tables? When I try drop the table I get the error 'Cannot drop the table '#Temp', because it does not exist in the system catalog.' And I get a similar error message when I try to delete the table from Enterprise Manager.

Thanks.

 

[vbkris]

temp tables get deleted automatically if they are created within a SP. but it is always safe to delete them in the SP using drop commands...

Known is handfull, Unknown is worldfull

 

[rds747]

I get this error when the sp is executed..

There is already an object named '#Temp' in the database.
There is already an object named '#TempGp' in the database.

You probably need to see the sp?

 

[vbkris]

not required, this happens sometimes.

first run this in query analyser:

drop table #Temp
drop table #TempGp

dont bother the errors.

in your SP:

create table #Temp
.....
--Last Line
drop table #Temp


the error must not happen any more (unless you are dropping the table before that line)...

Known is handfull, Unknown is worldfull

 

[rds747]

How would I do this so i dont get Incorrect syntax near the keyword 'convert'.

Update #Temp Set convert(char, OhMfNo) = ' '

 

[vbkris]

Update #Temp Set cast(OhMfNo as char) = ' '



Known is handfull, Unknown is worldfull

 

[rds747]

Incorrect syntax near '('

Update #Temp Set cast(OhMfNo as Char) = ' ' From #TempGp Where #Temp.OhTkNo = #TempGp.OhTkNo
And #Temp.OhCus = #TempGp.OhCus And #Temp.OhMfNo = #TempGp.OhMfNo And #Temp.OhOrd <> #TempGp.OhOrd

Not sure whats wrong with that line of snytax.

 

[vbkris]

oh no, that cannot be done, cast simply converts the type at run time. what exaclty are you trying to do???

Known is handfull, Unknown is worldfull

 

[mhoyt]

Are you trying to change the properties of the column OhMfNo? That is the the reason for the

cast(OhMfNo as Char)

If so, may I suggest the ALTER command?

USE SANDBOX
DROP TABLE #TEMP
CREATE TABLE #TEMP (OhMfNo VARCHAR(3))
INSERT INTO #TEMP VALUES ('AAA')
SELECT * FROM #TEMP

ALTER TABLE #TEMP
ALTER COLUMN OhMfNo CHAR(4)