How to write the query to minus a Field in the Sel

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to write the query to minus a Field in the Sel

Author

Topic

itchocks
Starting Member

44 Posts

Posted - 2003-11-09 : 08:03:50

Hai All,

The below is my query. I need to get the field calculated the expression is (InvObj - Qtyonord - StkQty) the red coloured area is the place where i am calculating this Formula field. The value seams to be wrong, because its coming in negative and some places what happens is its taking the Positive value to Negative value, for example

if (InvObj = 0 then Qtyonord = 23 then StkQty = -2) means it should

the value is showing -25

actually the value i should get here and why its showing this. The mistake may be in the Sum area, but i am new i am not sure so Kindly can u correct me the query.

rst.Open "select stk.SC01001 as ItemCode,stk.SC01002 as Description,sum(stk.SC01045) as Qtyonord,sum(stk.SC01042) as StkQty,stk.SC01037 as PrdGrp,(select sum(ST030100.ST03020)
From ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" & txtFromdt.Text &
"' And ST030100.ST03015 <='" & txtTodt.Text & "')" as SoldQty,(select sum(ST030100.ST03020)/" & dtn & " From ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" & txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "')
as AvgSls,(select (sum(ST030100.ST03020)/" & dtn & ")*" & 16 & " From ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" & txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "')" as InvObj,(select(sum(ST030100.ST03020)/" & dtn & ")*" & 16 & "- sum(stk.SC01045) - sum(stk.SC01042) From ST030100 Where ST030100.ST03017 = stk.SC01001 "
"And ST030100.ST03015 >='" & txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "')"
" as Wkord from SC010100 stk where stk.SC01045 >=" & 0 & " Group By stk.SC01001,stk.SC01002 order by stk.SC01001", cnn, adOpenStatic, adLockReadOnly, adCmdText

Thank you very much,
Chock.

Chock

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-09 : 12:21:55

Red area should be:

(select (sum(ST030100.ST03020)/" & dtn & ")*" & 16 & " From
ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" &
txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "')"
- sum(stk.SC01045) - sum(stk.SC01042) as Wkord

itchocks
Starting Member

44 Posts

Posted - 2003-11-10 : 04:41:22

quote:
Originally posted by Stoad

Red area should be:

(select (sum(ST030100.ST03020)/" & dtn & ")*" & 16 & " From
ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" &
txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "')"
- sum(stk.SC01045) - sum(stk.SC01042) as Wkord

Hai Stoad,

Thank you for your reply. If the InvObj value is Positive then i am getting the output correctly. Otherwise if the value is negative then its coming as below

if InvObj is Positive (InvObj = 2)-(Qtyonord = 0)-(StkQty=10) = -8

if InvObj is Negative (InvObj = -2)-(Qtyonord = 0)-(StkQty=10) = -12

is the value returning is correct or what. may be the problem could be in the Formula

then

I need to get the Product Field from the table ST030100 and the field name is ST03018, how can i add this field to the query in which place i can add, because i am using (select sum(ST03020)/20 .....) like this, here in which place i can add the ST03018 field, i need to add this because its the ProductName field.

Thank you,
Chock.

Chock

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-10 : 06:19:42

Chock :)

I am a humble plant programmer not a specialist in cryptanalysis...

Really, Chock, it's very hard to understand what exactly you need.

itchocks
Starting Member

44 Posts

Posted - 2003-11-10 : 08:24:50

quote:
Originally posted by Stoad

Chock :)

I am a humble plant programmer not a specialist in cryptanalysis...

Really, Chock, it's very hard to understand what exactly you need.

Hai Stoad,

Sorry for my lost post which is so confused

Actually i am having the ProductName Field in both the Tables. SC010100 and ST030100

In VB - DataReport i assigned the SC01037 (i.e) the ProductName

by adding the ProductName at the first senctence of the query as below in the blue color

rst.Open "select stk.SC01001 as ItemCode,stk.SC01002 as Description,sum(stk.SC01045) as Qtyonord,sum(stk.SC01042) as StkQty,stk.SC01037 as Prd,

i get the error "Report Sections do not match data source"

I am not sure whether is because of query or not so i planned to take the ProductName field from the ST030100 table, so if I want to take that

1. Is it possible to modify my query

2. If yes then how i have to modify the query

Thank youl very much,
Chock.

Chock

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-10 : 13:15:39

Chock,

seems the problem is in the VB's DataReport not in the SQL query.

itchocks
Starting Member

44 Posts

Posted - 2003-11-12 : 12:45:00

quote:
Originally posted by Stoad

Chock,

seems the problem is in the VB's DataReport not in the SQL query.

Hai Stoad,

Is it possible for me to write my query without using the Alias name.

and did u worked with Data Reports in VB before. When i insert the Group in the DataReport i get the error

Report Sections do not match data source

Kindly suggest your idea.

Thank you very much,
Chock.

Chock

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-12 : 13:39:15

Hi, Chock!

Pity I never used VB's DataReports but I think the problem is that
the names of output fields of the query do not correspond to the
names of data sources of report's textboxes. Maybe only one of
them >> now in query there is Prd, but earlier it was PrdGrp.
Maybe you should correct datasource name of a textbox (in
which will be displayed Prd field).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources