sql query builder issues

[xswzaq]

is anyone know how to convert sql from TOAD to vs2005, i am building sql for oracle database.

SELECT T .NAME, case WHEN T .ORIGNL_QTY = T .COUNT_QTY THEN 100 WHEN T .ORIGNL_QTY = 0 THEN 0 WHEN T .COUNT_QTY = 0 THEN 0 WHEN T .ORIGNL_QTY
> 0 AND T .ORIGNL_QTY > T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .ORIGNL_QTY), 2))
* 100 WHEN T .COUNT_QTY > 0 AND T .ORIGNL_QTY < T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .COUNT_QTY), 2))
* 100 END AS PERCENT
FROM TAG T

This code work fine in TOAD program, but when I put it in vb.net it give me this error

Error in list of function arguments : 'T' not recognized.

Error in list of function arguments: ')' not recognized.

Unable to parse query text.

 

[jmcilhinney]

This belongs in the Data Access forum, not the Windows Forms forum. Please put some thought into where you post. Moved.

 

[cjard]

Unable to parse query text.

The VS2005 IDE is too retarded to understand advanced oracle queries. You will do yourself many favours if you remove the headache from you life, by doing this:

[B]CREATE OR REPLACE VIEW [U]TAG_WHATEVER [/U]AS[/B]

SELECT T .NAME, case WHEN T .ORIGNL_QTY = T .COUNT_QTY THEN 100 WHEN T .ORIGNL_QTY = 0 THEN 0 WHEN T .COUNT_QTY = 0 THEN 0 WHEN T .ORIGNL_QTY
> 0 AND T .ORIGNL_QTY > T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .ORIGNL_QTY), 2)) 
* 100 WHEN T .COUNT_QTY > 0 AND T .ORIGNL_QTY < T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .COUNT_QTY), 2)) 
* 100 END AS PERCENT
FROM TAG T

THen in VS2005 do:

SELECT * FROM TAG_WHATEVER

It also means you can change the logic of the view (but not the columns or the data types!!!!) without rolling out another app

 

[xswzaq]

Sorry i am so clueless at this but what is vs2005 IDE? How do I make one?

 

[cjard]

Sorry i am so clueless at this but what is vs2005 IDE? How do I make one?

It's that thing you see when you click Microsoft Visual Studio on the start menu.

You know.. This thing:

-

You use it to, like.. write programs and stuff

 

[xswzaq]

I am still does not figure out about vs2005 IDE., but is there a way like write the 'case when' statement in the code and make write to the percent column instead of putting it into sql statement?

 

[cjard]

Go to TOAD

Paste this:

CREATE OR REPLACE VIEW TAG_WHATEVER AS

SELECT T .NAME, case WHEN T .ORIGNL_QTY = T .COUNT_QTY THEN 100 WHEN T .ORIGNL_QTY = 0 THEN 0 WHEN T .COUNT_QTY = 0 THEN 0 WHEN T .ORIGNL_QTY
> 0 AND T .ORIGNL_QTY > T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .ORIGNL_QTY), 2)) 
* 100 WHEN T .COUNT_QTY > 0 AND T .ORIGNL_QTY < T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .COUNT_QTY), 2)) 
* 100 END AS PERCENT
FROM TAG T

Run it

-

Go to VS2005 (That's the thing you write .NET programs in, it's the thing you were talking about in your first post when you said 'vs2005' and 'vb.net')

Open the DataSet Designer

Right Click the Surface

Choose Add TableAdapter

Paste this as the SQL for your tableadapter:

SELECT * FROM TAG_WHATEVER

-

If you get lost with this, I'll give you my PayPal account info, you can deposit $1000 and I will buy an airline ticket to come to your house, and do it for you, because I cant think of any way to make it simpler..

 

[alander]

Sorry i am so clueless at this but what is vs2005 IDE? How do I make one?

VS2005 IDE = Visual Studio 2005 integrated development environment = ur complier

u cant make 1, only microsoft make them

 

[xswzaq]

cjard, thank you. I am sorry that I am so clueless, but your explanation is very simpler and easy to understand now. I got it to work. Once again thank you.

 

[xswzaq]

I want to ask something, this code put in TOAD

CREATE OR REPLACE VIEW TAG_WHATEVER AS

SELECT T .NAME, case WHEN T .ORIGNL_QTY = T .COUNT_QTY THEN 100 WHEN T .ORIGNL_QTY = 0 THEN 0 WHEN T .COUNT_QTY = 0 THEN 0 WHEN T .ORIGNL_QTY
> 0 AND T .ORIGNL_QTY > T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .ORIGNL_QTY), 2))
* 100 WHEN T .COUNT_QTY > 0 AND T .ORIGNL_QTY < T .COUNT_QTY THEN (1 - round((abs(T .ORIGNL_QTY - T .COUNT_QTY) / T .COUNT_QTY), 2))
* 100 END AS PERCENT
FROM TAG T

is it going to create a new table in TOAD program?

 

[cjard]

cjard, thank you. I am sorry that I am so clueless, but your explanation is very simpler and easy to understand now. I got it to work. Once again thank you.

Youre welcome.. and remember, I was clueless too.. In some weeks or months, you'll be advising other people

 

[cjard]

is it going to create a new table in TOAD program?

no.. it creates a VIEW which is like a table but it is actually an SQL that is run every time you select from it. (Actually its a bit more complicated than that but that will do for now)

So you say:

CREATE OR REPLACE VIEW example_view AS
SELECT * FROM tblPerson

You can then say:

SELECT * FROM example_view WHERE NAME = 'john smith'


The database effectievly runs the following query:


SELECT * FROM
(
SELECT * FROM tblPerson
)
WHERE name = 'john smith'


-

If you dont understand what a view is, read about it in google!

 

[xswzaq]

thank you very much. It is more sense now.