sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2005-11-16 : 04:20:57

HI

FOR A GENERATING A REPORT I HAVE TO DO THIS PROCESS TO ACHIVE THE RESUOLT
CAN THIS RESULT BE OBTAINED THOUGH SQL STATMENT WITHOUT CREATING A TABLE.
(I HAVE CREATE TWO TABLE FROM MAIN TABLE TO ACHIVE THIS RESULT)

With CmdObj
.ActiveConnection = ConnectAcct
.CommandText = "SELECT GLNAME, OAMOUNT, SUM(TAMOUNT) AS TAMT INTO OGENLED FROM GENLED WHERE (OAMOUNT <> 0 AND DOCDATE IS NULL) OR DOCDATE <'" & Format(TxtFromDate.Text, "yyyy-mm-dd") & "' GROUP BY GLNAME, OAMOUNT ORDER BY GLNAME"
.Execute
.CommandText = "SELECT * INTO TGENLED FROM GENLED WHERE DOCDATE >='" & Format(TxtFromDate.Text, "yyyy-mm-dd") & "' AND DOCDATE<='" & Format(TxtToDate.Text, "yyyy-mm-dd") & "' ORDER BY GLNAME"
.Execute
.CommandText = "UPDATE Tgenled SET oamount = (O.oamount + CASE WHEN O.tamt IS NULL THEN 0 ELSE O.TAMT END) FROM ogenled O WHERE tgenled.glname = O.glname "
.Execute
.CommandText = "INSERT INTO tgenled (glname, oamount, cb) SELECT o.glname, (o.oamount + case when o.tamt is null then 0 else o.tamt end), 'N' From ogenled o WHERE o.glname NOT IN (SELECT glname FROM tgenled)"
.Execute
.CommandText = "DROP TABLE ogenled"
.Execute
End With

THANKS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-16 : 04:39:09

Explain what you are trying to do
Give some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 15:25:46

SELECT ... columnlist ... INTO MyNewTable FROM MyOldTable WHERE ...

is fine as a quick hack to get some data into a "copy table", but really isn't a good method for a production system.

You really should create the table first (using dynamic SQL is fine) and then insert into it.

SELECT * ...

You should not use this syntax, specify all the columns you need to use (less effort for SQL Server to run your query, and "future proof" against new columns that might be added in the future - e.g. massive TEXT/IMAGE columns that will ruin your day at some future point and send performance to hell in a hamper!

UPDATE Tgenled SET oamount = (O.oamount + CASE WHEN O.tamt IS NULL THEN 0 ELSE O.TAMT END) FROM ogenled O WHERE tgenled.glname = O.glname

If you specified a column list on the previous statement you could incorporate this in it, rather than re-processing every row in the table a second time.

That aside, this syntax could be much more efficiently written as:


UPDATE U
SET oamount = O.oamount + O.TAMT
FROM Tgenled AS U
     JOIN ogenled AS O
          ON O.glname = U.glname
WHERE O.TAMT IS NOT NULL

I presume that there is only one ogenled row for a given Tgenled row? Otherwise I don't think this is going to work (I think you will have to use a nested subselect with an aggregate SUM on O.TAMT)

Your naming consistency is sloppy - you have tgenled, TGENLED, Tgenled etc. You should make these consistent (and the same as the physical name of the object)

Yes, I'm pretty sure that you can do this without a temporary table (and the way that you are creating your temporary table is limiting - only one user will be able to do this operation at one time). However, its neigh impossible to debug SQL like this. You need to get the SQL working in Query Analyser, or something similar, where you won't have all the VB string handling and parameter concatenation making it difficult to see what's going on. Once you have that working then convert it into VB (with parameter string concatenation etc.). Better still create a Stored Procedure and then just "call" that from VB.

Kristen

asm
Posting Yak Master

140 Posts

Posted - 2005-11-17 : 23:45:50

I have to generate Ledger Account - for the I have to accept
the FromDate & ToDate from User. I have create oGenLed table
from GenLed View for calculating the opening balance for Ledger
(docdate < FromDate) and another table tGenLed for details
(docdate between FromDate and ToDate.
After creating the table i have update the oamount in tGenLed
table with oamount + tamt from oGenLed table.
And also insert glname in tGenLed from oGenLed if not found.

SELECT GLNAME, OAMOUNT, SUM(TAMOUNT) AS TAMT
INTO oGenLed FROM GenLed
WHERE (OAMOUNT <> 0 AND DOCDATE IS NULL)
OR DOCDATE <'09/27/2005'
GROUP BY GLNAME, OAMOUNT
ORDER BY GLNAME

oGenLed Table
=============
GLNAME OAMOUNT TAMT
------------------------------------
CONVEYANCE 0.00 250.00
RENT 0.00 250.00
SALARY 0.00 500.00
PRASHANT 1500.00 NULL

SELECT * INTO tGenLed
FROM GenLed
WHERE DOCDATE >='09/27/2005' AND DOCDATE<='03/31/2006' ORDER BY GLNAME

tGenLed Table
=============
GLNAME OAMOUNT AMOUNT VOUCHER
---------------------------------------------
CONVEYANCE 0.00 500.00 C15
RENT 0.00 250.00 C18
RENT 0.00 500.00 C20

I want tGenLed table should be :-

GLNAME OAMOUNT AMOUNT VOUCHER
---------------------------------------------
CONVEYANCE 250.00 500.00 C15
RENT 250.00 250.00 J150
RENT 250.00 500.00 C20
SALARY 500.00 NULL NULL
PRASHANT 1500.00 NULL NULL

For getting this result I have : update and inset

UPDATE TGENLED
==============
UPDATE tGenLed
SET oamount = (O.oamount + CASE WHEN O.tamt IS NULL THEN 0 ELSE O.TAMT END)
FROM oGenLed O
WHERE tGenLed.glname = O.glname

OR- if i write update statment like this than i need not to create oGenLed

UPDATE tGenLed
SET oamount = (O.oamount + CASE WHEN O.tamt IS NULL THEN 0 ELSE O.TAMT END)
FROM (SELECT GLNAME, OAMOUNT, SUM(TAMOUNT) AS TAMT
FROM GenLed
WHERE (OAMOUNT <> 0 AND DOCDATE IS NULL)
OR DOCDATE <'09/27/2005'
GROUP BY GLNAME, OAMOUNT)O
WHERE tGenLed.glname = O.glname

INSERT TGENLED
==============
INSERT INTO tGenLed (glname, oamount)
SELECT o.glname, (o.oamount + case when o.tamt is null then 0 else o.tamt end)
FROM oGenLed o
WHERE o.glname NOT IN (SELECT glname FROM tGenLed)

<OR>

INSERT INTO tGenLed (glname, oamount)
SELECT o.glname, (o.oamount + case when o.tamt is null then 0 else o.tamt end)
FROM (SELECT GLNAME, OAMOUNT, SUM(TAMOUNT) AS TAMT
FROM GenLed
WHERE (OAMOUNT <> 0 AND DOCDATE IS NULL)
OR DOCDATE <'09/27/2005'
GROUP BY GLNAME, OAMOUNT)O
WHERE o.glname NOT IN (SELECT glname FROM tGenLed)

Please guide how this result I can get without creating
a table and that should be used in multiuser environment.

thanks

asm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources