calling queries - 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

SQL Server 2000 Forums

SQL Server Development (2000)

calling queries

Author

Topic

anjan
Starting Member

21 Posts

Posted - 2005-09-25 : 12:11:44

i have two queries (select queries with parameter).

i have written stored procedures for that as i can't create views with parameters.

now i want to call those queries in third query.
i.e i want to refer queries like this -> queryname.columnname in select statement

here clear details abt that.....

"Finnacco","dtrans" are tables.

The following are qureries created in access.
this query takes 'pdate' as parameter

1.CrUpdtdate(query name)

SELECT Sum(dTRaNs.AMOUNT) AS CrAmount, dTRaNs.CRACCODE AS CrAccode
FROM dTRaNs INNER JOIN Finnacco ON dTRaNs.CRACCODE = Finnacco.ACCODE
WHERE left(finnacco.accode,3)<>'412' and dtrans.date<=[pdate]
GROUP BY dTRaNs.CRACCODE

-------------------------
2.DrUpdtDate(query name)

SELECT Sum(dTRaNs.AMOUNT) AS DrAmount, dTRaNs.DRACCODE AS DrAccode
FROM dTRaNs INNER JOIN Finnacco ON dTRaNs.DRACCODE = Finnacco.ACCODE
WHERE left(finnacco.accode,3)<>'412' and dtrans.date<=[pdate]
GROUP BY dTRaNs.DRACCODE;

------------------------
3.Updtdate ( this qury uses the above queries e.g CrUpdtDate.CrAmount)

Finnacco,dtrans are tables.
CrUpdtdate,DrUpdtdate are queries which take parameter "pdate"

INSERT INTO temp ( temp1, temp5, temp3, temp4, temp6 )
SELECT Finnacco.ACCname, iif( isnull(CrUpdtDate.Cramount),0, CrUpdtDate.Cramount)- iif(isnull(DrUpdtDate.Dramount),0,DrUpdtDate.Dramount)+iif(finnacco.pbtype='C',finnacco.prebal,-1*finnacco.prebal) AS Tamount, IIf(Tamount < 0, "D", "C"), finnacco.accode, finnacco.type
FROM (Finnacco LEFT JOIN DrUpdtDate ON Finnacco.ACCODE = DrUpdtDate.Draccode) LEFT JOIN CrUpdtDate ON Finnacco.ACCODE = CrUpdtDate.Craccode
WHERE (((Left([finnacco].[accode],3))<>'412'))

How can i write above queries in Sql Server.

it works fine in access , same thing i want to get in sql server .how to get that...

plz help me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 00:35:00

You can use ISNULL, COALESCE and CASE

Try this

INSERT INTO temp ( temp1, temp5, temp3, temp4, temp6 )
SELECT Finnacco.ACCname, isnull(CrUpdtDate.Cramount,0)-
isnull(DrUpdtDate.Dramount,0)+
(Case When finnacco.pbtype='C' then finnacco.prebal else -1*finnacco.prebal end) AS Tamount,
Case When Tamount < 0 then 'D' else 'C' end, finnacco.accode, finnacco.type
FROM (Finnacco LEFT JOIN DrUpdtDate ON Finnacco.ACCODE = DrUpdtDate.Draccode)
LEFT JOIN CrUpdtDate ON Finnacco.ACCODE = CrUpdtDate.Craccode
WHERE (((Left([finnacco].[accode],3))<>'412'))

Madhivanan

Failing to plan is Planning to fail

anjan
Starting Member

21 Posts

Posted - 2005-09-26 : 10:41:56

In sql server....i can't refer CrUpdtdate.CrAmout in the last query
that's what my real problem...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 00:53:54

What is the error you got?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources