sql reports: query

Showing posts with label query. Show all posts

Thursday, March 29, 2012

can we have temporary table in store procedure when using BCP ?

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'

it show result , however when i run it using BCP tools

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way

CREATE PROCEDURE [SP_TEST]

--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B

SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D

----------BEGINNING OF PROCESS------

SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2

)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

----------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO

when i run in query analyzer , using "exec SP_TEST" , it work

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.

do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.

I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.

Wait until Monday, and lets see if any other forum members recognize this error.|||Hi

Did a quick RTFM:

Copying Data To or From a Temporary Table

When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".

HTH

EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,

This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.

bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:

SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF

If you use the "queryout" operator with an exec statement, it issues the following command:

SET FMTONLY ON
exec ...
SET FMTONLY OFF

Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.

FYI, a solution with a table variable WILL work, e.g.

<code>
create proc dbo.bills_test
as
begin

declare @.output table (col1... , col2 ... , ...)

insert @.output
select ...

select col1, col2 ...
from @.output

end
<\code>

you can play with this in query analyser, to get a stored proc that will work with bcp...

HTH,

Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

will tempdb object will causing any performance issue , i really not sure ..

:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.

Oh, for the love of god....

In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders

SELECT * FROM @.myTabVar99
GO

EXEC mySproc99

exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'

DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql

Tuesday, March 27, 2012

can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.

Sunday, March 25, 2012

Can we ask English Query to show a Graph ?

Hi
My EQ model dispalys a normal table when you ask a question such as;
show me the amount of goods solds for last 5 years.
it shows the amount grouped in to 5 years.
It would be nice if I can show it in a graph rather than the data.
I have seen a GRAPH verb in EQ but can't find a single help on how to make
it draw a graph.
Have anybody done this?
rgds
KK
Within English Query you can use something called commands which can run a
proc, shell out and run a batch file, etc.
Depending on your programming skill you should be able to use this to
generate a graph.
IIRC - Adam Blum (Not sure if I have his name correct), who is/was the PM
(Program Manager) for English Query did a demonstration at TechEd or PDC
where he demonstrated something similar.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"KK" <kk@.kk.com> wrote in message
news:uGyC1RiaEHA.2408@.tk2msftngp13.phx.gbl...
> Hi
> My EQ model dispalys a normal table when you ask a question such as;
> show me the amount of goods solds for last 5 years.
> it shows the amount grouped in to 5 years.
> It would be nice if I can show it in a graph rather than the data.
> I have seen a GRAPH verb in EQ but can't find a single help on how to make
> it draw a graph.
> Have anybody done this?
> rgds
> KK
>
|||Hi Hilary,
*Phew*
About time

btw, thanks for the reply,
Well, coding is not at all a problem. I am already messing up with
the command object. can get smalll commands like sending
generated custom emails and such is possible...
however, trying to create relationship for graphs always
gives me errors (thats my bad) Do you have any idea
where I can find this guys demo ? link to a web page
where he might have information with a sample?
Amazing thing is, can't find a single help regarding this
on the web!!
I would greatly appreciate if you can send any links if u have
meanwhile, i'll dig for his name on the web
KK
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ePN7tjnaEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Within English Query you can use something called commands which can run a
> proc, shell out and run a batch file, etc.
> Depending on your programming skill you should be able to use this to
> generate a graph.
> IIRC - Adam Blum (Not sure if I have his name correct), who is/was the PM
> (Program Manager) for English Query did a demonstration at TechEd or PDC
> where he demonstrated something similar.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "KK" <kk@.kk.com> wrote in message
> news:uGyC1RiaEHA.2408@.tk2msftngp13.phx.gbl...
make
>

Can views be used in query builder?

I tried it but got an invalid object error. I then copied and pasted the sql statement contained in the view but it doesn't run correctly inside query builder and only shows 1 valid row, and a 2nd row that's blank the the first column and has a number 1 in the second column. The view runs correctly in SQL Server Management Studio Express.

How about wrapping the view inside a stored procedure?

|||

Stored procedures can't be used in query builder either. All I can do is put in a dummy SQL statement (like Select 1 as column1, 2 as whatever) and then change it to a stored procedure later when I have access to the "data" tab in [Design].

That doesn't seem right but is the only way I can figure out how to do it.

can view be dynamically filtered like an MSAccess query?

In MSAccess you can dynamically filter a query by adding square brackets [ ]
to the criteria section and a prompt inside the brackets [enter filter code]
.
Then you can open the query directly and be prompted for a filter. I tried
this in the view without any success.
I was approached today by an End user who uses an Access ADP to interface
with our sql server. The user needs to pull/look at data from a large
table, and currently there is no mechanism for filtering the respective tabl
e
in the Access ADP. Since several users are using the same ADP, I don't want
to modify it. I was thinking of creating a view that could be opened from
the ADP. But rather than having the user call me everytime he needs to
change the criteria, is there a way to make the view dynamic?
Thanks,
RichRich,
You can use an inline table-valued function. You can not pass parameters to
a view.
Inline User-Defined Functions
http://msdn.microsoft.com/library/d...>
_08_73lf.asp
AMB
"Rich" wrote:

> In MSAccess you can dynamically filter a query by adding square brackets [
]
> to the criteria section and a prompt inside the brackets [enter filter cod
e].
> Then you can open the query directly and be prompted for a filter. I tri
ed
> this in the view without any success.
> I was approached today by an End user who uses an Access ADP to interface
> with our sql server. The user needs to pull/look at data from a large
> table, and currently there is no mechanism for filtering the respective ta
ble
> in the Access ADP. Since several users are using the same ADP, I don't wa
nt
> to modify it. I was thinking of creating a view that could be opened from
> the ADP. But rather than having the user call me everytime he needs to
> change the criteria, is there a way to make the view dynamic?
> Thanks,
> Rich|||Thanks - very

. I will give this a shot. The only thing is that - afte
r
reading the article - it looks like the user needs to write a sql statement.
If the user is using an Access ADP - where does he write the statement to us
e
the inline function? In the article it looked like the user had QA on his
desktop.
"Alejandro Mesa" wrote:
> Rich,
> You can use an inline table-valued function. You can not pass parameters t
o
> a view.
> Inline User-Defined Functions
> http://msdn.microsoft.com/library/d...
es_08_73lf.asp
>
> AMB
> "Rich" wrote:
>|||I figured it out. The function will show up in the user interface. The use
r
can invoke the function the same way as a query in MSAccess and will be
prompted for input. Very

. Thanks again for the suggestion.
"Rich" wrote:
> Thanks - very

. I will give this a shot. The only thing is that - af
ter
> reading the article - it looks like the user needs to write a sql statemen
t.
> If the user is using an Access ADP - where does he write the statement to
use
> the inline function? In the article it looked like the user had QA on his
> desktop.
> "Alejandro Mesa" wrote:
>

Thursday, March 22, 2012

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECT FLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

FROM [Basket Analysis]

NATURAL PREDICTION JOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECTFLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

FROM [Basket Analysis]

NATURALPREDICTIONJOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

Can use some help with a select query

Hi,

I have this tables (view attachment) wich i want to query with a selct statement I'll tried al sort of things but it didn't happend!

I have a machine which has one cabinet in this cabinet there could be 2 screens , I want the details of both screens of a certain machine in a single line
Hope somesone understand what i want!

Cheers WimmoAs a matter of fact, no I don't understand.
It sounds like you are describing a crosstab query, but your schema allows only one screen per cabinet.
It would be best if you posted the query you have tried, and let us know how the results differed from what you wanted.|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Numberi would be extremely leery of mixing "comma list" syntax with JOIN syntax

in mysql 5, for instance, JOINs take precedence (similar to the way ANDs take precedence over ORs) and so the following will produce an error --tblScreens s1 left join tblScreens s2
on c.Screen2=s2.Part_Numbercan you see why?|||Yes the left join should be to tblCabinet i.e.
select ...
from tblMachine m
, tblScreens s1
, tblCabinet c
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number

And as you say this is just as bad as mixing ANDs and ORs without brackets
Thanks for highlighting it
So here it is without mixing syntax
select ...
from tblMachine m
,tblCabinet c
,tblScreens s1
,tblScreens s2
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number
and c.Screen2*=s2.Part_Number

select ...
from tblMachine m
join tblCabinet c on m.Cabinet=c.id
join tblScreens s1 on c.Screen1=s1.Part_Number
left join tblScreens s2 on c.Screen2=s2.Part_Number|||Hi All, thanx for your reply's.
I tried the query's but none did actually worked, they generated no errors but it returned zero records where ther should be one.

@.Blindman, I tried this query for getting the info of 1 screen which is already hard to get but there could be 2 or none in a cabinet

SELECT tblCabinet.Screen1, tblCabinet.Screen2, tblScreenTypes.ScreenType, tblBrands.BrandName, tblCommTypes.CommType, tblAdaptor.Adaptor
FROM tblMachine INNER JOIN
tblCabinet ON tblMachine.Cabinet = tblCabinet.ID INNER JOIN
tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number INNER JOIN
tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID INNER JOIN
tblBrands ON tblScreens.Brand = tblBrands.ID INNER JOIN
tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID INNER JOIN
tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'

I'll tried several changes in the joins but none returned an error but none returned values.|||If your query unexpectedly returned zero rows, then run this and see how many rows it returns:SELECT count(*)
FROM tblMachine
-- INNER JOIN tblCabinet ON tblMachine.Cabinet = tblCabinet.ID
-- INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number
-- INNER JOIN tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID
-- INNER JOIN tblBrands ON tblScreens.Brand = tblBrands.ID
-- INNER JOIN tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID
-- INNER JOIN tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'
Then, uncomment one line at a time until your query again returns zero rows, and that will tell you where the problem join is.|||I'll did what you suggested and at this line

INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number

it returns 0 but i don´t understand why, there are values. Could it have something todo with the relation?

Thanks to your proposed strategy i found the problem, seems that there was a relation to an old table on screen 1 so deleting that table did solve my problem.

Thanks for all your help and probably expensive time.

Wim

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
Thanks in advance
TakeCare
Love
Amit
dev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegr oups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID = Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID = Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID = dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>sql

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>

can u guide me writing a simple query

I am trying to do following thing:

i got a table which contains some words...all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table...

could anyone help me out here....thanks a lot in advance.......

I'm assuming by the title that you want to do it in the sql query. Here is one way to do it:

Select
Case WhenWordlike 'r%' Then right(Word, len(Word)-1) end
From Table1

|||

Can you provide some sample data and what you are trying to update it to so we can get a better idea?

sql

Can TSQL query create new output column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

Tuesday, March 20, 2012

Can this query be simpilified

Hi,
Can this query be simpilified for performance
If (select count(id)
From dbo.TDetails
where
ID = 554 AND Name = 'SUN'
HAVING
Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ......
.
ThanksChris,
I think we are missing a "group by" clause.
AMB
"Chris" wrote:

> Hi,
> Can this query be simpilified for performance
>
> If (select count(id)
> From dbo.TDetails
> where
> ID = 554 AND Name = 'SUN'
> HAVING
> Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ....
..
> Thanks|||> I think we are missing a "group by" clause.
My mistake. Can you try:
if (
select
isnull(sum(Quantity_Shipped + Quantity_Credited), 0) -
isnull(sum(QUANTITY), 0)
from
dbo.TDetails
where
[ID] = 554 AND [Name] = 'SUN'
) != 0
...
AMB
"Alejandro Mesa" wrote:
> Chris,
> I think we are missing a "group by" clause.
>
> AMB
> "Chris" wrote:
>|||Hi Chris,
I would not expect any performance gain by rewriting the statement.
Personally, I would prefer the following syntax. An "Else" clause might
become faster this way. The "Then" clause will not be faster.
If NOT EXISTS (
SELECT 1
FROM dbo.TDetails
WHERE ID = 554
AND Name = 'SUN'
HAVING SUM(Quantity_Shipped + Quantity_Credited) <> SUM(Quantity)
)
Begin
.
End
Make sure you have an index on (ID, Name).
Gert-Jan
Chris wrote:
> Hi,
> Can this query be simpilified for performance
> If (select count(id)
> From dbo.TDetails
> where
> ID = 554 AND Name = 'SUN'
> HAVING
> Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ....
..
> Thanks

can this query be rewritten?

update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = (select max(MCARA_RISK_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM),
MAX_MCARD_RISK_ADJ_RTE = (select max(MCARD_RISK_ADJ_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM)

Can I get the same results with one join instead of two without creating a temporary table?

Thanks much.

:confused:update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = MaxValues.MCARA_RISK_RTE,
MAX_MCARD_RISK_ADJ_RTE = MaxValues.MCARD_RISK_ADJ_RTE
from CMS_RISK_SCORES
inner join --MaxValues
(select HIC_NUM,
max(MCARA_RISK_RTE) as MCARA_RISK_RTE,
max(MCARD_RISK_ADJ_RTE) as MCARD_RISK_ADJ_RTE
from XTAW0200_MEM_DTL
group by HIC_NUM) MaxValues
on CMS_RISK_SCORES.HIC_NUM = MaxValues.HIC_NUM|||Thank You.|||While there is a difference in syntax, I don't think there will be any significant difference in execution plan between the two statments. SQL Server is very good at combining redundant queries like this.

-PatPsql

Can This Query Be Improved?

The query displayed below currently takes approximately 5-6 seconds to run in SQL Query Analyzer. It returns 685 rows. In my opinion, 5 seconds seems way to long, so I am wondering if there is a way to optimize this query. Does anyone have suggestions on what I could do to improve the performance of this query?

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON

SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'

FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID

WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)

ORDER BY SalesLead.LastName

Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.

Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.

-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.

The rowcount without the where clause is 15,617.

According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)

19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??

That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...

Or simplify the criteria...

or...and this is my last shot before FULLTEXT

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'

FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID

WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID

WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.

It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!

-PatP

can this be done?

Table1
ID|CATID|NAME
1 3 A
2 3 B
3 3 C
4 4 D
5 4 E
I want to write a query that pull all the record from the same CATID but
with only ID given.
If ID = 4 it would return ID 4 and 5 because they are in the same category
If ID = 2 it would return ID 1,2,3
Thanks,
HowardHoward wrote:
> Table1
> ID|CATID|NAME
> 1 3 A
> 2 3 B
> 3 3 C
> 4 4 D
> 5 4 E
> I want to write a query that pull all the record from the same CATID but
> with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same category
> If ID = 2 it would return ID 1,2,3
> Thanks,
> Howard
DECLARE @.id INT;
SET @.id = 4;
SELECT id
FROM table1
WHERE catid =
(SELECT catid
FROM table1
WHERE id = @.id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I want to write a query that pull all the record from the same CATID
> but with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same
> category If ID = 2 it would return ID 1,2,3
A relatively easy way to do that would be using in sub-query:
create table dbo.table1(id tinyint,catid tinyint,name char(1))
go
insert into dbo.table1(id,catid,name)
select 1,3,'A' union all
select 2,3,'B' union all
select 3,3,'C' union all
select 4,4,'D' union all
select 5,4,'E'
go
declare @.id tinyint
set @.id = 1
select * from dbo.table1
where catid in
(select catid from dbo.table1 where id=@.id)
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Kent,
declare @.id tinyint
set @.id = 4
select t2.* from dbo.table1 t1
left join dbo.table1 t2
on t1.catid = t2.catid
where t1.id = @.id
is an option too.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

Can this be done with sql?

I am trying to produce a query that will create a sorted list and I
don't know if it's even possible.
Here's what I aim to have the query return:
Category1
item1 description
item2 description
Category 2
item1 description
item2 description
Here are the two tables.
tblCategory
========== catID description
1 Category1
2 Category2
tblItem
=========== itemID itemName itemDescription catID[fk]
1 item1 description 1
2 item2 description 1
3 item1 description 2
4 item2 description 2
Thanks in advance,
RanginaldThat should be done on the client side in a report generator program of some
sort. That said, here's one way to get a listing similar to what you're
asking for (there will be two extra columns used for proper ordering of the
rows, but you can eliminate those in your front-end app):
CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
description VARCHAR(20) NOT NULL)
INSERT INTO #tblCategory (catID, description)
VALUES (1, 'Category1')
INSERT INTO #tblCategory (catID, description)
VALUES (2, 'Category2')
CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
itemName VARCHAR(10) NOT NULL,
itemDescription VARCHAR(20) NOT NULL,
catID INT NOT NULL)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (1, 'item1', 'description', 1)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (2, 'item2', 'description', 1)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (3, 'item1', 'description', 2)
INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
VALUES (4, 'item2', 'description', 2)
SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
FROM #tblItem i
UNION
SELECT 'A' AS RowType, c.description, '', c.catID
FROM #tblCategory c
ORDER BY catID, RowType, itemName
DROP TABLE #tblCategory
DROP TABLE #tblItem
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...
>I am trying to produce a query that will create a sorted list and I
> don't know if it's even possible.
> Here's what I aim to have the query return:
> Category1
> item1 description
> item2 description
> Category 2
> item1 description
> item2 description
> Here are the two tables.
> tblCategory
> ==========> catID description
> 1 Category1
> 2 Category2
> tblItem
> ===========> itemID itemName itemDescription catID[fk]
> 1 item1 description 1
> 2 item2 description 1
> 3 item1 description 2
> 4 item2 description 2
> Thanks in advance,
> Ranginald
>|||As I'm new to web programming, I never thought to use a report
generator program.
What I am aiming to do is have a category.aspx page where the user has
a drop down "go" list (data bound) which leads them to the category
they chose via an HTML anchor on the same page (hence the SQL I asked
about).
I have no experience with sql server's report generator (although I
have the express 2005 version). Is this a good place to start for the
very basic needs I have, or could you recommend a .net compatible basic
report generator (little cost would be great, too).
Thanks again in advance for all your help!
-Ranginald
Mike C# wrote:
> That should be done on the client side in a report generator program of some
> sort. That said, here's one way to get a listing similar to what you're
> asking for (there will be two extra columns used for proper ordering of the
> rows, but you can eliminate those in your front-end app):
> CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
> description VARCHAR(20) NOT NULL)
> INSERT INTO #tblCategory (catID, description)
> VALUES (1, 'Category1')
> INSERT INTO #tblCategory (catID, description)
> VALUES (2, 'Category2')
> CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
> itemName VARCHAR(10) NOT NULL,
> itemDescription VARCHAR(20) NOT NULL,
> catID INT NOT NULL)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (1, 'item1', 'description', 1)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (2, 'item2', 'description', 1)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (3, 'item1', 'description', 2)
> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> VALUES (4, 'item2', 'description', 2)
> SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
> FROM #tblItem i
> UNION
> SELECT 'A' AS RowType, c.description, '', c.catID
> FROM #tblCategory c
> ORDER BY catID, RowType, itemName
> DROP TABLE #tblCategory
> DROP TABLE #tblItem
> "Ranginald" <davidwank@.gmail.com> wrote in message
> news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...
> >I am trying to produce a query that will create a sorted list and I
> > don't know if it's even possible.
> >
> > Here's what I aim to have the query return:
> >
> > Category1
> > item1 description
> > item2 description
> > Category 2
> > item1 description
> > item2 description
> >
> > Here are the two tables.
> >
> > tblCategory
> > ==========> > catID description
> > 1 Category1
> > 2 Category2
> >
> > tblItem
> > ===========> > itemID itemName itemDescription catID[fk]
> > 1 item1 description 1
> > 2 item2 description 1
> > 3 item1 description 2
> > 4 item2 description 2
> >
> > Thanks in advance,
> > Ranginald
> >|||Ranginald wrote:
> As I'm new to web programming, I never thought to use a report
> generator program.
> What I am aiming to do is have a category.aspx page where the user has
> a drop down "go" list (data bound) which leads them to the category
> they chose via an HTML anchor on the same page (hence the SQL I asked
> about).
>
You don't need a report generator to do this. I don't know ASP.NET, so
I'll give you the method for a traditional ASP page, you can adapt from
there.
Your page will issue a query similar to this:
SELECT
tblCategory.Description,
tblItem.ItemName,
tblItem.ItemDescription
FROM tblItem
INNER JOIN tblCategory
ORDER BY tblCategory.Description, tblItem.ItemName
The results will returned to a Recordset object. In your ASP code,
you're going to loop through that Recordset object:
strCategory = ""
While Not oRecordset.EOF
If oRecordset("Description") <> strCategory Then
' Print a new category header
Response.Write(oRecordset("Description"))
strCategory = oRecordset("Description")
End If
' Print current item listing
Response.Write(oRecordset("ItemName"))
Response.Write(oRecordset("ItemDescription"))
oRecordset.MoveNext
Loop
Does that help?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you're not generating a report, you don't need a report generator;
although the basic concept is the same as the old report-writing with
"breaks". Just SELECT your data (order it by CategoryID and ItemName), then
loop through the data on the client side and build your drop-down list.
Here's an example in ASP.NET (assumes you have a form with a Drop-down list
named DropDownList1):
Dim SqlCon As SqlClient.SqlConnection
Dim SqlCmd As SqlClient.SqlCommand
Dim SqlDR As SqlClient.SqlDataReader
Try
SqlCon = New SqlClient.SqlConnection("SERVER=;INITIAL
CATALOG=;INTEGRATED SECURITY=SSPI;")
SqlCon.Open()
SqlCmd = New SqlClient.SqlCommand("SELECT c.description AS CategoryName,
i.itemName, i.itemDescription " & _
"FROM tblCategory c " & _
"INNER JOIN tblItem i " & _
"ON c.catID = i.catID " & _
"ORDER BY c.description, i.itemName", SqlCon)
SqlDR = SqlCmd.ExecuteReader()
Dim LastCategory As String = ""
While (SqlDR.Read())
Dim CurrentCategory As String = SqlDR("CategoryName").ToString()
Dim CurrentItem As String = "::" & SqlDR("ItemName").ToString()
If (CurrentCategory <> LastCategory) Then
DropDownList1.Items.Add(CurrentCategory)
LastCategory = CurrentCategory
End If
DropDownList1.Items.Add(CurrentItem)
End While
Catch ex As Exception
Throw New Exception("Error: Database Connection Error: " + ex.Message())
Finally
If Not (SqlDR Is Nothing) Then
SqlDR.Close()
End If
If Not (SqlCmd Is Nothing) Then
SqlCmd.Dispose()
End If
If Not (SqlCon Is Nothing) Then
SqlCon.Dispose()
End If
End Try
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1155687276.781480.170340@.i3g2000cwc.googlegroups.com...
> As I'm new to web programming, I never thought to use a report
> generator program.
> What I am aiming to do is have a category.aspx page where the user has
> a drop down "go" list (data bound) which leads them to the category
> they chose via an HTML anchor on the same page (hence the SQL I asked
> about).
> I have no experience with sql server's report generator (although I
> have the express 2005 version). Is this a good place to start for the
> very basic needs I have, or could you recommend a .net compatible basic
> report generator (little cost would be great, too).
> Thanks again in advance for all your help!
> -Ranginald
> Mike C# wrote:
>> That should be done on the client side in a report generator program of
>> some
>> sort. That said, here's one way to get a listing similar to what you're
>> asking for (there will be two extra columns used for proper ordering of
>> the
>> rows, but you can eliminate those in your front-end app):
>> CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
>> description VARCHAR(20) NOT NULL)
>> INSERT INTO #tblCategory (catID, description)
>> VALUES (1, 'Category1')
>> INSERT INTO #tblCategory (catID, description)
>> VALUES (2, 'Category2')
>> CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
>> itemName VARCHAR(10) NOT NULL,
>> itemDescription VARCHAR(20) NOT NULL,
>> catID INT NOT NULL)
>> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
>> VALUES (1, 'item1', 'description', 1)
>> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
>> VALUES (2, 'item2', 'description', 1)
>> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
>> VALUES (3, 'item1', 'description', 2)
>> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
>> VALUES (4, 'item2', 'description', 2)
>> SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
>> FROM #tblItem i
>> UNION
>> SELECT 'A' AS RowType, c.description, '', c.catID
>> FROM #tblCategory c
>> ORDER BY catID, RowType, itemName
>> DROP TABLE #tblCategory
>> DROP TABLE #tblItem
>> "Ranginald" <davidwank@.gmail.com> wrote in message
>> news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...
>> >I am trying to produce a query that will create a sorted list and I
>> > don't know if it's even possible.
>> >
>> > Here's what I aim to have the query return:
>> >
>> > Category1
>> > item1 description
>> > item2 description
>> > Category 2
>> > item1 description
>> > item2 description
>> >
>> > Here are the two tables.
>> >
>> > tblCategory
>> > ==========>> > catID description
>> > 1 Category1
>> > 2 Category2
>> >
>> > tblItem
>> > ===========>> > itemID itemName itemDescription catID[fk]
>> > 1 item1 description 1
>> > 2 item2 description 1
>> > 3 item1 description 2
>> > 4 item2 description 2
>> >
>> > Thanks in advance,
>> > Ranginald
>> >
>|||Perfect! Thanks!
Mike C# wrote:
> If you're not generating a report, you don't need a report generator;
> although the basic concept is the same as the old report-writing with
> "breaks". Just SELECT your data (order it by CategoryID and ItemName), then
> loop through the data on the client side and build your drop-down list.
> Here's an example in ASP.NET (assumes you have a form with a Drop-down list
> named DropDownList1):
> Dim SqlCon As SqlClient.SqlConnection
> Dim SqlCmd As SqlClient.SqlCommand
> Dim SqlDR As SqlClient.SqlDataReader
> Try
> SqlCon = New SqlClient.SqlConnection("SERVER=;INITIAL
> CATALOG=;INTEGRATED SECURITY=SSPI;")
> SqlCon.Open()
> SqlCmd = New SqlClient.SqlCommand("SELECT c.description AS CategoryName,
> i.itemName, i.itemDescription " & _
> "FROM tblCategory c " & _
> "INNER JOIN tblItem i " & _
> "ON c.catID = i.catID " & _
> "ORDER BY c.description, i.itemName", SqlCon)
> SqlDR = SqlCmd.ExecuteReader()
> Dim LastCategory As String = ""
> While (SqlDR.Read())
> Dim CurrentCategory As String = SqlDR("CategoryName").ToString()
> Dim CurrentItem As String = "::" & SqlDR("ItemName").ToString()
> If (CurrentCategory <> LastCategory) Then
> DropDownList1.Items.Add(CurrentCategory)
> LastCategory = CurrentCategory
> End If
> DropDownList1.Items.Add(CurrentItem)
> End While
> Catch ex As Exception
> Throw New Exception("Error: Database Connection Error: " + ex.Message())
> Finally
> If Not (SqlDR Is Nothing) Then
> SqlDR.Close()
> End If
> If Not (SqlCmd Is Nothing) Then
> SqlCmd.Dispose()
> End If
> If Not (SqlCon Is Nothing) Then
> SqlCon.Dispose()
> End If
> End Try
>
> "Ranginald" <davidwank@.gmail.com> wrote in message
> news:1155687276.781480.170340@.i3g2000cwc.googlegroups.com...
> > As I'm new to web programming, I never thought to use a report
> > generator program.
> >
> > What I am aiming to do is have a category.aspx page where the user has
> > a drop down "go" list (data bound) which leads them to the category
> > they chose via an HTML anchor on the same page (hence the SQL I asked
> > about).
> >
> > I have no experience with sql server's report generator (although I
> > have the express 2005 version). Is this a good place to start for the
> > very basic needs I have, or could you recommend a .net compatible basic
> > report generator (little cost would be great, too).
> >
> > Thanks again in advance for all your help!
> >
> > -Ranginald
> > Mike C# wrote:
> >> That should be done on the client side in a report generator program of
> >> some
> >> sort. That said, here's one way to get a listing similar to what you're
> >> asking for (there will be two extra columns used for proper ordering of
> >> the
> >> rows, but you can eliminate those in your front-end app):
> >>
> >> CREATE TABLE #tblCategory (catID INT PRIMARY KEY NOT NULL,
> >> description VARCHAR(20) NOT NULL)
> >>
> >> INSERT INTO #tblCategory (catID, description)
> >> VALUES (1, 'Category1')
> >>
> >> INSERT INTO #tblCategory (catID, description)
> >> VALUES (2, 'Category2')
> >>
> >> CREATE TABLE #tblItem (itemID INT NOT NULL PRIMARY KEY,
> >> itemName VARCHAR(10) NOT NULL,
> >> itemDescription VARCHAR(20) NOT NULL,
> >> catID INT NOT NULL)
> >>
> >> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> >> VALUES (1, 'item1', 'description', 1)
> >>
> >> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> >> VALUES (2, 'item2', 'description', 1)
> >>
> >> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> >> VALUES (3, 'item1', 'description', 2)
> >>
> >> INSERT INTO #tblItem (itemID, itemName, itemDescription, catID)
> >> VALUES (4, 'item2', 'description', 2)
> >>
> >> SELECT 'B' AS RowType, i.itemName, i.itemDescription, i.catID
> >> FROM #tblItem i
> >> UNION
> >> SELECT 'A' AS RowType, c.description, '', c.catID
> >> FROM #tblCategory c
> >> ORDER BY catID, RowType, itemName
> >>
> >> DROP TABLE #tblCategory
> >> DROP TABLE #tblItem
> >>
> >> "Ranginald" <davidwank@.gmail.com> wrote in message
> >> news:1155610407.063807.318420@.74g2000cwt.googlegroups.com...
> >> >I am trying to produce a query that will create a sorted list and I
> >> > don't know if it's even possible.
> >> >
> >> > Here's what I aim to have the query return:
> >> >
> >> > Category1
> >> > item1 description
> >> > item2 description
> >> > Category 2
> >> > item1 description
> >> > item2 description
> >> >
> >> > Here are the two tables.
> >> >
> >> > tblCategory
> >> > ==========> >> > catID description
> >> > 1 Category1
> >> > 2 Category2
> >> >
> >> > tblItem
> >> > ===========> >> > itemID itemName itemDescription catID[fk]
> >> > 1 item1 description 1
> >> > 2 item2 description 1
> >> > 3 item1 description 2
> >> > 4 item2 description 2
> >> >
> >> > Thanks in advance,
> >> > Ranginald
> >> >
> >

Can this be done with a single query?

Please be polite enough o post DDL in the future. Is this what you
meant?
CREATE TABLE GradeBook
(student_name CHAR(20) NOT NULL,
subject_name CHAR(10) NOT NULL,
PRIMARY KEY (student_name, subject)
student_grade INTEGER NOT NULL
CHECK(student_grade BETWEEN 0 AND 100));
The term list has a certain meaning in Computer Science that you did
not mean. Try something like this:
SELECT student_name
FROM GradeBook
WHERE grade = 85
AND subject_name IN ('math', 'physics', 'chemistry')
GROUP BY student_name
HAVING COUNT(*) = 3;
Look up "relatioanl Division" for a more general approach to this kind
of problem."--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1143495213.186157.100040@.u72g2000cwu.googlegroups.com...
> Please be polite enough o post DDL in the future. Is this what you
> meant?
> CREATE TABLE GradeBook
> (student_name CHAR(20) NOT NULL,
> subject_name CHAR(10) NOT NULL,
> PRIMARY KEY (student_name, subject)
> student_grade INTEGER NOT NULL
> CHECK(student_grade BETWEEN 0 AND 100));
> The term list has a certain meaning in Computer Science that you did
> not mean. Try something like this:
> SELECT student_name
> FROM GradeBook
> WHERE grade = 85
> AND subject_name IN ('math', 'physics', 'chemistry')
> GROUP BY student_name
> HAVING COUNT(*) = 3;
> Look up "relatioanl Division" for a more general approach to this kind
> of problem.
>
Joe, you've fallen into the same trap that ALL the other posters fell into.
The original question was...
Find all the students who got a grade 85 or better in math, physics and
chemistry.
That is you need all three rows for a single student. And only of all three
rows are over 85 select the student. Hence why I did the solution which
uses a pivot.
Colin.

Thursday, March 29, 2012

can we have temporary table in store procedure when using BCP ?

Tuesday, March 27, 2012

can we combine these 3 statements into one single query

Sunday, March 25, 2012

Can we ask English Query to show a Graph ?

Can views be used in query builder?

can view be dynamically filtered like an MSAccess query?

Thursday, March 22, 2012

can use Where in Dts query?

can use Where in Dts query?

Can use some help with a select query

Can u please check this queary?

Can u please check this queary?

Can u please check this queary?

can u guide me writing a simple query

Can TSQL query create new output column ?

Tuesday, March 20, 2012

Can this query be simpilified

can this query be rewritten?

Can This Query Be Improved?

can this be done?

Can this be done with sql?

Can this be done with sql?

Can this be done with a single query?

sql reports

Blog Archive

About Me