Query longer in SP: level

Showing posts with label level. Show all posts

Friday, March 30, 2012

Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELECT statement.
Perhaps you can explain what you want to achieve in the end, and you might get some suggestions...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
--
Hugo Kornelis, SQL Server MVPsql

Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELE
CT statement.
Perhaps you can explain what you want to achieve in the end, and you might g
et some suggestions...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVPsql

Wednesday, March 28, 2012

Query Problem

Monday, March 26, 2012

Query Problem

I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I have the following query that is giving me an error that I can't quite
> >figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
> >Internal SQL Server error. I think it is related to the sub-query or the
> >group by. The sub-query executes successfully if I run it by itself.
> >
> > Any Ideas as to what I can try?
> >
> > Thanks
> >
> > Jeff
> >
> >
> >
> > Declare @.StartDate VarChar(12)
> > SET @.StartDate = '04/07/03'
> >
> > SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> > @.StartDate as StartDate
> > FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> > CASE
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> > DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> > /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> > And DateAdd(Day, 13, @.StartDate) Then 4 */
> > End AS Period
> > FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> > WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> > AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> > AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> > AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> > AND A.EMPLOYEEID = B.EMPLOYEEID
> > AND B.PayRuleName <> 'All Exempt'
> > AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate)
> > GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> > GROUP BY Plant, Period
> > ORDER BY Plant, Period DESC
> >
>
>

Query Problem

Monday, February 20, 2012

Query needed

Hi, I've a prob in my office.i have a table with the following fields,

table : acc_file
acc_cod nvarchar(6) P.key
acc_name nvarchar(30),
level int,
acc_mas nvarchar(6) which should any one of the previously entered
values of the field acc_cod(reference).

the rows will be (example)

ASSET ASSETS 0
LIAB LIABILITIES 0
curass current assets 1 Asset
Fixass fixed assets 1 Asset
curlia current liability 1 LIAB
BANKBAL BANLBALANCES 2 curass.

I want the rows to be displayed or ordered by the acc_mas and level.As
per the accounting standards the level field is given . for instance
it needed to be

ASSET ASSETS 0 ' base level
curass current assets 1 Asset
BANKBAL BANLBALANCES 2 curass.
Fixass fixed assets 1 Asset
LIAB LIABILITIES 0 ' base level
curlia current liability 1 LIAB

HOW IT WILL BE PERFORMED

LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET

I want that the query should return the values as per the acc_mas and
level as the example given above.Is it posiable thru the query .

With Thanks .
RaghuOn 8 Nov 2003 06:02:39 -0800, raghuraman_ace@.rediffmail.com
(Raghuraman) wrote:

>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
Is that level a fixed quantity or could there also be level 3, level 4
etc?|||Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
and each level may also have inner levels starting from (1,2,3, etc)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
>Is that level a fixed quantity or could there also be level 3, level 4
>etc?
>On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:

>Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
>and each level may also have inner levels starting from (1,2,3, etc)
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

In that case I'd be interested to see some ideas, as well. It's doable
if you know how many levels there are, but if you don't I don't think
sql server can do this with a straight query.

The following link has a discussion of this problem.

http://www.sqlteam.com/item.asp?ItemID=8866|||Hi - this sounds like something that I did recently, which required a
self cross join unioned with a self cross join and recursed. Um...
yeah anyway it was a bit wierd.

If you fancy posting some sample create and inserts we could maybe
have a bash at making something work for you.

Lyndon Hills <lyndon@.nospam.tenegi.com> wrote in message news:<squvqvc41es2fi1673mhinkcfjqbttnihq@.4ax.com>...
> >Hi, I've a prob in my office.i have a table with the following fields,
> >table : acc_file
> >acc_cod nvarchar(6) P.key
> >acc_name nvarchar(30),
> >level int,
> >acc_mas nvarchar(6) which should any one of the previously entered
> >values of the field acc_cod(reference).
> >the rows will be (example)
> >ASSET ASSETS 0
> >LIAB LIABILITIES 0
> >curass current assets 1 Asset
> >Fixass fixed assets 1 Asset
> >curlia current liability 1 LIAB
> >BANKBAL BANLBALANCES 2 curass.
> >I want the rows to be displayed or ordered by the acc_mas and level.As
> >per the accounting standards the level field is given . for instance
> >it needed to be
> >ASSET ASSETS 0 ' base level
> >curass current assets 1 Asset
> >BANKBAL BANLBALANCES 2 curass.
> >Fixass fixed assets 1 Asset
> >LIAB LIABILITIES 0 ' base level
> >curlia current liability 1 LIAB
> >HOW IT WILL BE PERFORMED
> >LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
> >LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
> >I want that the query should return the values as per the acc_mas and
> >level as the example given above.Is it posiable thru the query .
> >With Thanks .
> >Raghu
> >Is that level a fixed quantity or could there also be level 3, level 4
> >etc?
> >On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:
> >Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
> >and each level may also have inner levels starting from (1,2,3, etc)
> >*** Sent via Developersdex http://www.developersdex.com ***
> >Don't just participate in USENET...get rewarded for it!
> In that case I'd be interested to see some ideas, as well. It's doable
> if you know how many levels there are, but if you don't I don't think
> sql server can do this with a straight query.
> The following link has a discussion of this problem.
> http://www.sqlteam.com/item.asp?ItemID=8866|||>> As per the accounting standards the level field [sic]is given. <<

In the accounting systems I have seen, the account codes are fixed
length strrings of digits, arranged in a hierarchy, like the the Dewey
Decimal system, so you just sort them numerically. Do a GOOGLE on
"Uniform Chart of Accounts" and you will get all of the various state
requirements in the US.

Friday, March 30, 2012

Query question

Query question

Wednesday, March 28, 2012

Query Problem

Monday, March 26, 2012

Query Problem

Query Problem

Monday, February 20, 2012

Query needed

Query longer in SP

Blog Archive

About Me