select a query based on case in sql table valued function

Question

3.00/5 (2 votes)

See more:

SQL

CREATE function [dbo].[getRptOrders_test](@OrderNumber int)
returns table
as
RETURN
(
select case exists (select 1 from pf  where ordernumber = @OrderNumber) 
when 1 then 

      SELECT osc.tName, osc.VariantName, osc.Quantity,pf.RequestedDate, ISNULL(osc.Shipped, 0) AS TotalQtyShipped,
      case p.isakit
            when 1 then dbo.getKitPrice(osc.ShoppingCartRecID)
            else osc.OrderedProductPrice
      end
      as OrderedProductPrice, osc.ShoppingCartRecID, osc.Notes, p.IsAKit, osc.OrderNumber,o.CompanyID
      FROM Orders_ShoppingCart osc
      INNER JOIN  p ON osc.ProductID = p.ProductID
      INNER JOIN  o ON osc.OrderNumber = o.OrderNumber
      INNER JOIN   pitem ON osc.ShoppingCartRecID = pitem.ShoppingCartRecID
      INNER JOIN  pf ON pitem.POID = pf.POID
      WHERE (osc.OrderNumber = @OrderNumber) --and dbo.HasAKit(p.productID) = 1
      and osc.iskititem=0
      
      else 
      
       SELECT osc.tName, osc.VariantName, osc.Quantity, ISNULL(osc.Shipped, 0) AS TotalQtyShipped,
      case p.isakit
            when 1 then dbo.getKitPrice(osc.ShoppingCartRecID)
            else osc.OrderedProductPrice
      end
      as OrderedProductPrice, osc.ShoppingCartRecID, osc.Notes, p.IsAKit, osc.OrderNumber,o.CompanyID
      FROM Orders_ShoppingCart osc
      INNER JOIN  p ON osc.ProductID = p.ProductID
      INNER JOIN  o ON osc.OrderNumber = o.OrderNumber
      
      WHERE (osc.OrderNumber = @OrderNumber) --and dbo.HasAKit(p.productID) = 1
      and osc.iskititem=0
     
)
 

GO

Posted 19-Aug-14 23:08pm

venky1988

Updated 19-Aug-14 23:11pm

Magic Wonder

v2

Add a Solution

Comments

Ashi0891 20-Aug-14 5:11am

where is the problem?

Magic Wonder 20-Aug-14 5:12am

What is the issue?

venky1988 20-Aug-14 5:13am

select case exists ..
case statement not working

Herman<T>.Instance 20-Aug-14 5:14am

use functions for these queries and call the functions from this query

venky1988 20-Aug-14 5:19am

CREATE function [dbo].[getRptOrders_test](@OrderNumber int)
returns table
as
RETURN
(
select case exists (select 1 from pf where ordernumber = @OrderNumber)
when 1 then
[dbo].[getRptOrders](@OrderNumber int)

else

[dbo].[getRptOrders_withoutpo](@OrderNumber int)

)

GO

here it is giving 2 errors 1. Incorrect syntax near the keyword 'exists'

2.Incorrect syntax near the keyword 'when'

Herman<T>.Instance 20-Aug-14 5:45am

CREATE FUNCTION [dbo].[CheckExistingOrderNumber](@Ordernumber int)
This function should return the 1 or the null or zero

venky1988 20-Aug-14 6:04am

CREATE function [dbo].[getRptOrders_COH](@OrderNumber int)
returns table
as
RETURN
(

select case when exists (select 1 from po_form where ordernumber = @OrderNumber)
then
dbo.getRptOrders(@OrderNumber)

else

dbo.getRptOrdersWithoutPO(@OrderNumber)
end
)

GO

it is giving this error .. Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getRptOrders", or the name is ambiguous.

can i Pass table valued functions . or this error is with input

Herman<T>.Instance 20-Aug-14 6:15am

I should make it returns INT

Herman<T>.Instance 20-Aug-14 6:16am

as which user are you logged on? Can you use the DBO scheme?

venky1988 20-Aug-14 6:20am

yes
, i can use dbo

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vinay Mistry · Accepted Answer · 2014-08-19T23:25:00

Solution 1

Use this

SQL

CREATE function [dbo].[getRptOrders_test](@OrderNumber int)
returns table
as
RETURN
(
select case when exists (select 1 from pf  where ordernumber = @OrderNumber)
then

Posted 19-Aug-14 23:25pm

Vinay Mistry

Comments

venky1988 20-Aug-14 5:33am

this seems to be working , Thank you

Vinay Mistry 20-Aug-14 5:45am

Good to hear that. and welcome.

venky1988 20-Aug-14 5:54am

CREATE function [dbo].[getRptOrders_COH](@OrderNumber int)
returns table
as
RETURN
(

select case when exists (select 1 from po_form where ordernumber = @OrderNumber)
then
dbo.getRptOrders(@OrderNumber)

else

dbo.getRptOrdersWithoutPO(@OrderNumber)
end
)

GO

it is giving this error .. Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getRptOrders", or the name is ambiguous.

can i Pass table valued functions . or this error is with input

Vinay Mistry 20-Aug-14 6:40am

Try to call your functions without dbo. I don't have all the details otherwise I can check it myself.

venky1988 20-Aug-14 6:43am

in other table valued functions they are calling with dbo.functionname

Vinay Mistry 20-Aug-14 6:52am

then try [dbo].[getRptOrders](@OrderNumber)
as I told before I don't have all the details otherwise I can check it myself.

Vinay Mistry 20-Aug-14 6:58am

Ok use now
select * from [dbo].[getRptOrders](@OrderNumber)
This will definitely work without error.

Magic Wonder · Accepted Answer · 2014-08-19T23:25:00

Solution 2

Hi,

Try this...

SQL

select case WHEN  exists (select 1 from pf  where ordernumber = @OrderNumber)
then 1 ...

Hope this will help you.

Cheers

Posted 19-Aug-14 23:25pm

Magic Wonder

Comments

venky1988 20-Aug-14 5:34am

this seems to be working , Thank you

Magic Wonder 20-Aug-14 5:37am

Your welcome.

Vinay Mistry 20-Aug-14 5:45am

After then why need 1?

Magic Wonder 20-Aug-14 5:51am

you can replace the same with your desired result.