gudii9
asked on
queries to find data
How to write below query on say table ORDER given the order_nbr is the column name in that table
queries to find below
1. order number more than 11 digits
2. order number more than 11 digits which has payment online
3. order number more than 18 digits
4. order number more than 18 digits which has payment CreditCard option
Please advise
queries to find below
1. order number more than 11 digits
2. order number more than 11 digits which has payment online
3. order number more than 18 digits
4. order number more than 18 digits which has payment CreditCard option
Please advise
You simply need to translate those words into conditions in a WHERE clause.
select *
from orders
where len(order_nbr) > 11
and payment_type = 'online'
;
select *
from orders
where len(order_nbr) > 18
and payment_type = 'credit card'
;
ps: Do NOT name a table "ORDER"... please! (it is a word used in SQL and it will be a big problem for you)
select *
from orders
where len(order_nbr) > 11
and payment_type = 'online'
;
select *
from orders
where len(order_nbr) > 18
and payment_type = 'credit card'
;
ps: Do NOT name a table "ORDER"... please! (it is a word used in SQL and it will be a big problem for you)
if order_nbr is numeric
select *
from orders
where order_nbr > 9999999999 ; 10 x 9's
and payment_type = 'online'
;
select *
from orders
where order_nbr> 999999999999999999 ; 18 x 9's
and payment_type = 'credit card'
;
select *
from orders
where order_nbr > 9999999999 ; 10 x 9's
and payment_type = 'online'
;
select *
from orders
where order_nbr> 999999999999999999 ; 18 x 9's
and payment_type = 'credit card'
;
ASKER
that number is alphanumeric like below
1X2356791485932Y
1X2356791485932Y
ASKER
i need counts though
so just say
select count(order_nbr) from orders WHERE LEN(order_nbr) >18
50
select count(order_nbr) from orders WHERE LEN(order_nbr) >11
100
above worked
below not working as payment_type is not in same table as orders
not sure on which table it is joined?
select count(order_nbr) from orders WHERE LEN(order_nbr) >11 and payment_type = 'credit card'
how to check which all other tables this order table joined within microsoft sql server management studio?
so just say
select count(order_nbr) from orders WHERE LEN(order_nbr) >18
50
select count(order_nbr) from orders WHERE LEN(order_nbr) >11
100
above worked
below not working as payment_type is not in same table as orders
not sure on which table it is joined?
select count(order_nbr) from orders WHERE LEN(order_nbr) >11 and payment_type = 'credit card'
how to check which all other tables this order table joined within microsoft sql server management studio?
>how to check which all other tables this order table joined within microsoft sql server management studio?
Before you start you need to know where this information is stored. Usually there should be a column on the orders table which stores this value
Before you start you need to know where this information is stored. Usually there should be a column on the orders table which stores this value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE LEN(OrderNumber) >11
for PaymentOption, is there any column that stores this info, is so add that condition to the above.